就像我必须从sql表中获取源,ID和目标,但它们存在于两个不同的表中,因此我尝试了
dom_id=[1,10,19,31,37,42,48,57,63,64,65]
for i in range(len(dom_id)):
cursor.execute("SELECT xval, yval from table1 WHERE DOMID=?",dom_id[i])
source=""
for row in cursor.fetchall():
source=float(row[0]),float(row[1])
source=','.join(map(str, source))
cursor.execute("select t1.id as pid,pxval,pyval from table2")
ID=[]
destination=""
for row_d in cursor.fetchall():
ID.append(row_d[0])
destination = float(row_d[1]),float(row_d[2])
destination = ','.join(map(str, destination))
现在,我正在使用for循环提取源代码,并使用其他for循环提取
ID
和destination
我尝试过
cursor.execute(("SELECT xval, yval from table1 WHERE DOMID=?",dom_id[i]);("select t1.id as pid,pxval,pyval from table2 t1 left join (select * from table3 where clubid=?) t2 on t1.id=t2.pid where t1.cityid in (SELECT cityid FROM table1 WHERE domid = ? group by cityid) and t1.pxval>0 and t2.distance is null order by pid",(dom_id[i],dom_id[i])))
但它给出了错误
SyntaxError: invalid syntax
有什么办法可以将两个
cursor.execute
组合到一个for循环中。有什么建议吗?
最佳答案
我认为可以这样做:
sqlQuery = "SELECT a.xval, a.yval,b.pid,pxval,b.pyval from table1 as a,
(select t1.id as pid,pxval,pyval from <tbl1> t1
left join ( select * from <tbl2> where clubid="+clubid+")
t2 on t1.id=t2.projectid where t1.cityid in ( SELECT cityid
FROM <tbl3> WHERE cbdid ="+cbdid+" group by cityid)
and t1.pxval>0 and t2.distance is null order by projectid)
as b WHERE a.DOMID="+dom_id[i]+"
cursor.execute(sqlQuery)
这将从两个表中提供所需的数据。
注意:如果两个表具有不同的行数,然后告诉您将为NULL值,则可以在代码中使用某些条件进行检查。
如果要一次执行添加,请尝试以下操作:
cursor.execute("SELECT a.xval, a.yval,b.pid,b.pxval,b.pyval from table1 as a, (select t1.id as pid,pxval,pyval from table2 t1 left join (select * from table3 where clubid=?) t2 on t1.id=t2.pid where t1.cityid in (SELECT cityid FROM table1 WHERE domid = ? group by cityid) and t1.pxval>0 and t2.distance is null order by pid) as b WHERE a.DOMID=?",dom_id[i],dom_id[i],dom_id[i])
希望这可以帮助。
关于python - 如何在Python中包含多个cursor.execute,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47196391/