title: How to update a Postgres table column using a pandas data frame?
body: <p>I am adding a single column to a Postgres table with 100+ columns via Django ( a new migration). How can I update a column in a PostgreSQL table with the data from a <code>pandas</code> data_frame? The <strong>pseudo-code</strong> for Postgres SQL <a href="http://www.postgresqltutorial.com/postgresql-update/" rel="noreferrer">UPDATE</a> would be: </p>
  
  <pre><code>UPDATE wide_table wt
  SET wt.z = df.z
  WHERE date = 'todays_date'
  </code></pre>
  
  <p>The reason for doing it this way is that I am computing a column in the <code>data_frame</code> using a CSV that is in <code>S3</code> (this is <code>df.z</code>). The docs for <a href="http://www.postgresqltutorial.com/postgresql-update/" rel="noreferrer">Postgres update</a> are straightforward to use, but I am unsure how to do this via Django, sqlalchemy, pyodbc, or the like. </p>
  
  <p>I apologize if this is a bit convoluted. A small and incomplete example would be: </p>
  
  <h3>Wide Table (pre-update column <code>z</code>)</h3>
  
  <pre><code>identifier    |      x       |      y      |      z       |      date       
  foo           |      2       |      1      |     0.0      |      ...           
  bar           |      2       |      8      |     0.0      |      ...      
  baz           |      3       |      7      |     0.0      |      ...      
  foo           |      2       |      8      |     0.0      |      ...      
  foo           |      1       |      5      |     0.0      |      ...      
  baz           |      2       |      8      |     0.0      |      ...      
  bar           |      9       |      3      |     0.0      |      ...      
  baz           |      2       |      3      |     0.0      |      ...      
  
  </code></pre>
  
  <h3>Example Python snippet</h3>
  
  <pre><code>def apply_function(identifier):
      # Maps baz-&gt; 15.0, bar-&gt; 19.6, foo -&gt; 10.0 for single date
      df = pd.read_csv("s3_file_path/date_file_name.csv")
      # Compute 'z' based on identifier and S3 csv
      return z
  
  postgres_query = "Select identifier from wide_table"
  df = pd.read_sql(sql=postgres_query, con=engine)
  df['z'] = df.identifier.apply(apply_function)
  
  # Python / SQL Update Logic here to update Postgres Column
  ???
  </code></pre>
  
  <h3>Wide Table (post-update column <code>z</code>)</h3>
  
  <pre><code>identifier    |      x       |      y      |      z        |      date 
  foo           |      2       |      1      |     10.0      |      ...     
  bar           |      2       |      8      |     19.6      |      ... 
  baz           |      3       |      7      |     15.0      |      ... 
  foo           |      2       |      8      |     10.0      |      ... 
  foo           |      1       |      5      |     10.0      |      ... 
  baz           |      2       |      8      |     15.0      |      ... 
  bar           |      9       |      3      |     19.6      |      ... 
  baz           |      2       |      3      |     15.0      |      ... 
  
  </code></pre>
  
  <p>NOTE: The values in z will change daily so simply creating another table to hold these <code>z</code> values is not a great solution. Also, I'd really prefer to avoid deleting all of the data and adding it back.</p>