Run multiple queries at the same time on Mysql using threading in python

user_10787835 0
Viewed 953

I have multiple queries that I need to execute on MySQL DB using python. If I have 3 queries running 5 mins each then it takes approx total 15 mins to execute all queries

Is it possible to execute/fire all 3 queries at the same time using threading so the executing time is reduced to approx 5 mins?

when I try multi-threading or multi-processing iI get the error as

OperationalError: 2013 (HY000): Lost connection to MySQL server during query

def connect(host,username,passwor,db_name):
    mysql_db = mysql.connector.connect(
          host = host,
          user = username,
          passwd = password,
          database = db_name
            )
    cursor = mysql_db.cursor()
    return(cursor)
        
def execute_query(cursor,query):
    cursor.execute(query)
    res = cursor.fetchall()
    que.put(res)
    
query_1 = "select count(*) from db.table1"
query_2 = "select count(*) from db.table2"
query_3 = "select count(*) from db.table3"

cursor = connect((host,username,passwor,db_name):
threads = []
que = Queue.Queue()

threads.append(Thread(target = execute_query, args = (cursor,query1,)))
threads.append(Thread(target = execute_query, args = (cursor,query2,)))
threads.append(Thread(target = execute_query, args = (cursor,query3,)))

for thread in threads:
    thread.start()
    
for thread in threads:
    threads.join()
    res = que.get()
python-3.x multithreading multiprocessing mysql
1 Answers

ou can tweak your "connect" function like:

def connect(host,username,passwor,db_name):
    mysql_db = mysql.connector.connect(
          host = host,
          user = username,
          passwd = password,
          database = db_name
            )
    cursor = mysql_db.cursor()
   #####
    cursor.execute("SET SESSION MAX_EXECUTION_TIME=50000")
   #####
    return(cursor)

Just after cursor = mysql_db.cursor()

user_159089780
Related