CODEWITHSUNDEEP
pandasdataframems-accesspyodbcsqlalchemy-access
Jay
Jay

Reputation: 23

MS Access pyodbc Pandas dataframe to_sql throws Invalid Precision Value

I'm using the pandas dataframe's to_sql function to save records to MS Access 97 MDB. But I'm getting the error below:

pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid 
precision value  (98) (SQLBindParameter)').

I'm using Python 3.8.5 32-bit on a Windows 10 Workstation.

I also have the following libraries installed.

datacompy==0.7.1
et-xmlfile==1.0.1
jdcal==1.4.1
numpy==1.19.2
openpyxl==3.0.5
pandas==1.1.2
pyodbc==4.0.30
python-dateutil==2.8.1
pytz==2020.1
pywin32==228
six==1.15.0
SQLAlchemy==1.3.19
sqlalchemy-access==1.0.7

Thanks in advance!

Upvotes: 1

Views: 1171

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

You have encountered a limitation of the Access ODBC Driver when working with Access_97 database files. In Python_3 all strings are Unicode, but Access did not start supporting Unicode until Access_2000.

This code fails with the error you describe

num_characters = 128
df = pd.DataFrame(
    [
        (
            1,
            "x" * num_characters,
        )
    ],
    columns=["ID", "TextField"],
)
df.to_sql("sa_a_test", engine, index=False, if_exists="append")

for values of num_characters greater than 127 when working with an Access_97 (Jet 3.5) .mdb file. The same code does not fail when working with an Access_2000 (Jet 4.0) .mdb file.

As a workaround you might be able to use something like this

cnxn = engine.raw_connection()
crsr = cnxn.cursor()
sql = "INSERT INTO [sa_a_test] ([ID],[TextField]) VALUES (?,?)"
crsr.setinputsizes([(pyodbc.SQL_INTEGER,), (pyodbc.SQL_LONGVARCHAR,)])
crsr.executemany(sql, df.itertuples(index=False))
cnxn.commit()

but a better solution would be to upgrade the database file to a newer version. The Access_97 file format has been deprecated for many years and is now completely unsupported by Microsoft.

Upvotes: 1

Related Questions