python db insert

Question

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)

Can you post some example code? It's hard to suggest improvements without it. — Jason Coon
– Jason Coon, Commented Aug 13, 2009 at 11:51
We can't even know what database engine you're using, what module you're using, or identify where the bottleneck might remotely be. Plus my eyes are bleeding from the formatting of your post. — Jeremy Visser
– Jeremy Visser, Commented Aug 13, 2009 at 12:42
Maybe you can also tag this question with 'sqlalchemy' (which I assume it's what you are using) — dalloliogm
– dalloliogm, Commented Aug 13, 2009 at 14:24

Michael van der Westhuizen · Accepted Answer · 2009-08-13 12:59:47Z

3

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.

answered Aug 13, 2009 at 12:59

Michael van der Westhuizen

7847 silver badges12 bronze badges

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

4 Comments

ha22109 Over a year ago

i have to check some conditions before inserting and in my select query i ma fectching data from 3 tables with some condition. I m using psycopg2 for db connection and using postgressql database

Michael van der Westhuizen Over a year ago

The answer still stands - for best performance you should try to move your logic to your SQL. You could use unions to join the result sets of the three source tables (given that the columns are the same types). The idea is that, say, six queries to insert 60k rows will always be faster than 60006 queries with their associated round trips.

ha22109 Over a year ago

i m using regex in my logic and i think regex cannot be used in db scripts.Since all my things are in python so i want some uniformity.

Michael van der Westhuizen Over a year ago

As requested by others, what database are you using? Regex may be supported by your database.

Collectives™ on Stack Overflow

python db insert

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related