[pandas] specifying column (field) types for to_sql()

When saving a DataFrame with to_sql(), pandas makes some assumptions regarding the data types of the columns it creates. These assumptions are "safe" ones, intending to ensure that no data is lost. However, they may not be the best choices depending on your needs.

For example, this code ...

import pandas as pd

df = pd.DataFrame([("bacon", 5.99), ("ham", 21.99),], columns=["id", "price"],)
df.to_sql("product", engine, if_exists="replace", index=False)

... produces a table where the [id] column is Long Text and the [price] column is Double.

If you'd prefer that the [id] column is Short Text and the [price] column is Currency then simply specify them using the dtype= argument:

import pandas as pd
import sqlalchemy_access as sa_a

df = pd.DataFrame([("bacon", 5.99), ("ham", 21.99),], columns=["id", "price"],)
dtype_dict = {"id": sa_a.ShortText(20), "price": sa_a.Currency}
df.to_sql(
    "product", engine, if_exists="replace", index=False, dtype=dtype_dict
)

See also:

[faq] field (column) type summary