Database API: How to deal with multi where conditi

Question

Like this question passing-param-to-db-execute-for-where-in-int-list

But more where condition, how to deal with it?

select * from tbl where params1='p1' and params2='p2' and params3 in ('v1', 'v2', v3')

If I want pass params1, params2, params3 to SQL statement using Python DB API, any suggestion?

BTW: Database is Oracle

Answer 1

You need to use SQL parameters for each value.

For the in statement, that means you need to generate the parameters:

sql = 'select * from tbl where params1=:params1 and params2=:params2 and params3 in ({})'.format(
    ', '.join(['params3_' + str(i) for i in range(len(params3_value))])

where I assume that params3_value is a list of values to test params3 against. If params3_value is 3 elements (like ['v1', 'v2', 'v3']) then the generated SQL will look like:

select * from tbl where params1=:params1 and params2=:params2 and params3 in (:params3_0, :params3_1, :params3_2)

Then pass those paramaters to the cursor.execute() call:

params = {'params1': params1_value, 'params2': params2_value}
for i, val in enumerate(params3_value):
    params['params3_' + str(i)] = value
cursor.execute(sql, {params})

I used the :name named SQL parameter style here as that is what the cx_Oracle uses. Consult your database connector documentation for the exact supported parameter styles.

The :named named SQL parameter style requires that you pass in parameters as a dictionary, so the above code generates the right keys for the params3_value items.