[EMAIL PROTECTED] wrote: > Steve, I want to make sure I understand. My test code is below, where > ph serves as a placeholder. I am preparing for a case where the number > of ? will be driven by the length of the insert record (dx) > > dtable= 'DTABLE3' > print 'Insert data into table %s, version #3' % dtable > ph= '?, ?, ?, ?' > sqlx= 'INSERT INTO %s VALUES ( %s ) ' % (dtable,ph) > t0a=time.time() > for dx in d1: > curs1.execute(sqlx,dx) > print (time.time()-t0a) > print curs1.lastrowid > conn1.commit() > > I think you are saying that sqlx is re-evaluated in each loop, i.e. > not the same as pure hard coding of > sqlx= 'INSERT INTO DTABLE3 VALUES ( ?, ?, ?, ? ) ' > Is that right?
Yes. If the sql is constant then you would be performing an unnecessary computation inside the loop. Not a biggie, but it all takes time. Is the loop above your original code? If so I was wrong about the loop. > Hence (if I understand python convention), this can be > solved by adding > sqlx= copy.copy(sqlx) > before the looping. And in tests adding this step saved about 5-10% in > time. > Now this I don;t really understand at all. What's the point of trying to replace sqlx with a copy of itself? Perhaps if you explained what you hope this will achieve I could comment more intelligently. > And yes, I can see why (B) is always better from a security > standpoint. The python solutions for problems such as there are a > great help for people like me, in the sense that the most secure way > does not have a speed penalty (and in this case is 3-4x faster). Yes, it's a real win-win. Since both the table and the number of arguments appear to be variable one possible solution is to build a dict that would allow you to look up the right SQL using the table name. So, suppose you have the following tables and number of arguments: tables = (("table1", 3), ("table2", 5), ("table3", 2) ) you could create a suitable dict as (untested): tdict = {} for tbl, ct in tables: tdict[tbl] = "INSERT INTO %s VALUES (%s)" % \ (tbl, ", ".join(["?"] * ct)) Then you can use the table to look up the right SQL, quite a fast operation compared with actually building it. regards Steve -- Steve Holden +1 571 484 6266 +1 800 494 3119 Holden Web LLC http://www.holdenweb.com/ -- http://mail.python.org/mailman/listinfo/python-list