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)

Your question is a bit unclear -- are you saying that if query3 fails, you want query1 and query2 to be rolled back? — John Gordon
– John Gordon, Commented Aug 9, 2020 at 7:30
@WILLIAM . . . It is possible that the stored procedures themselves commit the changes to the database so this could be tricky. — Gordon Linoff
– Gordon Linoff, Commented Aug 9, 2020 at 11:52
It would probably make more sense to combine the three queries into one stored procedure. — John Gordon
– John Gordon, Commented Aug 9, 2020 at 23:04

juppys · Accepted Answer · 2020-08-10 18:45:14Z

1

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.

answered Aug 10, 2020 at 18:45

juppys

1505 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CaptainDaVinci · Accepted Answer · 2020-08-09 07:29:52Z

1

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

mydb.autocommit = False

answered Aug 9, 2020 at 7:29

CaptainDaVinci

1,0659 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related