[pandas] working with 64 bit integer (BIGINT) values - gordthompson/sqlalchemy-access GitHub Wiki

Microsoft added support for 64-bit integer (BIGINT) columns to Access 2016 version 16.0.7812, calling them "Large Number" fields. Unfortunately, the Access 2016 version of the ACE ODBC Driver remains at version 16.0.4513, so it cannot work with Access 2016 databases that have been converted to support BIGINT columns.

Also, as it happens, pandas creates integer columns as numpy.int64, even if they are created by read_sql_query pulling data from a (32-bit) int column. So, to avoid hassles in the majority of cases, this dialect will create a 32-bit integer column ("Long Integer" in Access parlance) and try to write the values into it.

So, this works fine:

df = pd.DataFrame([(1234567890,),], columns=['column1'])
df.to_sql("my_table", engine, if_exists="replace", index=False)

However, the following will fail with "[ODBC Microsoft Access Driver]Optional feature not implemented" ...

df = pd.DataFrame([(12345678901,),], columns=['column1'])
df.to_sql("my_table", engine, if_exists="replace", index=False)

... because 12345678901 is too large to fit into a 32-bit signed integer.

Workarounds include saving the column as ShortText …

import sqlalchemy_access as sa_a

df = pd.DataFrame(
    [
        (12345678901,),
        (-12345678901,),
    ],
    columns=["column1"],
)
df["column1"] = df["column1"].astype(str)
dtype_dict = {'column1': sa_a.ShortText(20)}
df.to_sql("my_table", engine, index=False, if_exists="replace", dtype=dtype_dict)

… or as Decimal

import sqlalchemy_access as sa_a

df = pd.DataFrame(
    [
        (12345678901,),
        (-12345678901,),
    ],
    columns=["column1"],
)
df["column1"] = df["column1"].astype(str)  # still need to convert the column to string!
dtype_dict = {'column1': sa_a.Decimal(19, 0)}
df.to_sql("my_table", engine, index=False, if_exists="replace", dtype=dtype_dict)