On Jan 4, 5:11 am, Carsten Haese <[EMAIL PROTECTED]> wrote: > On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote: > > On Jan 3, 7:47 pm, t_rectenwald <[EMAIL PROTECTED]> 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() -- http://mail.python.org/mailman/listinfo/python-list