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

Answer 1

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