title: Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style)
body: <p>My application is using a scoped session and the declarative style of SQLALchemy. It's a web-app and a lot of the DB insertions are executed by <code>Celery</code>, a task scheduler.</p>
  
  <p>Typically, when deciding to insert an object, my code might do something along the following lines:</p>
  
  <pre><code>from schema import Session
  from schema.models import Bike
  
  pk = 123 # primary key
  bike = Session.query(Bike).filter_by(bike_id=pk).first()
  if not bike: # no bike in DB
      new_bike = Bike(pk, "shiny", "bike")
      Session.add(new_bike)
      Session.commit()
  </code></pre>
  
  <p>The issue here is that because a lot of this is done by asynchronous workers, it's possible for one working to be halfway though inserting a <code>Bike</code> with <code>id=123</code>, while another one is checking for its existence. In this case the second worker will try and insert a row with the same primary key, and SQLAlchemy will raise an <code>IntegrityError</code>.</p>
  
  <p>I can't for the life of me find a nice way to deal with this issue apart from swapping out <code>Session.commit()</code> for:</p>
  
  <pre><code>'''schema/__init__.py'''
  from sqlalchemy.orm import scoped_session, sessionmaker
  Session = scoped_session(sessionmaker())
  
  def commit(ignore=False):
      try:
          Session.commit()
      except IntegrityError as e:
          reason = e.message
          logger.warning(reason)
  
          if not ignore:
              raise e
  
          if "Duplicate entry" in reason:
              logger.info("%s already in table." % e.params[0])
              Session.rollback()
  </code></pre>
  
  <p>And then everywhere I have <code>Session.commit</code> I now have <code>schema.commit(ignore=True)</code> where I don't mind that the row is not inserted again.</p>
  
  <p>To me this seems very brittle because of the string checking. Just as an FYI, when an <code>IntegrityError</code> is raised it looks like this:</p>
  
  <pre><code>(IntegrityError) (1062, "Duplicate entry '123' for key 'PRIMARY'")
  </code></pre>
  
  <p>So of course is the primary key I was inserting was something like <code>Duplicate entry is a cool thing</code> then I suppose I could miss <code>IntegrityError</code>'s which weren't actually because of duplicate primary keys.</p>
  
  <p>Are there any better approaches, which maintain the clean SQLAlchemy approach I'm using (as opposed to starting to write out statements in strings etc. . .)</p>
  
  <p>Db is MySQL (though for unit testing I like to use SQLite, and wouldn't want to hinder that ability with any new approaches).</p>
  
  <p>Cheers!</p>