How to include multiple cursor.execute in python

Question

Like I have to take source,ID and destination from sql table but they are present in two different table, so I have tried with

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

Right now I'm using for-loop to extract source and other for-loop to extract ID and destination I tried with

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])))

but it gives error SyntaxError: invalid syntax

Is there any way I can combine the two cursor.execute in a single for-loop. Any suggestion?

Bhushan Pant · Accepted Answer · 2017-11-09 08:59:41Z

1

I think it can be done like this:

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)

This will give required data from both the table.

NOTE: If both tables has different row count then tell will be NULL values you can check for that using some condition in your code.

If you want to add this on a single execute, try this:

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])

Hope this helps.

edited Nov 9, 2017 at 8:59

answered Nov 9, 2017 at 7:53

Bhushan Pant

1,5902 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Domnick Over a year ago

the second sql query is quite big and already include inner queries in it i.e

select t1.id as pid,pxval,pyval  from      <tbl1> t1      left join      (         select *          from <tbl2>          where clubid=?      ) t2          on t1.id=t2.projectid  where      t1.cityid in (             SELECT cityid              FROM <tbl3>              WHERE cbdid =?              group by cityid)      and      t1.pxval>0      and      t2.distance is null  order by projectid

Bhushan Pant Over a year ago

The length of the query doesn't matter here. You just have to adjust your dynamic variables correctly.

Domnick Over a year ago

Here is the second query ` cursor.execute("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]))`

Bhushan Pant Over a year ago

Remove cbid and clubid from above and add dom_id[i] it will get you the results. Also, you can do an exhaustive join to accomodate these two queries into one.

Bhushan Pant Over a year ago

You're Welcome :)

|

Collectives™ on Stack Overflow

How to include multiple cursor.execute in python

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related