title: Bulk upsert with SQLAlchemy
body: <p>I am working on bulk upserting lots of data into PostgreSQL with SQLAlchemy 1.1.0b, and I'm running into duplicate key errors.</p>
  
  <pre><code>from sqlalchemy import *
  from sqlalchemy.orm import sessionmaker
  from sqlalchemy.ext.automap import automap_base
  
  import pg
  
  engine = create_engine("postgresql+pygresql://" + uname + ":" + passw + "@" + url)
  
  # reflectively load the database.
  metadata = MetaData()
  metadata.reflect(bind=engine)
  session = sessionmaker(autocommit=True, autoflush=True)
  session.configure(bind=engine)
  session = session()
  base = automap_base(metadata=metadata)
  base.prepare(engine, reflect=True)
  
  table_name = "arbitrary_table_name" # this will always be arbitrary
  mapped_table = getattr(base.classses, table_name)
  # col and col2 exist in the table.
  chunks = [[{"col":"val"},{"col2":"val2"}],[{"col":"val"},{"col2":"val3"}]]
  
  for chunk in chunks:
      session.bulk_insert_mappings(mapped_table, chunk)
      session.commit()
  </code></pre>
  
  <p>When I run it, I get this:</p>
  
  <pre><code>sqlalchemy.exc.IntegrityError: (pg.IntegrityError) ERROR:  duplicate key value violates unique constraint &lt;constraint&gt;
  </code></pre>
  
  <p>I can't seem to properly instantiate the <code>mapped_table</code> as a <code>Table()</code> object, either.</p>
  
  <p>I'm working with time series data, so I'm grabbing data in bulk with some overlap in time ranges. I want to do a bulk upsert to ensure data consistency.</p>
  
  <p>What's the best way to do a bulk upsert with a large data set? I know <a href="https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql">PostgreSQL support upserts</a> now, but I'm not sure how to do this in SQLAlchemy.</p>