MySQLdb and Cursor

[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/