Peewee query based on columns known only at runtime

Question

I'm currently constructing an SQL query (in Python) along the lines of:

query = "SELECT * FROM Table WHERE (" + \
    " OR ".join([firstType+'=1' for firstType in firstTypes]) + \
    ") AND (" + \
    " OR ".join([secondType+'=1' for secondType in secondTypes]) + \
    ")"

which, given two lists firstType=['B','F'] and secondType=['a','d'] produces the query

SELECT * FROM Table WHERE ( ('B'=1 OR 'F'=1) AND ('a'=1 OR 'd'=1) )

which I execute with Table.raw(query).

I know how to produce that particular query in Peewee, something like:

Table.select().where( (Table.B=1 | Table.F=1) & (Table.a=1 | Table.d=1) )

but the problem is that I don't know the contents of the two lists (i.e. the columns) in advance.

How do I construct a Peewee query dynamically based on (potentally many) columns which are known only at runtime?

Answer 1

It's quite easy -- you just use field = getattr(MyModel, 'field_name') or alternatively field = MyModel._meta.fields['field_name'].

Then you would presumably generate a list of expressions:

data = {'field_a': 1, 'field_b': 33, 'field_c': 'test'}
clauses = []
for key, value in data.items():
    field = MyModel._meta.fields[key]
    clauses.append(field == value)

To AND all the clauses together, you can:

import operator
expr = reduce(operator.and_, clauses) # from itertools import reduce in Py3

To OR them:

import operator
expr = reduce(operator.or_, clauses)

The last step is just to drop it into the where() clause of a query.