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

by

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)]

More Related Contents:

  1. How to execute raw SQL in Flask-SQLAlchemy app
  2. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  3. sqlalchemy: how to join several tables by one query?
  4. Python, SQLAlchemy pass parameters in connection.execute
  5. Complex foreign key constraint in SQLAlchemy
  6. SQLAlchemy: how to filter date field?
  7. How to execute “left outer join” in SqlAlchemy
  8. Getting random row through SQLAlchemy
  9. Select NULL Values in SQLAlchemy
  10. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  11. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  12. VALUES clause in SQLAlchemy
  13. flask sqlalchemy query with keyword as variable
  14. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  15. How to create a large pandas dataframe from an sql query without running out of memory?
  16. Passing table name as a parameter in psycopg2
  17. Getting the SQL from a Django QuerySet [duplicate]
  18. sqlalchemy unique across multiple columns
  19. return SQL table as JSON in python
  20. How to create a new database using SQLAlchemy?
  21. Multi-threaded use of SQLAlchemy
  22. How to get column names from SQLAlchemy result (declarative syntax)
  23. How to see the raw SQL queries Django is running?
  24. pyodbc insert into sql
  25. Python String Formats with SQL Wildcards and LIKE
  26. SQLAlchemy Joinedload filter column
  27. SQLAlchemy func.count on boolean column
  28. sqlalchemy existing database query
  29. Flask-SQLAlchemy Constructor
  30. convert selected datetime to date in sqlalchemy

Leave a Comment