数据库API:如何处理Python中的多条件条件 [英] Database API: How to deal with multi where condition in Python
问题描述
像这个问题 passing-param-to-
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')
如果我想要使用将params1,params2,params3传递给SQL语句href =http://www.python.org/dev/peps/pep-0249/ =nofollow> Python DB API ,有什么建议吗?
BTW:数据库是Oracle
BTW: Database is Oracle
推荐答案
您需要为每个值使用SQL参数。
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))])
假设 params3_value
是测试 params3
的值的列表。如果 params3_value
是3个元素(如 ['v1','v2','v3']
SQL将如下所示:
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)
然后将这些参数传递到 cursor.execute ()
call:
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})
我使用:name
命名的SQL参数样式什么 cx_Oracle
使用。
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.
:named
命名的SQL参数样式要求您将参数传递为字典,因此上述代码为 params3_value
项目生成正确的键。
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.
这篇关于数据库API:如何处理Python中的多条件条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!