SQLAlchemy check before insert in Python

Question

I have a question about a more proper/efficient way how to check if a entry is already in a database table.

Suppose that I have a table called Foo with single attribute and Name which is also unique.

What is a better way to check before insert if there is already an entry with such name? I can think of two alternatives:

from sqlalchemy import MetaData, Table, Column, String, \
create_engine
from sqlalchemy.orm import mapper, relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import IntegrityError

Base = declarative_base()

class Foo(Base):
    __tablename__ = "foo"

    name = Column(String(), primary_key=True)

    @staticmethod
    def silent_insert1(name):
            if not session.query(Foo).filter(Foo.name == name).first():
                    foo = Foo()
                    foo.name = name
                    session.add(foo)
                    session.commit()
            else:
                    print("already exists 1")

    @staticmethod
    def silent_insert2(name):
            try:
                    foo = Foo()
                    foo.name = name
                    session.add(foo)
                    session.commit()
            except(IntegrityError):
                    print("already exists 2")


engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()

Foo.silent_insert1(1)
Foo.silent_insert2(1)

In the first silent_insert1, there is of course a race condition. The second seems more bullet proof.

Which one is better? Is even a better way or recommended way how to check this?

Thank you

http://stackoverflow.com/a/21146492/443900 has a generic solution similar to your second method. — Lee, Sep 08 '15 at 16:41

score 10 · Accepted Answer · answered Sep 08 '15 at 17:31

10

you should just put the session.add in the try/except

secondly this is a a question of philosophy. you will not get a definitive answer.

the two styles are know as look before you leap (LBYL) , this style uses an if statement ahead of time to see if you should do it.

the 2nd style is known as Easier To Ask Forgiveness, than Permission(EAFP), this style assumes that it will work and catches and handles the exception that is raised in the event that the rules are broken. as a general rule python programmers tend to favor EAFP, but like any rule there are many many many exceptions

answered Sep 08 '15 at 17:31

Joran Beasley

110,522
12
160
179

3

I think you'd also want a `session.flush` before the `try`, to ensure any previous errors would be raised before the start of the block. – Lee Sep 08 '15 at 18:28
What is the exception that violtes UniqueKeyConstraiints? – technazi Jul 24 '20 at 06:17

SQLAlchemy check before insert in Python

1 Answers1