I'm trying to execute this update query from columns in a pandas dataframe:
sql = "UPDATE tblhis_ventas SET portabilidad = '%s' WHERE (contrato = '%s' and estado = '%s') " % (
df['portabilidad'], df['contrato'], df['estado']
)
cursor.execute(sql)
The query isn't executed and no error is displayed.
My dataframe looks like below:
Unfortunately, MS Access (specifically its disk-level Jet/ACE Engine not the GUI .exe program) is not supported with SQLAlchemy to allow for the pandas.to_sql() method which ideally you can push your dataframe to a temp table in database to run an UPDATE final INNER JOIN temp ... query to update final table, a much faster route than iterating across rows.
Fortunately, MS Access' Jet/ACE Engine can query csv files as if they were tables where you specify the file's path and then name of csv file. Hence, consider exporting dataframe to_csv, then create a temp table using the Make-Table query, and finally run the update join query. Below try/except is used to drop the table if exists (since IF EXISTS command is not available in MS Access SQL).
df.to_csv('C:\Path\To\CSV\Output.csv', index=False)
try:
cursor.execute("SELECT * INTO tblhis_ventas_Temp" +\
" FROM [text;HDR=Yes;FMT=Delimited(,);Database=C:\Path\To\CSV].Output.csv")
conn.commit()
cursor.execute("UPDATE tblhis_ventas f INNER JOIN tblhis_ventas_Temp t" + \
" ON f.contrato = t.contrato AND f.estado = t.estado" + \
" SET f. portabilidad = t.portabilidad")
conn.commit()
except Exception as e:
if 'already exists' in str(e):
cursor.execute("DROP TABLE tblhis_ventas_Temp")
conn.commit()
else:
print(e)
cursor.close()
conn.close()
And no, we cannot directly use CSV file in UPDATE query (bypassing temp table process) as the csv is a read-only and not updateable recordset. Interestingly, you can use CSV in an INSERT...SELECT.
Iterate rows, and update one by one:
sql = 'UPDATE tblhis_ventas SET portabilidad = ? WHERE contrato = ? and estado = ?'
for index, row in df.iterrows():
cursor.execute(sql, [row['portabilidad'], row['contrato'], row['estado']])
df, I can't tell you how to improve it, or even tell you whether it's possible.