title: Bulk Upsert with SQLAlchemy Postgres
body: <p>I'm following the SQLAlchemy documentation <a href="https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html" rel="noreferrer">here</a> to write a bulk upsert statement with Postgres. For demonstration purposes, I have a simple table <code>MyTable</code>:</p>
  
  <pre class="lang-py prettyprint-override"><code>class MyTable(base):
      __tablename__ = 'mytable'
      id = Column(types.Integer, primary_key=True)
      test_value = Column(types.Text)
  </code></pre>
  
  <p>Creating a generic insert statement is simple enough:</p>
  
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy.dialects import postgresql
  
  values = [{'id': 0, 'test_value': 'a'}, {'id': 1, 'test_value': 'b'}]
  insert_stmt = postgresql.insert(MyTable.__table__).values(values)
  </code></pre>
  
  <p>The problem I run into is when I try to add the "on conflict" part of the upsert.</p>
  
  <pre class="lang-py prettyprint-override"><code>update_stmt = insert_stmt.on_conflict_do_update(
      index_elements=[MyTable.id],
      set_=dict(data=values)
  )
  </code></pre>
  
  <p>Trying to execute this statement yields a <code>ProgrammingError</code>:</p>
  
  <pre><code>from sqlalchemy import create_engine
  engine = create_engine('postgres://localhost/db_name')
  
  engine.execute(update_stmt)
  
  &gt;&gt;&gt; ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'dict'
  </code></pre>
  
  <p>I think my misunderstanding is in constructing the statement with the <code>on_conflict_do_update</code> method. Does anyone know how to construct this statement? I have looked at other questions on StackOverflow (eg. <a href="https://stackoverflow.com/questions/38579049/bulk-upsert-with-sqlalchemy">here</a>) but I can't seem to a way to address the above error.</p>