title: sqlalchemy integrity error
body: <p>I have a table where the primary key (id) isn't the key by which i distinguish between records. For this, I have a unique constraint for 3 columns.
  In order to be able to merge records, I've added a classmethod that retrieves the relevant record if exists, otherwise, it returns with a new record.</p>
  
  <pre><code>class Foo(Base):
      __table_args__ = (sa.UniqueConstraint('bar', 'baz', 'qux'),)
  
      id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), nullable=False, primary_key=True)
      bar = sa.Column(sa.BigInteger)
      baz = sa.Column(sa.BigInteger)
      qux = sa.Column(sa.BigInteger)
      a1 = sa.Column(sa.BigInteger)
      a2 = sa.Column(sa.BigInteger)
  
      @classmethod
      def get(cls, bar=None, baz=None, qux=None, **kwargs):
          item = session.query(cls).\
              filter(cls.bar== bar).\
              filter(cls.baz == baz).\
              filter(cls.qux == qux).\
              first()
  
          if item:
              for k, v in kwargs.iteritems():
                  if getattr(item, k) != v:
                      setattr(item, k, v)
          else:
              item = cls(bar=bar, baz=baz, qux=qux, **kwargs)
  
          return item
  </code></pre>
  
  <p>This works well most of the time, but every once in a while, I get an Integrity error when trying to merge an item:</p>
  
  <pre><code>foo = Foo.get(**item)
  session.merge(foo)
  </code></pre>
  
  <p>As I understand, this happens since merge tries to insert a record where a record having the unique fields already exists.</p>
  
  <p>Is there something wrong with the <code>get</code> function? What am I missing here?</p>
  
  <p>(BTW: I realize this might look awkward, but I need a unique sequential ID and to avoid problems with DB's not supporting sequences on non-primary-key columns, I made it this way)</p>
  
  <p><strong>Edit 1:</strong>
  Changed orm.db to session so the example would be clearer</p>
  
  <p><strong>Edit 2:</strong>
  I have this system running on several platforms and it seems that this only happens in mysql on top of Ubuntu (other platform is Oracle on top of RedHat). Also, in some weird way, it happens much more to specific end-users.
  Regarding mysql, I tried both mysql and mysql+mysqldb as connection strings but both produce this error.
  Regarding the end users, it makes no sense, and I don't know what to make of it ...
  Regarding mysql, </p>