Cursors in a Loop

[Carsten Haese]

On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote:
> On Jan 3, 7:47 pm, t_rectenwald <t.rectenw... at gmail.com> wrote:
> > I have a python script that uses the cx_Oracle module.  I have a list
> > of values that I iterate through via a for loop and then insert into
> > the database.  This works okay, but I'm not sure whether I can use one
> > cursor for all inserts, and define it outside of the loop, or
> > instantiate and close the cursor within the loop itself.  For example,
> > I have:
> >
> > for i in hostlist:
> >     cursor = connection.cursor()
> >     sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
> >     cursor.execute(sql)
> >     cursor.close()
> >
> > And I've also tried:
> >
> > cursor = connection.cursor()
> > for i in hostlist:
> >     sql= "insert into as_siebel_hosts_temp values('%s')" % (i)
> >     cursor.execute(sql)
> > cursor.close()
> >
> > Both work fine, and execute in the same amount of time.  I'm just
> > trying to understand what is the "correct" approach to use.

Actually, the correct approach would be "neither." You should NEVER use
string formatting to fill values into an SQL query. (Doing so causes
security vulnerabilities and performance problems. See, for example,
http://informixdb.blogspot.com/2007/07/filling-in-blanks.html for
detailed explanations.) Instead, you should use a parametrized query.

With a parametrized query, your code becomes this:

cursor = connection.cursor()
for i in hostlist:
    cursor.execute("insert into as_siebel_hosts_temp values(?)", (i,) )
cursor.close()

Since this will save the database engine from having to re-parse the
query every time, it will run much faster if the list is long.

Even better would be to use executemany:

cursor = connection.cursor()
cursor.executemany("insert into as_siebel_hosts_temp values(?)",
    [(i,) for i in hostlist] )
cursor.close()

Depending on whether cx_Oracle allows this, the list comprehension in
that example could be replaced by the generator expression
((i,) for i in hostlist), but I don't know if cx_Oracle allows
executemany with an arbitrary iterable.

Hope this helps,

-- 
Carsten Haese
http://informixdb.sourceforge.net