I have successfully connected to MS Access, and my code accurately displays the data from the MS Access database table in a DataFrame. However, when I attempt to insert the data from the Pandas DataFrame into the MS Access table, I encounter an error stating, “Type Error: The first argument to execute must be a string or Unicode query.” I would appreciate it if you could review my code, correct it, and provide me with the appropriate code to insert the DataFrame data into the MS Access table. Thank you in advance for your guidance.

import streamlit as st
import pyodbc
import pandas as pd
conn_str = (r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            r'DBQ=C:\iqra\mfa.accdb;')
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
query = "SELECT * FROM Payments"
dataf = pd.read_sql(query, conn)
rows = [tuple(x) for x in dataf.values]
conn.execute("INSERT INTO Payments VALUES (:0:1,:2,:3)",rows)  # insert df into msaccess give error TypeError: The first argument to execute must be a string or unicode query.
connmit()
conn

conn.execute("INSERT INTO Payments VALUES (:0:1,:2,:3)",rows)  # insert df into msaccess 

I have successfully connected to MS Access, and my code accurately displays the data from the MS Access database table in a DataFrame. However, when I attempt to insert the data from the Pandas DataFrame into the MS Access table, I encounter an error stating, “Type Error: The first argument to execute must be a string or Unicode query.” I would appreciate it if you could review my code, correct it, and provide me with the appropriate code to insert the DataFrame data into the MS Access table. Thank you in advance for your guidance.

import streamlit as st
import pyodbc
import pandas as pd
conn_str = (r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            r'DBQ=C:\iqra\mfa.accdb;')
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
query = "SELECT * FROM Payments"
dataf = pd.read_sql(query, conn)
rows = [tuple(x) for x in dataf.values]
conn.execute("INSERT INTO Payments VALUES (:0:1,:2,:3)",rows)  # insert df into msaccess give error TypeError: The first argument to execute must be a string or unicode query.
connmit()
conn

conn.execute("INSERT INTO Payments VALUES (:0:1,:2,:3)",rows)  # insert df into msaccess 

• python
• pandas
• pyodbc

• 1 Aside: a comma is missing between :0 and :1. – Man made of meat Commented Jan 18 at 15:06

1 Answer 1

Your rows variable is a list of tuples, so you want to use the executemany function to insert all of the rows.

rows = [tuple(x) for x in dataf.values]
insert_sql = "INSERT INTO Payments VALUES (?, ?, ?, ?)"
cursor.executemany(insert_sql, rows)
cursormit()