CODEWITHSUNDEEP
pythondatabasesqlitedatabase-designsqlalchemy
john
john

Reputation: 3103

SqlAlchemy IntegrityError

I'm trying to get a whole bunch of data off of facebook, and add objects into the database as I process them. For likes, for example, I am just making them the ID of the person who issued the like. However, when I add more than one like I will encounter an integrity error in sqlalchemy because the primary keys are not unique.

What is the best way to handle this? Do I have to do a query every time I'm about to add a new like to the database, find out if its there, increase its count if it exists, and create a new entry if it doesn't?

Doesn't that seem like a lot of querying? What do you think is best to do?

Upvotes: 4

Views: 2823

Answers (1)

Denis Otkidach
Denis Otkidach

Reputation: 33200

Do you realy need to optimize it for heavy load? Probably not, having you are using SQLite. The simple solution in this case is much better:

class Like(Base):
    __tablename__ = 'Like'
    id = Column(Integer, primary_key=True)
    counter = Column(Integer, nullable=False, default=0)


o = session.merge(Like(id=1))
session.flush()  # Required when it's new record
o.counter = Like.counter+1
session.commit()

There is a race condition between check and insertion, but I believe it won't beat you in practice.

When you realy need to optimize it a bit or fix this race condition, there is a INSERT OR IGNORE in SQLite to avoid check (there are 2 separate statements are executed yet):

clause = Like.__table__.insert(prefixes=['OR IGNORE'],
                               values=dict(id=1, counter=0))
session.execute(clause)
o = session.merge(Like(id=1, counter=Like.counter+1))
session.commit()

And finally there is a way to do it in single statement using INSERT OR REPLACE and subselect (there are other ways to do such thing in most other databases, e.g. ON DUPLICATE KEY in MySQL), but I doubt it will give you noticeable performance gain:

old = session.query(Like.counter).filter_by(id=1).statement.as_scalar()
new = func.ifnull(old, 0) + 1
clause = Like.__table__.insert(prefixes=['OR REPLACE'],
                               values=dict(id=1, counter=new))
session.execute(clause)

Upvotes: 6

Related Questions