title: Insert into postgreSQL table from pandas with "on conflict" update
body: <p>I have a pandas DataFrame that I need to store into the database. Here's my current line of code for inserting:</p>
  
  <pre><code>df.to_sql(table,con=engine,if_exists='append',index_label=index_col)
  </code></pre>
  
  <p>This works fine if none of the rows in <code>df</code> exist in my table. If a row already exists, I get this error:</p>
  
  <pre><code>sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key
  value violates unique constraint "mypk"
  DETAIL:  Key (id)=(42) already exists.
   [SQL: 'INSERT INTO mytable (id, owner,...) VALUES (%(id)s, %(owner)s,...']
   [parameters:...] (Background on this error at: http://sqlalche.me/e/gkpj)
  </code></pre>
  
  <p>and nothing is inserted.</p>
  
  <p>PostgreSQL has optional <code>ON CONFLICT</code> clause, which could be used to <code>UPDATE</code> the existing table rows. I read entire <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html" rel="nofollow noreferrer">pandas.DataFrame.to_sql manual page</a> and I couldn't find any way to use <code>ON CONFLICT</code> within <code>DataFrame.to_sql()</code> function.</p>
  
  <p>I have considered spliting my DataFrame in two based on what's already in the db table. So now I have two DataFrames, <code>insert_rows</code> and <code>update_rows</code>, and I can safely execute</p>
  
  <pre><code>insert_rows.to_sql(table, con=engine, if_exists='append', index_label=index_col)
  </code></pre>
  
  <p>But then, there seems to be no <code>UPDATE</code> equivalent to <code>DataFrame.to_sql()</code>. So how do I update the table using DataFrame <code>update_rows</code>?</p>