Chris
1/4/2008 8:04:00 AM
On Jan 4, 5:11 am, Carsten Haese <cars...@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...@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()