title: How do I perform an UPDATE of existing rows of a db table using a Pandas DataFrame?
body: <p>I am attempting to query a subset of a MySql database table, feed the results into a Pandas DataFrame, alter some data, and then write the updated rows back to the same table.  My table size is ~1MM rows, and the number of rows I will be altering will be relatively small (&lt;50,000) so bringing back the entire table and performing a <code>df.to_sql(tablename,engine, if_exists='replace')</code> isn't a viable option.  Is there a straightforward way to UPDATE the rows that have been altered without iterating over every row in the DataFrame?</p>
  
  <p>I am aware of this project, which attempts to simulate an "upsert" workflow, but it seems it only accomplishes the task of inserting new non-duplicate rows rather than updating parts of existing rows:</p>
  
  <p><a href="https://github.com/ryanbaumann/Pandas-to_sql-upsert" rel="noreferrer">GitHub Pandas-to_sql-upsert</a></p>
  
  <p>Here is a skeleton of what I'm attempting to accomplish on a much larger scale:</p>
  
  <pre><code>import pandas as pd
  from sqlalchemy import create_engine
  import threading
  
  #Get sample data
  d = {'A' : [1, 2, 3, 4], 'B' : [4, 3, 2, 1]}
  df = pd.DataFrame(d)
  
  engine = create_engine(SQLALCHEMY_DATABASE_URI)
  
  #Create a table with a unique constraint on A.
  engine.execute("""DROP TABLE IF EXISTS test_upsert """)
  engine.execute("""CREATE TABLE test_upsert (
                    A INTEGER,
                    B INTEGER,
                    PRIMARY KEY (A)) 
                    """)
  
  #Insert data using pandas.to_sql
  df.to_sql('test_upsert', engine, if_exists='append', index=False)
  
  #Alter row where 'A' == 2
  df_in_db.loc[df_in_db['A'] == 2, 'B'] = 6
  </code></pre>
  
  <p>Now I would like to write <code>df_in_db</code> back to my <code>'test_upsert'</code> table with the updated data reflected.</p>
  
  <p>This SO question is very similar, and one of the comments recommends using an "sqlalchemy table class" to perform the task.</p>
  
  <p><a href="https://stackoverflow.com/questions/29607222/update-existing-row-in-database-from-pandas-df">Update table using sqlalchemy table class</a></p>
  
  <p>Can anyone expand on how I would implement this for my specific case above if that is the best (only?) way to implement it?</p>