pySQLite Insert speed

[Steve Holden]

mdboldin at gmail.com 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/