MySQLdb and Cursor

[Michel Combe]

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

[Glauco]

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

[Gerhard Häring]

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

[Andy Todd]

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/