Python / MySQL - this use of cursors works but is it correct?-CodePudding

I have the code below which selects some data from one table, finds related data in another table, updates the data in the related table and deletes the data from first table. The use of cursors below works but not sure it is best. Do I need to define a new cursor(x) = db.cursor() each time like this?

db = MySQLdb.connect(host=cred.host, user=cred.user, password=cred.password, 
db=cred.db, port=cred.port)
cursor = db.cursor()  
cursor.execute("SELECT * FROM tbl_sqs order by timeOfOfferChange ASC limit 200")  

for reprice in  cursor.fetchall():

  #do initial processing of data retreived from tbl_sqs
  #select the current value(s) from tbl_inventory_data  that are for the same product from the same seller   cursor2 = db.cursor()  # prepare a cursor object using cursor() method

  cursor2 = db.cursor()
  cursor2.execute("SELECT * FROM tbl_inventory_data WHERE `asin`=%s and `user`=%s", (ASIN, SellerId))
  db.commit()

     for row in cursor2.fetchall():  #iterate over inventory items 

       cursor3 = db.cursor()  # prepare a cursor object using cursor() method#
       cursor3.execute("UPDATE tbl_inventory_data SET `…..WHERE `seller-sku`=%s AND `user`=%s"))
       db.commit()

     cursor4 = db.cursor() 
     cursor4.execute("DELETE FROM tbl_sqs WHERE MessageId=%s", (message_id)) # delete the message just processed. 
     db.commit()

CodePudding user response：

You don't need to create a cursor for the database on every query. It's best that you create a cursor one time and use the same cursor until the end of using the database. Each cursor creation has an overhead on the database and in the big or busy databases might cause some problems.

Also, after done using the database closing the cursor is good:

cursor.close()