471,349 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,349 software developers and data experts.

Cursors in a Loop

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.

Thanks,
Tom

Jan 4 '08 #1
5 8548
On Jan 3, 7:47*pm, t_rectenwald <t.rectenw...@gmail.comwrote:
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.

Thanks,
Tom
I think I have this one figured out. The answer would be the second
option, i.e. keep the cursor instantion and close outside of the
loop. I wasn't aware that one cursor could be used for multiple
executes.

Regards,
Tom
Jan 4 '08 #2
On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote:
On Jan 3, 7:47 pm, t_rectenwald <t.rectenw...@gmail.comwrote:
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/...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
Jan 4 '08 #3
On Jan 4, 5:11 am, Carsten Haese <cars...@uniqsys.comwrote:
On Thu, 2008-01-03 at 17:25 -0800, t_rectenwald wrote:
On Jan 3, 7:47 pm, t_rectenwald <t.rectenw...@gmail.comwrote:
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()
Jan 4 '08 #4
On Fri, 2008-01-04 at 00:03 -0800, Chris wrote:
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()
Huh? In the OP's example, the table one has one column. I'll openly
admit that I don't know anything about Oracle, but that code doesn't
make sense to me. Maybe you're trying to execute a multi-row insert, but
that would be done with execute(), not executemany(), wouldn't it?

Also, isn't "[(i,)[0] for i in hostlist]" exactly the same as "[i for i
in hostlist]" which in turn is exactly the same as "hostlist"?

--
Carsten Haese
http://informixdb.sourceforge.net
Jan 4 '08 #5
On Jan 4, 4:32 pm, Carsten Haese <cars...@uniqsys.comwrote:
On Fri, 2008-01-04 at 00:03 -0800, Chris wrote:
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()

Huh? In the OP's example, the table one has one column. I'll openly
admit that I don't know anything about Oracle, but that code doesn't
make sense to me. Maybe you're trying to execute a multi-row insert, but
that would be done with execute(), not executemany(), wouldn't it?

Also, isn't "[(i,)[0] for i in hostlist]" exactly the same as "[i for i
in hostlist]" which in turn is exactly the same as "hostlist"?

--
Carsten Haesehttp://informixdb.sourceforge.net
The OPs example has a formatted string, no idea what is in it...
My example creates a tuple out of each of the records you want to
insert and uses them in the bind variables.

You can do a loop through hostlist and do a single execute on each one
if you want. It won't make a large impact.
The [(i,)[0] for i in hostlist] was mainly directed to you because
your structure ends up being a tuple inside a list which doesn't work
for cx_Oracle. You need a straight tuple to bind to the statement.

My code creates a series of usable tuples for the executemany
function.
HTH,
Chris
Jan 5 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Alban Hertroys | last post: by
22 posts views Thread by T.S.Negi | last post: by
5 posts views Thread by Gustavo Randich | last post: by
5 posts views Thread by Just Me | last post: by
6 posts views Thread by a | last post: by
1 post views Thread by mrcraze | last post: by
1 post views Thread by Dima Kuchin | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.