Python multithreading in a loop for executing query sql

Question

I search to transform my program in multithreading, but I don't say how to do. Indeed, I thought of 2 solutions, but i think it is not the more optimal : Do a select and stock the result in an array in python and filter after by range for the threads. Or multithread with a clause id for the select to have only one Domain by thread Is there a better way to make this directly in the loop?

#!/usr/bin/python
import mysql.connector as mariadb
from subprocess import Popen, PIPE, STDOUT
import re
import os

mariadb_connection = mariadb.connect(user='root', password='xxxx', database='xxxx');
cursor = mariadb_connection.cursor(buffered=True)
#retrieving information

cursor.execute("SELECT Domain,Id FROM classement where Domain like '%com';")

for Domain,Id in cursor:
          counter=0
          for character in Domain:
                if (character == "."):
                        counter = counter + 1
                        if (counter==1):
                            print(Domain)
                            pingresult = Popen(['ping','-c','3', Domain], stdout=PIPE, stderr=STDOUT).communicate()[0]
                            pingresult=str(pingresult)
                            ping = pingresult.find('min/avg/max/mdev')
                            ping=pingresult[ping+19:-6]
                            print(ping)
                            cursor.execute('update classement set Ping = "%s" where Domain = "%s";' % (ping,Domain))
                            mariadb_connection.commit()
mariadb_connection.close()

Stanislav Ivanov · Accepted Answer · 2017-11-03 10:18:56Z

1

I think simplest way is a wrap your loop in function and use multiprocessing.dummy thread pool:

from multiprocessing.dummy import Pool
...
cursor.execute( ... )
...

def process (entry):
    Domain,Id = entry
    ...
    # your code there
    ...
    cursor.execute( ... )
    mariadb_connection.commit()

pool = Pool(os.cpu_count())
# or may be N*os.cpu_count() if ping waits for
# network interaction too long

pool.map(process, cursor)
pool.join()

May be additional locking required for multithreaded access to database.

edited Nov 3, 2017 at 10:18

answered Nov 2, 2017 at 17:46

Stanislav Ivanov

2,0121 gold badge18 silver badges23 bronze badges

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

2 Comments

Alexandre Lemaitre Over a year ago

Thanks for your answer, but I don't understand because if I parallelize my cursor, the query executed is the same but by multiple threads so I don't increase the speed no ?

Stanislav Ivanov Over a year ago

Main question is there bottleneck of your program? If it is in popen - yes, multiple ping in different threads will increase speed of execution. If bottleneck in inserting results into database, firstly do not execute commit after each query. Try to do commit after multiple insertion operation.

Collectives™ on Stack Overflow

Python multithreading in a loop for executing query sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related