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]

Re: MySQLdb and Cursor

Michel Combe wrote:
[color=blue]
> 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
>
>[/color]

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.connectio n....
cur1 = con.cursor()
cur1.execute("s elect.....
for rec in cur1:
do something
cur2 = con.cursor()
cur2.execute("i nsert ....

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]

Re: MySQLdb and Cursor

Michel Combe wrote:[color=blue]
> 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 ?".[/color]

You can use the same cursor object.

-- Gerhard

[Andy Todd]

Re: MySQLdb and Cursor

Glauco wrote:
[color=blue]
> Michel Combe wrote:
>[color=green]
>> 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
>>
>>[/color]
>
> 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.connectio n....
> cur1 = con.cursor()
> cur1.execute("s elect.....
> for rec in cur1:
> do something
> cur2 = con.cursor()
> cur2.execute("i nsert ....
>
> 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
>
>[/color]

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;
[color=blue][color=green][color=darkred]
>>> cur1.execute("S ELECT a, b, c FROM table1")
>>> for row in cur1.fetchone() :
>>> # do something
>>> cur1.execute("I NSERT INTO table2 VALUES (?, ?, ?)" % (row.a,[/color][/color][/color]
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;
[color=blue][color=green][color=darkred]
>>> cur1.execute("S ELECT a, b, c FROM table1")
>>> results = cur1.fetchall()
>>> for row in results:
>>> # do something
>>> cur1.execute("I NSERT INTO table2 VALUES (?, ?, ?)" % (row.a,[/color][/color][/color]
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/