How can I write a pandas dataframe into an Access database using pyodbc?

Question

I am using the following code to read a table from an access db as a pandas dataframe:

import pyodbc 
import pandas as pd

connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\A\Documents\Database3.accdb;"
    )
cnxn = pyodbc.connect(connStr)

sql = "Select * From Table1"
data = pd.read_sql(sql,cnxn)  # without parameters [non-prepared statement]

# with a prepared statement, use list/tuple/dictionary of parameters depending on DB
#data = pd.read_sql(sql=sql, con=cnxn, params=query_params)

I plan to make some transformations and then write the dataframe back into the databsae in a similar way. Does anyone know how I can do this?.

Thank you

Thanks. Would this syntax be correct then?: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html — , Jun 08 '22 at 21:29
I'll try add this also from sqlalchemy import create_engine engine = create_engine("access+pyodbc://@your_dsn") — , Jun 08 '22 at 21:31
Thank you so much! problem solved. I would upvote you but I don't have enough scores to do so. — , Jun 08 '22 at 21:46
This was the connection syntax I used:import sqlalchemy as sa connection_string = ( r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" r"DBQ=C:\Users\Public\test\sqlalchemy-access\sqlalchemy_test.accdb;" r"ExtendedAnsiSQL=1;" ) connection_url = sa.engine.URL.create( "access+pyodbc", query={"odbc_connect": connection_string} ) engine = sa.create_engine(connection_url) Then I added the .to_sql(). If you put it as an answer I could mark it as a solution — , Jun 08 '22 at 21:48

Gord Thompson · Accepted Answer · 2022-06-08T21:56:11.147

When working with pandas and a database other than SQLite we need to use SQLAlchemy. In this case, we would use the sqlalchemy-access dialect.

(I am currently the maintainer.)

Example:

import pandas as pd
import sqlalchemy as sa 

connection_string = ( 
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\test\sqlalchemy-access\sqlalchemy_test.accdb;"
    r"ExtendedAnsiSQL=1;" )
connection_url = sa.engine.URL.create(
    "access+pyodbc",
    query={"odbc_connect": connection_string}
)
engine = sa.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")

How can I write a pandas dataframe into an Access database using pyodbc?

1 Answers1