How to get rows which match a list of 3-tuples conditions with SQLAlchemy

Having a list of 3-tuples :

[(a, b, c), (d, e, f)]

I want to retrieve all the rows from a table where 3 columns matches the tuples. FOr this example, the query WHERE clause could be something like this :

   (column_X = a AND column_Y = b AND column_Z = c)
OR (column_X = d AND column_Y = e AND column_Z = f)

How can I create such a request using SQLAlchemy ? In my case the 3-tuples list will contains hundred of elements, and I'm looking for the best scallable solution.

Thanks for your help,

Solution

Easiest way would be using SQLAlchemy-provided tuple_ function:

from sqlalchemy import tuple_

session.query(Foo).filter(tuple_(Foo.a, Foo.b, Foo.c).in_(items))

This works with PostgreSQL, but breaks with SQLite. Not sure about other database engines.

Fortunately there's a workaround that should work on all databases.

Start by mapping out all the items with the and_ expression:

conditions = (and_(c1==x, c2==y, c3==z) for (x, y, z) in items)

And then create an or_ filter that encloses all the conditions:

q.filter(or_(*conditions))

Here's a simple example:

#/usr/bin/env python
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer
from sqlalchemy.sql import and_, or_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///')
session = sessionmaker(bind=engine)()
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    a = Column(Integer)
    b = Column(Integer)
    c = Column(Integer)

    def __init__(self, a, b, c):
        self.a = a
        self.b = b
        self.c = c

    def __repr__(self):
        return '(%d %d %d)' % (self.a, self.b, self.c)

Base.metadata.create_all(engine)

session.add_all([Foo(1, 2, 3), Foo(3, 2, 1), Foo(3, 3, 3), Foo(1, 3, 4)])
session.commit()
items = ((1, 2, 3), (3, 3, 3))
conditions = (and_(Foo.a==x, Foo.b==y, Foo.c==z) for (x, y, z) in items)
q = session.query(Foo)
print q.all()
q = q.filter(or_(*conditions))
print q
print q.all()

Which outputs:

$ python test.py 
[(1 2 3), (3 2 1), (3 3 3), (1 3 4)]
SELECT foo.id AS foo_id, foo.a AS foo_a, foo.b AS foo_b, foo.c AS foo_c 
FROM foo 
WHERE foo.a = :a_1 AND foo.b = :b_1 AND foo.c = :c_1 OR foo.a = :a_2 AND foo.b = :b_2 AND foo.c = :c_2
[(1 2 3), (3 3 3)]