最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

python - Dataframe data did not insert into MS Access database “Type Error: The first argument to execute must be a string or un

programmeradmin2浏览0评论

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 Aside: a comma is missing between :0 and :1. – Man made of meat Commented Jan 18 at 15:06
Add a comment  | 

1 Answer 1

Reset to default 0

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

与本文相关的文章

发布评论

评论列表(0)

  1. 暂无评论