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
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.
