Am I interacting with the database correctly?

Am I interacting with the database correctly?

Post by Hussein » Mon, 19 Jan 2009 20:59:51


Hey,
I'm new with database interactions in Python and I'm not sure if I'm
handling the cursor and transactions correctly:
++++++++
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(flate_rate_pkgs_sql)
rows = cursor.fetchall()
#I have for loop here to iterate over rows
cursor.execute()
rows = cursor.fetchall()
# some more cursor.execute() calls but only SQL select statements
# here is another for loop that contains try block
# here are cursor.execute() calls, both insert and update
db.commit()
# in the except code block, I use db.rollback()
++++++++
As you see, my script contains only one db object and one cursor
object and both the db and cursor objects are used multiple times, it
is ok?
As you might figured, this is a script for reports :)
Thanks.
 
 
 

Am I interacting with the database correctly?

Post by John Fabia » Tue, 20 Jan 2009 00:54:06


I have never worked with MySQL. I do work with others. The first part
looks fine. If you insert, update or delete then you need a 'commit' or
a 'rollback'. Preparing data for a report it is unlikely that you need to
commit or rollback anything. After all you are only using 'select'.

John

 
 
 

Am I interacting with the database correctly?

Post by Gabriel Ge » Tue, 20 Jan 2009 02:09:01

En Sun, 18 Jan 2009 13:54:06 -0200, John Fabiani < XXXX@XXXXX.COM >
escribi
>> I have never worked with MySQL. I do work with others. The first part >> looks fine. If you insert, update or delete then you need a 'commit' or >> a 'rollback'. Preparing data for a report it is unlikely that you need >> to >> commit or rollback anything. After all you are only using 'select'.

Note that you have to commit/rollback a transaction *even* if you only
execute select statements - at least when using isolation levels higher
than "read uncommited".
By example, using "repeatable reads", a "select" on table A blocks any
attempt to modify the involved rows until the transaction ends. And using
"read commited", it won't see rows modified or added after the transaction
began.

--
Gabriel Genellina
 
 
 

Am I interacting with the database correctly?

Post by Gabriel Ge » Tue, 20 Jan 2009 02:09:01

En Sun, 18 Jan 2009 13:54:06 -0200, John Fabiani < XXXX@XXXXX.COM >
escribi
>> I have never worked with MySQL. I do work with others. The first part >> looks fine. If you insert, update or delete then you need a 'commit' or >> a 'rollback'. Preparing data for a report it is unlikely that you need >> to >> commit or rollback anything. After all you are only using 'select'.

Note that you have to commit/rollback a transaction *even* if you only
execute select statements - at least when using isolation levels higher
than "read uncommited".
By example, using "repeatable reads", a "select" on table A blocks any
attempt to modify the involved rows until the transaction ends. And using
"read commited", it won't see rows modified or added after the transaction
began.

--
Gabriel Genellina