How SqlAlchemy handles a unique constraint in a 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 

There is a unique restriction in the "name" column, but I can do

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

So, as you can see, the only restriction is not checked at all, since I added to the session object 2 with the same name.

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

Is it possible that sqlalchemy tells me in advance that I cannot do this or does not identify it and does not insert 2 records with the same "name" columm? If not, should all existing names be stored in memory, so I can check if they exist due to the absence before creating the object?

Answer 1

SQLAlechemy does not handle uniquness because it cannot be done in a good way. Even if you track created objects and / or check whether an object with that name exists, there is a race condition: any other process can insert a new object with the name that you just marked. The only solution is to lock the entire table before checking and release the lock after insertion (some databases support this lock).

Answer 2

AFAIK, sqlalchemy does not handle uniqueness constraints in python behavior. These "unique = True" declarations are used only to limit the restrictions of the database level table, and only if you create the table using the 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 limitation, it will be as if it did not exist.

Depending on your specific use case, you may have to use a template like

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

either an option, or you just need to catch the mysql exception and repair it there.

Answer 3

From the documents

 class MyClass(Base): __tablename__ = 'sometable' __table_args__ = ( ForeignKeyConstraint(['id'], ['remote_table.id']), UniqueConstraint('foo'), {'autoload':True} ) 

Answer 4

.one() throws two types of exceptions: sqlalchemy.orm.exc.NoResultFound and sqlalchemy.orm.exc.MultipleResultsFound

You must create this object when the first exception occurs, if the second occurs, you are screwed anyway and should not do worse.

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