how to update multiple rows in ms access from pandas dataframe

Question

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:

Answer 1

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.