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
Share
Improve this question
edited Jan 18 at 14:23
Bill the Lizard
406k212 gold badges573 silver badges891 bronze badges
asked Jan 18 at 7:24
Muhammad abdullahMuhammad abdullah
1
1
|
1 Answer
Reset to default 0Your 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()
:0
and:1
. – Man made of meat Commented Jan 18 at 15:06