Error using to_sql(… , method='multi') with MS Access

Question

I am inserting data into a Microsoft Access database using the following code:

test_data.to_sql('employee_table', cnxn, index=False, if_exists='append', chunksize=10, method='multi')

This gives error:

AttributeError: 'CompileError' object has no attribute 'orig'

There is no error when just using the following i.e. no method option:

test_data.to_sql('employee_table', cnxn, index=False, if_exists='append', chunksize=10)

Then why not use the code that does not error? Does it commit the data? What is the issue here? Why do you need the additional parameter? — June7, Oct 05 '21 at 18:44

Gord Thompson · Accepted Answer · 2021-10-05T23:05:09.430

The error message you cited is a subsequent exception caused by the original error (earlier in the stack trace):

sqlalchemy.exc.CompileError: The 'access' dialect with current database version settings does not support in-place multirow inserts.

The method="multi" option of .to_sql() wants to create multi-row INSERT statements, often in the form of a "table-value constructor", e.g.,

INSERT INTO table1 (col1, col2) VALUES (1, 'foo'), (2, 'bar')

and Access SQL does not support those.

If a plain .to_sql() (without method=) is too slow for a large DataFrame then consider the alternative approach documented in the wiki:

1 Answers1