Programmatically generate sql alchemy?

Question

I have an input form with 3 input fields. The user can choose to fill between 1 and 3 of the input fields. I am generating a SQL alchemy query based on their input. If the user has not filled out a field, then it should not be part of the criteria for the query.

I suppose I could write code with 7 branches -- with a SQL alchemy query for each possible query (ex. if user has filled out fields 2 and 3 but not 1, then query looks like this...).

But I would think there would be a way to programmatically generate a SQL alchemy statement to avoid this sort of complex branching. Is there such a method?

van · Accepted Answer · 2014-06-28 19:47:55Z

1

Just chain the filters on demand and avoid branching completely:

q = session.query(Person) # .join(...).filter(...).order_by(...)

# optional filters
if form.filter_name:
    q = q.filter(Person.name == form.filter_name)
if form.filter_minimum_age:
    q = q.filter(Person.minimum_age >= form.minimum_age)
if form.tag_name:
    q = q.join(Tag, Person.tags).filter(Tag.name.like('%' + form.tag_name + '%'))

# iterate over results (only now the DB will be queried)
for person in q.all():
    # do whatever

edited Jun 28, 2014 at 19:47

answered Jun 27, 2014 at 18:02

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bernie2436 Over a year ago

and that will only execute one query? at the end of the if statements? when is the db actually queried?

TNT Over a year ago

@akh2103 when you access the results e.g. by res = q.all()

Collectives™ on Stack Overflow

Programmatically generate sql alchemy?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related