SQLAlchemy: Check if object is already present in table

Question

I have a class Item whose id is a primary key and auto-generated. Now I read data from some other external source, create an Item object, and need to check if this object is already present in my items table. How do I do it?

Solution 1

The most efficient way is to use exists()

q = session.query(Item.id).filter(Item.email==email)
session.query(q.exists()).scalar()    # returns True or False

Solution 2

You could query for items that have the same attributes and check if the count is greater than zero.

if session.query(Item.id).filter(Item.email==newItem.email,
                                 Item.type==newItem.type).count() > 0:
    // item exists

Solution 3

You could try something similar to this:

from sqlalchemy import exc

try:
    session.add(item)
    session.commit()
except exc.IntegrityError:
    session.rollback()
    print 'This item fails one of the unique/foreign key checks!'
except:
    print 'There was another error'
    raise
else:
    print 'Everything is OK'

This has the problem that it acts upon committing, and then rolls back...

Another option, if you can check the properties individually, then this might work:

session.query(Item).filter_by(x=item.x, y=item.y).count() == 0

Comments

[kalu]

The >0 is redundant since count() returns zero if no matches are found and a positive integer otherwise.

[Brendon Crawford]

@kalu: "Explicit is better than implicit." -- The Zen of Python

[Salami]

Using count makes the database do more work then it needs to. See my answer below for an efficient method.