title: Updating sql db with records from dataframe pandas
body: <p>I have a sample database with five columns and five rows of data.  The first two columns are unique for every row.  Some of the cells in the database are intentionally empty.</p>
  <p>I have a dataframe with the same sequence of columns, names, etc as in the database, but all the cells in the dataframe are filled with data.</p>
  <p>I want to avoid deleting the db table and uploading the dataframe in its place and, instead, via sqlalchemy or any other method, loop through the dataframe and update the data in the database accordingly.</p>
  <p>Here are some of my multiple attempts to create the loop to accomplish that none of which worked and all which threw different errors:</p>
  <pre><code>for row in df.itertuples():
      print(row.Index)
      for col in df.columns[1:]:
          print(getattr(row, col))
  
  mytable = db.Table('daily_admits_test', metadata, autoload=True, autoload_with=engine)
  for col in update.columns:
      for idx, row in update.iterrows():
          print(f'{col} = {row[col]}')
          print(row[0], row[3], row[1])
          query = db.update(mytable).values(col = row[col])
          query = query.where(mytable.columns.client_id == row[0] and 
           mytable.columns.Transfer_Date == row[3] and mytable.columns.fac_id == row[1])
          results = con.execute(query)
  </code></pre>
  <p>This worked to update a single record, but required hard coding the variables as you can see in the second query line.  Won't work:</p>
  <pre><code>mytable = db.Table('daily_admits_test', metadata, autoload=True, autoload_with=engine)
  query = db.update(mytable).values(Patient_Name = '')
  # query = query.where(mytable.columns.client_id == '936042')
  query = query.where(mytable.columns.client_id == '936042' and mytable.columns.Transfer_Date == '2022-01-04' and mytable.columns.fac_id == 22)
  results = con.execute(query)
  </code></pre>
  <p>The goal seems simple.  Iterate over my dataframe and only update the corresponding sql db columns where the values in the dataframe differ from those in the database.  I really appreciate you taking a look at this.  I have spent hours on SO, the internet in general, tried many approaches and nothing is working.  Thanks.</p>