CODEWITHSUNDEEP
sqlalchemyunique
duduklein
duduklein

Reputation: 10604

How does SqlAlchemy handle unique constraint in table definition

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?

Upvotes: 11

Views: 15313

Answers (4)

Stephen Fuhry
Stephen Fuhry

Reputation: 13009

From the docs

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

Upvotes: 2

Jan
Jan

Reputation: 19

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

You should create that object when the first exception occurs, if the second occurs you're screwed anyway and shouldn't make is worse.

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

Upvotes: 1

acslater00
acslater00

Reputation: 417

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.

Upvotes: 9

Denis Otkidach
Denis Otkidach

Reputation: 33200

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).

Upvotes: 11

Related Questions