Exporting or writing data from python pandas data frame to MS Access table

Question

I am trying to export data from a python pandas data frame to an existing MS Access table, I would like to replace the MS access table with data that has been updated (in python) I have tried to use pandas.to_sql, but I get an error message. I find this strange this using pandas.read_sql works seamlessly?

Here is my code:

import pyodbc
import pandas as pd
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=H:\Work\IndexTrader\Data\database\IndexData.accdb;'
)
cnxn = pyodbc.connect(conn_str)
SQL = 'SELECT * FROM Index_data;

Reading data from MS Access is fine, see below

dfins = pd.read_sql(SQL, cnxn)

However when I try to write back and replace the table in MS excess it doesn't work?

dfins.to_sql('Index_data', cnxn, if_exists='replace')
cnxn.close()

The error I get is:

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

If there is an alternative way instead of pandas.to_sql, that would help as well, I just need to know how to export my data.

If you look at the documentation, it states that only sqlite3 is supported. You can just parse the dataframe row by row and use INSERT statements. — Erik A
– Erik A, Commented Oct 16, 2017 at 10:14
Hi Erik thank you for the response, I am not quite sure how I could use INSERT? Could you provided more guidance please. — blala
– blala, Commented Oct 16, 2017 at 10:26

Erik A · Accepted Answer · 2022-02-15 07:59:34Z

7

As said in the comments, to_sql only supports sqlite3

An example approach to parse the data frame row by row, and insert each row into a table:

import pyodbc
import pandas as pd
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:\Users\Erik\Desktop\TestDb.accdb;'
)
cnxn = pyodbc.connect(conn_str)
SQL = 'SELECT * FROM Index_data;'
dfins = pd.read_sql(SQL, cnxn)
for index, row in dfins.iterrows():
    with cnxn.cursor() as crsr:
       crsr.execute("INSERT INTO Output(Field1, Field2) VALUES(?,?)", row["F1"], row["F2"] )

This inserts columns F1 and F2 of the DataFrame into fields Field1 and Field2 of a table named Output.

This has two main conditions to properly work:

You need to have an Access table in place to receive the data
The table needs to have the right fields with the right data types to receive the data

You can use a pre-existing table, like the index table you're getting the data from, though I don't recommend it (risk of losing data). If you do so, you need to clear the table first. To clear out the index table:

with cnxn.cursor() as crsr:
    crsr.execute("DELETE * FROM Index_data;")

edited Feb 15, 2022 at 7:59

answered Oct 16, 2017 at 12:31

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

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

1 Comment

blala Over a year ago

Hi Erik, thank you that makes sense. I have used your code and it works perfectly. :-)

Parfait · Accepted Answer · 2017-10-16 17:57:17Z

Consider exporting pandas dataframe to CSV file and then run an inline query in MS Access SQL as the Jet/ACE SQL engine allows CSVs to be directly queried. Do note the folder is the database with period qualifier on the file name. Adjust columns accordingly.

import pyodbc
import pandas as pd

conn_str = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=H:\Work\IndexTrader\Data\database\IndexData.accdb;'
cnxn = pyodbc.connect(conn_str)

...

dfins.to_csv(r'C:\Path\To\myCSVFile.csv', index=False)

cur = cnxn.cursor()
cur.execute("INSERT INTO Index_data (Col1, Col2, Col3)" + \
            " SELECT Col1, Col2, Col3" + \
            " FROM [text;HDR=Yes;FMT=Delimited(,);Database=C:\Path\To\Folder].myCSVFile.csv t")
cnxn.commit()

Rudy · Accepted Answer · 2021-03-06 04:08:34Z

Just to follow up on Parfait's answer. I like the idea of an inline query in ms access instead of iterating over the rows in a dataframe. However, I ran into an issue with fields that look numeric (to Excel or Access); those fields with leading zeros. We have lots of those in my environment. For those, I just wrap them with quotes using the csv module. Something like this:

from pathlib import Path
import csv
csv_file = Path(Path.cwd(), ms_access_table_name + '.csv')
my_df.to_csv(csv_file, sep = ',', quoting=csv.QUOTE_NONNUMERIC , index=False)
ms_sql = f'INSERT INTO {ms_access_table_name} ({ms_access_columns}) \n' +\
            f'SELECT {ms_access_columns} \n' + \
            f'FROM [text;HDR=Yes;FMT=Delimited(,);IMEX=2;Database={Path.cwd()}].{ms_access_table_name + ".csv"}'
cursor.execute(ms_sql)
cursor.commit()

cursor.close()
conn.close()

Collectives™ on Stack Overflow

Exporting or writing data from python pandas data frame to MS Access table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related