CODEWITHSUNDEEP
pythonormerror-handlingflasksqlalchemy
Tony
Tony

Reputation: 1057

How to insert unique data / handle duplicate data in a database

Assume it's a model definition:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    account = Column(String(255), unique=True, index=True)
    password = Column(String(128))
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, onupdate=datetime.datetime.utcnow,
                        default=datetime.datetime.utcnow)

Suppose I have inserted Tom in database but you insert it again. How do you prefer which handle method?

Case A

Let it happened error and then handle the error.

try:
    user = User(account='Tom', ...)
    session.add(user)
    session.commit()
except:
    # do something

Case B

To search the user whether it exists or not. If it doesn't exist, it will create.

user = session.query(User).filter(User.account == 'Tom').one()
if user:
    # do something
user = User(account='Tom', ...)
session.add(user)
session.commit()

Which is better?

Upvotes: 4

Views: 1787

Answers (2)

bakkal
bakkal

Reputation: 55448

Permissions vs Forgiveness

In Python it's more idiomatic to ask for forgiveness than permission (EAFP)

EAFP: Easier to ask for forgiveness than permission. This common Python coding style assumes the existence of valid keys or attributes and catches exceptions if the assumption proves false. This clean and fast style is characterized by the presence of many try and except statements. The technique contrasts with the LBYL style common to many other languages such as C.

Source: https://docs.python.org/3.4/glossary.html

In this regard your Case A method is better.

Race Condition in the "look before you leap version" (LBYL)

Whatever check that you do to make sure that there are no duplicates, can be outdated by time you do the insert. So it's very possible that right after your check gives the green light, another instance of your program (e.g. another Flask worker) adds a row.

This is especially damaging to you, because your Case B as it is, doesn't expect any exceptions to occurs if that check clears, which can stall the execution of the program altogether. So Case A wins here easily.

Performance: Two queries vs One query + a possible exception

Also your second method always happens in two queries (one to check and one to add), whereas the first one will only throw an exception in case of a duplicate

So I'd say your Case A is better (the one that handles the exception only when there is a duplicate)

Don't cast a wide net though, catch a focused Exception

The only nitpick I see is that you should NOT use except: (too wide a net), but use a specific Exception that gets throw when you insert a duplicate row.

Assuming what gets throw in that case is an IntegrityError, we should do

try:
     # Insert a potentially duplicate row
except IntegrityError:
     # Deal with the case

Upvotes: 3

Chitrank Dixit
Chitrank Dixit

Reputation: 4051

In Web Application Development you would need to enhance your application over time and reducing the database query is one of the enhancements that you would need in some time. Therefore the Case A is more better than Case B. Cause It runs only one query if user is found (1 Query to update the data) if use is not found (1 Query to create).

I would also like to recommend the awesome book on Django web application development Two Scoops of Django by Daniel Roy Greenfeld and Audrey Roy Greenfeld and High Performance Django by Peter Baumgartner and Yann Malet.

Upvotes: 1

Related Questions