CODEWITHSUNDEEP
pythonsqlpypika
cstrzelc
cstrzelc

Reputation: 115

PyPika Create Dynamic Where Statements

I am using PyPika to build out SQL queries. I would like to dynamically add 'OR' clauses depending on the input from an external party (account_list). I don't see any documentation on how to do this or if it is possible.

Example:

from pypika import Query, Table, Field, CustomFunction, Order, Criterion

account_list = [ '23456', '49375', '03948' ]


Query.from_(my_table).select(my_table.product_code, account, ) \
                .where( ( my_table.product_code.like('product_1%') | \
                 my_table.product_code.like('product_2%') ) )  \
                .where( Criterion.any([my_table.account == '23456', \
                 my_table.account == '49375', my_table.account == '03948']) )

Is it possible to make the Criterion values populate from account_list no matter how many are in the list?

Thank you very much in advance.

Upvotes: 0

Views: 4905

Answers (1)

Glenn D.J.
Glenn D.J.

Reputation: 1965

You can simply build up the criterions beforehand in a list, before passing it on to Criterion.any.

account_list = [ '23456', '49375', '03948' ]
account_criterions = [my_table.account == account for account in account_list] 

query = (
    Query.from_(my_table)
    .select(my_table.product_code, account)
    .where(my_table.product_code.like('product_1%') | my_table.product_code.like('product_2%'))
    .where(Criterion.any(account_criterions))
)

Upvotes: 2

Related Questions