python db insert

Question

Hello

I am in facing a performance problem in my code.I am making db connection a making a select query and then inserting in a table.Around 500 rows in one select query ids populated .Before inserting i am running select query around 8-9 times first and then inserting then all using cursor.executemany.But it is taking 2 miuntes to insert which is not qood .Any idea

def insert1(id,state,cursor):

   cursor.execute("select * from qwert where asd_id =%s",[id])

   if sometcondition:

        adding.append(rd[i])

cursor.executemany(indata, adding)

where rd[i] is a aray for records making and indata is a insert statement

#prog start here


cursor.execute("select * from assd")



for rows in cursor.fetchall()

if rows[1]=='aq':


  insert1(row[1],row[2],cursor)

if rows[1]=='qw':

  insert2(row[1],row[2],cursor)

Answer 1

I don't really understand why you're doing this.

It seems that you want to insert a subset of rows from "assd" into one table, and another subset into another table?

Why not just do it with two SQL statements, structured like this:

insert into tab1 select * from assd where asd_id = 42 and cond1 = 'set';
insert into tab2 select * from assd where asd_id = 42 and cond2 = 'set';

That'd dramatically reduce your number of roundtrips to the database and your client-server traffic. It'd also be an order of magnitude faster.

Of course, I'd also strongly recommend that you specify your column names in both the insert and select parts of the code.