Cursors in a Loop

[Chris]

On Jan 4, 5:11 am, Carsten Haese <cars... at uniqsys.com> wrote:
> 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.
>

> 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.

You should bind all variables to save the pool.

cursor = connection.cursor()
cursor.executemany("""insert into as_siebel_hosts_temp
                      values (:whole, :lot, :of, :bind, :variables)
                   """
                  ,[(i,)[0] for i in hostlist]
                  )
connection.commit()
connection.close()