python-mysql-connector: I need to speed up the time it takes to update multiple items in mySQL table

I currently have a list of id’s approx. of size 10,000. I need to update all rows in the mySQL table which have an id in the inactive_ids list that you see below. I need to change their active status to ‘No’ which is a column in the mySQL table.

I am using mysql.connector python library.

When I run the code below, it is taking about 0.7 seconds to execute each iteration in the for loop. Thats about a 2 hour run time for all 10,000 id’s to be changed. Is there a more optimal/quicker way to do this?

# inactive_ids are unique strings something like shown below
# inactive_ids = ['a9okeoko', 'sdfhreaa', 'xsdfasy', ..., 'asdfad']

# initialize connection
mydb = mysql.connector.connect(
    user="REMOVED", 
    password="REMOVED", 
    host="REMOVED", 
    database="REMOVED"
)

# initialize cursor
mycursor = mydb.cursor(buffered=True)

# Function to execute multiple lines
def alter(state, msg, count):
    result = mycursor.execute(state, multi=True)
    result.send(None)
    print(str(count), ': ', msg, result)
    count += 1
    return count

# Try to execute, throw exception if fails
try:
    count = 0
    for Id in inactive_ids:
        
        # SAVE THE QUERY AS STRING
        sql_update = "UPDATE test_table SET Active = 'No' WHERE NoticeId = '" + Id + "'"
    
        # ALTER
        count = alter(sql_update, "done", count)

    # commits all changes to the database
    mydb.commit()
except Exception as e:
    mydb.rollback()
    raise e

Answer

Do it with a single query that uses IN (...) instead of multiple queries.

placeholders = ','.join(['%s'] * len(inactive_ids))
sql_update = f"""
    UPDATE test_table 
    SET Active = 'No' 
    WHERE NoticeId IN ({placeholders})
    """
mycursor.execute(sql_update, inactive_ids)

Advertisement

Answer