How to handle and execute multiple SQL at once in Python?

Question

I am trying to execute multiple query in a for-loop. So in each loop, if either query failed to execute, all queries in that loop will not commit to database and return the error message for failed query. Below is my code. Seems even there is one query failed to run, the others queries will still execute and commit the data to database. How can I change it to achieve what I want? Thanks
Code:
import mysql.connector 
mydb = mysql.connector.connect() 
cursor = mydb.cursor()
for i in country_list:
    try:
        query1 = "call_sp_insert_sth_1" //insert data to DB
        query2 = "call_sp_insert_sth_2" //insert data to DB
        query3 = "call_sp_insert_sth_3" //update data to DB
        cursor.execute(query1)
        cursor.execute(query2)
        cursor.execute(query3)
        mydb.commit()
    except Exceptiion as err:
        fail_list.append('error':err.msg)
        continue

mysql.connector.close_connection(mydb)

juppys · Accepted Answer

This is how I would write your code.
import mysql.connector 
mydb = mysql.connector.connect() 
cursor = mydb.cursor()
query1 = "call_sp_insert_sth_1" //insert data to DB
query2 = "call_sp_insert_sth_2" //insert data to DB
query3 = "call_sp_insert_sth_3" //update data to DB

for i in country_list:
  try:
    with mydb.cursor() as curs:
      cursor.execute(query1)
      cursor.execute(query2)
      cursor.execute(query3)
      curs.close()
  except Exception as err:
    fail_list.append('error':err.msg)
  else:
    mydb.commit()

I tend to have my queries as variables outside the for loop with parameters, then populate the parameters in the loop.
myquery = '''select * from {}'''
for table in tables:
  curs.execute(myquery.format(table))

The 'else' part only kicks off if there were no errors in the 'try' part.

CaptainDaVinci · Answer

You need to set the mydb connection to not auto-commit.
mydb.autocommit = False

How to handle and execute multiple SQL at once in Python?

2 Answers

Add your own answers!

Ask a Question