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?

Is it a ``get_or_create`` scenario? If so: http://stackoverflow.com/questions/2546207/does-sqlalchemy-have-an-equivalent-of-djangos-get-or-create — Demian Brecht, Oct 05 '12 at 15:07
@missingfaktor how do you know if it already exists? Based on the primary key? Or some other unique key? — jadkik94, Oct 05 '12 at 16:08

score 22 · Accepted Answer · answered May 15 '16 at 11:54

22

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

answered May 15 '16 at 11:54

Salami

2,849
4
24
33

Nathan Villaescusa · Answer 2 · 2016-04-03T05:13:19.747

13

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

edited Apr 03 '16 at 05:13

answered Oct 05 '12 at 16:24

Nathan Villaescusa

17,331
4
53
56

1

The `>0` is redundant since count() returns zero if no matches are found and a positive integer otherwise. – kalu Mar 17 '14 at 14:43
9

@kalu: "Explicit is better than implicit." -- The Zen of Python – Brendon Crawford Apr 30 '14 at 00:17
3

Using count makes the database do more work then it needs to. See my answer below for an efficient method. – Salami May 15 '16 at 11:56

score 3 · Answer 3 · answered Oct 05 '12 at 16:32

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

SQLAlchemy: Check if object is already present in table

3 Answers3