How does SqlAlchemy handle unique constraint in table definition

Question

I have a table with the following declarative definition:

class Type(Base):
    __tablename__ = 'Type'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique = True)
    def __init__(self, name):
        self.name = name

The column "name" has a unique constraint, but I'm able to do

type1 = Type('name1')
session.add(type1)
type2 = Type(type1.name)
session.add(type2)

So, as can be seen, the unique constraint is not checked at all, since I have added to the session 2 objects with the same name.

When I do session.commit(), I get a mysql error since the constraint is also in the mysql table.

Is it possible that sqlalchemy tells me in advance that I can not make it or identifies it and does not insert 2 entries with the same "name" columm? If not, should I keep in memory all existing names, so I can check if they exist of not, before creating the object?

Answer 1

SQLAlechemy doesn't handle uniquness, because it's not possible to do good way. Even if you keep track of created objects and/or check whether object with such name exists there is a race condition: anybody in other process can insert a new object with the name you just checked. The only solution is to lock whole table before check and release the lock after insertion (some databases support such locking).

Answer 2

AFAIK, sqlalchemy does not handle uniqueness constraints in python behavior. Those "unique=True" declarations are only used to impose database level table constraints, and only then if you create the table using a sqlalchemy command, i.e.

Type.__table__.create(engine)

or some such. If you create an SA model against an existing table that does not actually have this constraint present, it will be as if it does not exist.

Depending on your specific use case, you'll probably have to use a pattern like

try:
  existing = session.query(Type).filter_by(name='name1').one()
  # do something with existing
except:
  newobj = Type('name1')
  session.add(newobj)

or a variant, or you'll just have to catch the mysql exception and recover from there.