SQL查询:
Select *
from table_name
where ID in (123)
and date in (Select max(date)
from table_name
where ID in (123))
我想在上面的SQL查询中一次传递下面提到的列表值,并为列表包中的每个ID收集结果: cx_Oracle
我的尝试:
import cx_oracle
List= {123, 234,345,....}
List1 = []
query = " Select * from table_name where ID in (%s)
and date in (Select max(date) from table_name where ID in (%s))"
for j in List:
cursor1 = db_ora.cursor()
tb = cursor1.execute(query, params= List )
for i in tb:
List1.append(i)
提前谢谢你,如果你需要更多的细节,请告诉我
发布于 2018-12-18 00:16:48
如果要使其与原始代码相似,则可以使用字符串格式
Python 2
import cx_oracle
List= [123, 234,345,....]
List1 = []
masterQuery = " Select * from table_name where ID in (%s)
and date in (Select max(date) from table_name where ID in (%s))"
for j in List:
cursor1 = db_ora.cursor()
newQuery = masterQuery % (j, j)
tb = cursor1.execute(newQuery)
for i in tb:
List1.append(i)
Python 3
import cx_oracle
List= [123, 234,345,....]
List1 = []
masterQuery = " Select * from table_name where ID in {}
and date in (Select max(date) from table_name where ID in {})"
for j in List:
cursor1 = db_ora.cursor()
newQuery = masterQuery.format(j, j)
tb = cursor1.execute(newQuery)
for i in tb:
List1.append(i)
发布于 2018-12-18 00:15:32
据我所知,Oracle不会接受这样的列表作为有效参数。或者将该值列表存储到单独的表中,并将其用作查询的源,例如
and t.date in (select max(t1.date) from table_name t1
where t1.id in (select st.id from some_table st)
)
或者,如果可能,将逗号分隔值字符串拆分成行,例如
and t.date in (select max(t1.date) from table_name t1
where t1.id in (select regexp_substr(%s, '[^,]+', 1, level)
from dual
connect by level <= regexp_count(%s, ',') + 1
)
)
另外,我建议您在列名前面加上表别名,以避免可能的混淆。
https://stackoverflow.com/questions/53818555
复制相似问题