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 5 9165
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
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
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()
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alban Hertroys |
last post by:
Oh no! It's me and transactions again :)
I'm not really sure whether this is a limitation of psycopg or
postgresql. When I use multiple cursors in a transaction, the records
inserted at the...
|
by: bourgon |
last post by:
Working on some new code, I'm coming across WHILE loops used instead of
cursors. I was curious if anyone had any stats on how the speed of
doing this compares to the speed of a cursor. I...
|
by: T.S.Negi |
last post by:
Hi All,
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).
Any suggestion in these regards will be appreciated.
...
|
by: Gustavo Randich |
last post by:
Hello,
I need to know what's wrong in this translation from Informix. Note the
difference in the results, which is due to the UPDATE statement.
ORIGINAL PROGRAM (INFORMIX):...
|
by: Just Me |
last post by:
I have a UserControl that contains a few controls on it.
If when one of the buttons is clicked I want to, in the click event, set the
cursor to
Cursor = Cursors.WaitCursor
I want the cursor...
|
by: a |
last post by:
Hello,
I am doing some multithreading in an MDI app, and I can't seem to get the
cursor to stay as an Hourglass. I call:
Cursor.Current = cursors.wait
at the beginning of my routing, and...
|
by: Just Me |
last post by:
Does Me.Cursor.Current=Cursors.WaitCursor
set the current property of Me.Cursor to Cursors.WaitCursor
And Me.Cursor.Current=Cursors.Default set the Me.Current
property to something (default)...
|
by: mrcraze |
last post by:
Hi Everyone!
We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit...
|
by: Arielle |
last post by:
Good afternoon, here I am again with a new bugger of a problem. I'm working on an update for an existing program that uses classic ASP with a good SQL Server backend. The application server is...
|
by: Dima Kuchin |
last post by:
Hello,
I was trying to find the information about when and where should I use
cursors in DB2, no luck. Maybe you can point me to some article that
describes just that (or tell me which page is...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |