Am I interacting with the database correctly?

H

Hussein B

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.
 
G

Gabriel Genellina

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.
 
G

Gabriel Genellina

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top