How to construct a slightly more complex filter using "or_" or "and_" in SQLAlchemy

Question

I'm trying to do a very simple search from a list of terms

terms = ['term1', 'term2', 'term3']

How do I programmatically go through the list of terms and construct the conditions from the list of terms so that I can make the query using filter and or_ or _and?

query.filter(or_(#something constructed from terms))

Solution 1

If you have a list of terms and want to find rows where a field matches one of them, then you could use the in_() method:

terms = ['term1', 'term2', 'term3']
query.filter(Cls.field.in_(terms))

If you want to do something more complex, then or_() and and_() take ClauseElement objects as parameters. ClauseElement and its subclasses basically represent the SQL AST of your query. Typically, you create clause elements by invoking a comparison operator on Column or InstrumentedAttribute objects:

# Create the clause element
clause = (users_table.columns['name'] == "something")
#    you can also use the shorthand users_table.c.name

# The clause is a binary expression ...
print(type(clause))
#    <class 'sqlalchemy.sql.expression._BinaryExpression'>
# ... that compares a column for equality with a bound value.
print(type(clause.left), clause.operator, type(clause.right))
#    <class 'sqlalchemy.schema.Column'>, <built-in function eq>,
#    <class 'sqlalchemy.sql.expression._BindParamClause'>

# str() compiles it to SQL
print(str(clause)) 
# users.name = ?

# You can also do that with ORM attributes
clause = (User.name == "something")
print(str(clause))
# users.name = ?

You can handle clause elements representing your conditions like any Python objects, put them into lists, compose them into other clause elements, etc. So you can do something like this:

# Collect the separate conditions to a list
conditions = []
for term in terms:
    conditions.append(User.name == term)

# Combine them with or to a BooleanClauseList
condition = or_(*conditions)

# Can now use the clause element as a predicate in queries
query = query.filter(condition)
# or to view the SQL fragment
print(str(condition))
#    users.name = ? OR users.name = ? OR users.name = ?

Solution 2

Assuming that your terms variable contains valid SQL statement fragments, you can simply pass terms preceded by an asterisk to or_ or and_:

>>> from sqlalchemy.sql import and_, or_
>>> terms = ["name='spam'", "email='[email protected]'"]
>>> print or_(*terms)
name='spam' OR email='[email protected]'
>>> print and_(*terms)
name='spam' AND email='[email protected]'

Note that this assumes that terms contains only valid and properly escaped SQL fragments, so this is potentially unsafe if a malicious user can access terms somehow.

Instead of building SQL fragments yourself, you should let SQLAlchemy build parameterised SQL queries using other methods from sqlalchemy.sql. I don't know whether you have prepared Table objects for your tables or not; if so, assume that you have a variable called users which is an instance of Table and it describes your users table in the database. Then you can do the following:

from sqlalchemy.sql import select, or_, and_
terms = [users.c.name == 'spam', users.c.email == '[email protected]']
query = select([users], and_(*terms))
for row in conn.execute(query):
    # do whatever you want here

Here, users.c.name == 'spam' will create an sqlalchemy.sql.expression._BinaryExpression object that records that this is a binary equality relation between the name column of the users table and a string literal that contains spam. When you convert this object to a string, you will get an SQL fragment like users.name = :1, where :1 is a placeholder for the parameter. The _BinaryExpression object also remembers the binding of :1 to 'spam', but it won't insert it until the SQL query is executed. When it is inserted, the database engine will make sure that it is properly escaped. Suggested reading: SQLAlchemy's operator paradigm

If you only have the database table but you don't have a users variable that describes the table, you can create it yourself:

from sqlalchemy import Table, MetaData, Column, String, Boolean
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('email', String),
    Column('active', Integer)
)

Alternatively, you can use autoloading which queries the database engine for the structure of the database and builds users automatically; obviously this is more time-consuming:

users = Table('users', metadata, autoload=True)

Solution 3

I had the same issue in"SQLAlchemy: an efficient/better select by primary keys?":

terms = ['one', 'two', 'three']
clauses = or_( * [Table.field == x for x in terms] )
query = Session.query(Table).filter(clauses)