Pandas to_sql function doesn't put correct dtypes into SQLite DB

Question

I have a problem that pandas to_sql function doesn't put correct dtypes to SQLite 3 database. It automatically detects types and ignores the types specified in the dictionary provided. I have tried a lot of variants or types writing like 'int', 'integer', 'float', 'real', 'floating', tried to show them directly or with use of sqlalchemy.types methods.

I attach also screenshot with SQLite DB columns types and types in csv-file used for import to SQLite DB. SQLite DB columns types are always the same, no matter which datatypes I showed.

def generate_dtypes_for_sql(filename, separator, decimal, skip_errors, quoting, engine, shape):
    df2 = pd.DataFrame()
    if os.path.isfile(filename):
        try:
            df = load_csv(filename, separator, decimal, skip_errors, quoting, engine, shape)
            params_to_import = {}
            cols = df.columns
            i_arc = 7; i_name = 6; i_type = 3; i_factor = 5
            params_types = ['boolean', 'integer', 'float', 'text']
            if (i_arc==cols.get_loc('Архивация') and 
               i_name==cols.get_loc('Символьный тэг') and 
               i_type==cols.get_loc('Тип')):
                 for index, row in df.iterrows():
                    if row[i_arc] == 1:
                        if math.isnan(row[i_type]):
                           params_to_import[row[i_name]] = params_types[3]
                        elif row[i_type] in range(6):
                            if row[i_factor] == 1:
                                params_to_import[row[i_name]] = params_types[1]
                            else:
                                params_to_import[row[i_name]] = params_types[2]
                        elif row[i_type] == 6:
                            params_to_import[row[i_name]] = params_types[2]
                        else:
                            params_to_import[row[i_name]] = params_types[3]
            df2 = pd.DataFrame([params_to_import])
            df2.T.to_csv("params_to_import.csv", sep=";", index_label="Name", header=['Type'])
        except LoadCsvError as e:
            click.echo("Could not load {}: {}".format(filename, e), err=True)
    return df2

def sqlcol(dfparam):    
    dtypedict = {}
    for index, values in dfparam.items():
        for value in values:
            if value == "boolean":
                dtypedict.update({index: sqlalchemy.types.Boolean()})
            elif value == "integer":
                dtypedict.update({index: sqlalchemy.types.Integer()})
            elif value == "float":
                dtypedict.update({index: sqlalchemy.types.Float()})
            elif value == "text":
                dtypedict.update({index: sqlalchemy.types.Text()})        
    return dtypedict    

df_for_sql = generate_dtypes_for_sql(types_file, separator, decimal, skip_errors, quoting, engine, shape)
df_dtypes = sqlcol(df_for_sql)

conn = sqlite3.connect(dbname, detect_types=sqlite3.PARSE_DECLTYPES)

df.to_sql(df.name, conn, if_exists="append", index=False, dtype=df_dtypes_str)

Solution: I don't know why but pandas to_sql function ignores dtype only if I use it with flag: if_exists="append". But if I use it with flag if_exists="replace", it works OK.

Just as a reminder, SQLite has dynamic typing and the types you give columns simply define their affinity (how they prefer to store data), but you can insert text to an integer column etc. It also has its own way of mapping common SQL type names to the ones it actually supports (no real DATE etc. types). Please provide a minimal reproducible example, stressing minimal and reproducible. Provide sample data, expected outputs and actual outputs. Trim down the code to the absolute minimum required to produce those outputs. — Ilja Everilä
– Ilja Everilä, Commented Apr 25, 2020 at 18:33
I am able to reproduce your issue, but the dtype= fix works for me. — Gord Thompson
– Gord Thompson, Commented Apr 26, 2020 at 13:57
@gord-thompson, sa.Table string causes this error: AttributeError: 'sqlite3.Connection' object has no attribute 'run_callable' I couldn't find in Google how to fix it. — Akra Bato
– Akra Bato, Commented Apr 26, 2020 at 16:22
In my test code engine is a SQLAlchemy Engine object created with the create_engine method. — Gord Thompson
– Gord Thompson, Commented Apr 26, 2020 at 16:31

Gord Thompson · Accepted Answer · 2020-04-26 20:35:30Z

The issue here was not that pandas was ignoring the dtype= argument, it was that to_sql was told if_exists="append" and the table already existed, so the column types (actually "affinities" in SQLite) were already defined in the database. This test code shows that if the table does not already exist then using a dtype= argument will indeed produce the desired results:

import pandas as pd
import sqlalchemy as sa

connection_uri = "sqlite:///C:/__tmp/SQLite/walmart.sqlite"
engine = sa.create_engine(connection_uri)

def drop_table(table_name, engine):
    with engine.connect() as conn:
        conn.execute(sa.text(f'DROP TABLE IF EXISTS "{table_name}"'))

df = pd.read_csv(r"C:\Users\Gord\Desktop\test.csv")
print(df)
"""
   All_HY_SP1  All_HY_SP2
0           1         1.1
1           2         2.2
"""
# default behaviour
drop_table("from_csv", engine)
df.to_sql("from_csv", engine, if_exists="append", index=False)
tbl = sa.Table("from_csv", sa.MetaData(), autoload_with=engine)
print(", ".join([f'"{col.name}": {col.type}' for col in tbl.columns]))
# "All_HY_SP1": BIGINT, "All_HY_SP2": FLOAT
#               ^^^^^^
# fix with dtype:
dtype_dict = {"All_HY_SP1": sa.Float, "All_HY_SP2": sa.Float}
drop_table("from_csv", engine)
df.to_sql("from_csv", engine, if_exists="append", index=False, dtype=dtype_dict)
tbl = sa.Table("from_csv", sa.MetaData(), autoload_with=engine)
print(", ".join([f'"{col.name}": {col.type}' for col in tbl.columns]))
# "All_HY_SP1": FLOAT, "All_HY_SP2": FLOAT
#               ^^^^^

Doh! Spent the better part of the last hour trying to figure this out - thanks Gord.

Collectives™ on Stack Overflow

Pandas to_sql function doesn't put correct dtypes into SQLite DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related