Search code examples
pythonpandasms-accesspyodbc

Pandas to ODBC connection with to_sql

I'm trying to export a pandas DataFrame into an MS Access table through pyodbc.

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=my_db.accdb;')

df.to_sql('test', conn, index=False)

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;':
('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver]
The Microsoft Access database engine cannot find the input table or query 'sqlite_master'.
Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")

sqlite_master? Where does that come from?

Solution

  • .to_sql() expects the second argument to be either a SQLAlchemy Connectable object or a DBAPI Connection object. If it is the latter then pandas assumes that it is a SQLite connection.

    You need to use the sqlalchemy-access dialect.

    (Disclosure: I maintain that dialect.)