Dataframe data did not insert into MS Access database "Type Error: The first argument to execute must be a string or unicode query."

[badar]

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. conn.commit() conn

TypeError: The first argument to execute must be a string or unicode query.

[Dhruv4]

The error occurs because conn.execute() does not accept a list of tuples for bulk inserts, and the placeholders in your query (:0, :1, etc.) are not supported by the pyodbc library for MS Access.

Here’s code to insert the Pandas DataFrame data into the MS Access table:

import streamlit as st
import pyodbc
import pandas as pd

# Define the connection string for MS Access
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\iqra\mfa.accdb;'
)

# Connect to the database
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Fetch data from the MS Access table
query = "SELECT * FROM Payments"
dataf = pd.read_sql(query, conn)

# Prepare the DataFrame rows for insertion
rows = [tuple(x) for x in dataf.values]

# Prepare an INSERT query with placeholders for each column
# Assuming Payments table has 4 columns as an example
insert_query = "INSERT INTO Payments (column1, column2, column3, column4) VALUES (?, ?, ?, ?)"

# Insert each row into the table
for row in rows:
    cursor.execute(insert_query, row)

# Commit the transaction
conn.commit()

# Close the connection
cursor.close()
conn.close()