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?: pandas.pydata.org/docs/reference/api/… — user19299338
– user19299338, Commented Jun 8, 2022 at 21:29
I'll try add this also from sqlalchemy import create_engine engine = create_engine("access+pyodbc://@your_dsn") — user19299338
– user19299338, Commented Jun 8, 2022 at 21:31
Thank you so much! problem solved. I would upvote you but I don't have enough scores to do so. — user19299338
– user19299338, Commented Jun 8, 2022 at 21:46

Gord Thompson · Accepted Answer · 2022-06-08 21:56:11Z

2

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")

edited Jun 8, 2022 at 21:56

answered Jun 8, 2022 at 21:50

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related