0

I have an access table I am trying to update from a dataframe (I am having a lot of difficulty with the sqlalchemy syntax, so the following code may be very clunky with redundancies):

import pandas as pd
import pyodbc
import urllib
from sqlalchemy import create_engine
    
Results = [[178, 10], [179, 15], [180, 14]]
Res_summary = pd.DataFrame (Results, columns = ['ID', 'Score'])
cnn_str = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Folder\results.accdb;'
cnn_url = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(cnn_str)}"
acc_engine = create_engine(cnn_url)
Res_summary.to_sql('My_results', acc_engine, if_exists='append')

So now if I use different IDs next time I run the script, the rows will be added to the access table. However, if I try to run the script again using the same IDs and different scores, I would like the scores for those IDs to be updated, and not get duplicates. I know there is an option for "upsert", but I don't understand how to do it for eliminating duplicates of specific rows and not the entire table.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Nitzan
  • 33
  • 5
  • 1
    https://github.com/gordthompson/sqlalchemy-access/wiki/%5Bfaq%5D-upsert-from-DataFrame-to-existing-table – Gord Thompson Feb 15 '22 at 13:28
  • 1
    As Gord (author of the Access SQLAlchemy dialect) links, you need to push pandas data to a temp staging table and then run an `UPDATE` query to final table even an `INSERT` query that avoids duplicates. – Parfait Feb 15 '22 at 15:47

0 Answers0