dear boss My code successfully retrieves and displays data from MS Access; however, I encounter a parameter error when attempting to update a record. I would appreciate it if you could review my code and provide a correct solution for updating records from a DataFrame to an MS Access table. Thank you in advance.
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)
i_want_to_multiply = 2
filtered_rows = dataf.loc[dataf[‘Payment_ID’] == 20]
filtered_rows[‘Gross_Pay’] = filtered_rows[‘Gross_Pay’] * i_want_to_multiply
filtered_rows = filtered_rows[[‘Payment_ID’, ‘Gross_Pay’]]
cols = [‘Payment_ID’ , ‘Gross_Pay’]
sql = ‘’‘UPDATE Payments SET [Gross_Pay]=? WHERE [Payment_ID]=? ‘’’
cursor.execute(sql, filtered_rows[cols].values.tolist())
cursor.commit
conn.commit
cursor.close()
conn.close()