469,323 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

MySQLdb and Cursor

Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe
Jul 18 '05 #1
3 8412
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe


best way is to create a connection ar the start of one session work. use
this connection to do an entire transaction. At the end of work you must
commit or rollback. But this don't mean that you can misc select and
insert on tha same cursor.

con = MySql.connection....
cur1 = con.cursor()
cur1.execute("select.....
for rec in cur1:
do something
cur2 = con.cursor()
cur2.execute("insert ....

conn.commit or rollback
conn.close()

In this stupid example you CANNOT use only cur1 !!
Is not necessary to close cursors , last line do this for you
Bye
Glauco
Jul 18 '05 #2
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests (SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".


You can use the same cursor object.

-- Gerhard

Jul 18 '05 #3
Glauco wrote:
Michel Combe wrote:
Hi all,

I'm writing a program that will read an ASCII file periodically and
update
several tables in a MySQL database.
My question is "Can I use the same cursor for several SQL requests
(SELECT
and INSERT) or do I have to close the cursor between 2 requests ?".

Regards
Michel Combe


best way is to create a connection ar the start of one session work. use
this connection to do an entire transaction. At the end of work you must
commit or rollback. But this don't mean that you can misc select and
insert on tha same cursor.

con = MySql.connection....
cur1 = con.cursor()
cur1.execute("select.....
for rec in cur1:
do something
cur2 = con.cursor()
cur2.execute("insert ....

conn.commit or rollback
conn.close()

In this stupid example you CANNOT use only cur1 !!
Is not necessary to close cursors , last line do this for you
Bye
Glauco


You don't need to close the connection. By issuing a commit or a
rollback you are finishing the transaction (if you have transaction
aware tables, e.g. InnoDB). The point that Glauco is making is that you
can't use one cursor for two operations simultaneously.

Following his example, if you try and do;
cur1.execute("SELECT a, b, c FROM table1")
for row in cur1.fetchone():
# do something
cur1.execute("INSERT INTO table2 VALUES (?, ?, ?)" % (row.a, row.b, row.c))

Then you will get an exception because the second execute will
obliterate the result set of the first - which you are trying to loop
through. Its perfectly possibly though, to do;
cur1.execute("SELECT a, b, c FROM table1")
results = cur1.fetchall()
for row in results:
# do something
cur1.execute("INSERT INTO table2 VALUES (?, ?, ?)" % (row.a,

row.b, row.c))

Which is fine as long as the first select doesn't return too many rows ;-)

As a general rule of thumb establish a connection when your program
starts and close it when you finish. Oh, and don't create a new cursor
within a loop, that will not be very efficient.

Regards,
Andy
--
--------------------------------------------------------------------------------
From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Jul 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Chris Stromberger | last post: by
7 posts views Thread by Brian Kelley | last post: by
21 posts views Thread by John Fabiani | last post: by
reply views Thread by Wesley Kincaid | last post: by
1 post views Thread by Hector Villafuerte | last post: by
2 posts views Thread by ws Wang | last post: by
11 posts views Thread by Fred | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.