0

I have very large dataframe I'd like to directly write into a MS Access Database.

My current approach looks like this

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine import URL

accdb_path = r"C:\Users\Public\test\TEST.accdb"

connection_string = ( 
    "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    f"DBQ={accdb_path};"
    "ExtendedAnsiSQL=1;"
)

connection_url = URL.create(
    "access+pyodbc",
    query={"odbc_connect": connection_string}
)

engine = create_engine(connection_url)

df = pd.DataFrame([(1, "foo"), (2, "bar")], columns=["id", "txt"])
df.to_sql("my_table", engine, index=False, if_exists="append")

(... ) which yields

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] The data source name was not found and no default driver was specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/rvf5)

Although the specified driver is installed and the path to the database surely is correct!

enter image description here

hyperinfer
  • 39
  • 1
  • 1
  • 5
  • What is the issue you're having ? And how does it compare to the linked question ? – ljmc Jan 16 '23 at 17:55
  • Possibly of interest for "very large dataframe[s]": [pyodbc wiki entry](https://github.com/gordthompson/sqlalchemy-access/wiki/%5Bpandas%5D-faster-alternative-to-.to_sql()-for-large-uploads) – Gord Thompson Jan 17 '23 at 01:21
  • Are you running 64-bit Python? If so, then it cannot "see" the 32-bit ODBC driver. See the duplicate's answer for more information about "bitness". – Gord Thompson Jan 18 '23 at 09:44

0 Answers0