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?

Denis Otkidach · Accepted Answer · 2011-04-21 04:59:06Z

11

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

edited Apr 21, 2011 at 4:59

answered Apr 20, 2011 at 12:41

Denis Otkidach

33.5k8 gold badges86 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

duduklein Over a year ago

I'll be the only one inserting data there, so I'll make sure this does not happen. My question is specifically about how sqlalchemy handles uniqueness of a field

Denis Otkidach Over a year ago

@duduklein It doesn't handle uniqueness. And my answer describes why.

acslater00 · Accepted Answer · 2011-04-25 15:53:55Z

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.

Stephen Fuhry · Accepted Answer · 2017-12-15 10:14:00Z

2

From the docs

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

edited Dec 15, 2017 at 10:14

user8060120

answered Aug 8, 2012 at 13:44

Stephen Fuhry

13.1k6 gold badges59 silver badges57 bronze badges

6 Comments

Jacob Pavlock Over a year ago

What's the different between this and using unique=True as presented in the original question?

Stephen Fuhry Over a year ago

unique=True does not afford the opportunity to specify a unique name for the index, which will cause problems with alembic. See: alembic.sqlalchemy.org/en/latest/naming.html

Jacob Pavlock Over a year ago

Okay, but does this answer the original question? I think some more explanation of your relation to the OP's question could be helpful.

Stephen Fuhry Over a year ago

Not sure, but this question is almost a decade old now, and this isn't the accepted answer.

Jacob Pavlock Over a year ago

that doesn't mean it isn't still discovered often. I'm just trying to understand your answer and explain my -1.

|

Jan · Accepted Answer · 2012-10-28 00:43:21Z

1

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

edited Oct 28, 2012 at 0:43

user1114055

answered Feb 13, 2012 at 1:33

Jan

191 bronze badge

Collectives™ on Stack Overflow

How does SqlAlchemy handle unique constraint in table definition

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related