How to use python to query database in parallel

Question

I have two functions which I use to query database. Assuming two separate queries, how to run these in parallel to query same database, and also wait for both results to return before continuing the execution of the rest of the code?

def query1(param1, param2):
    result = None
    logging.info("Connecting to database...")
    try:
        conn = connect(host=host, port=port, database=db)
        curs = conn.cursor()
        curs.execute(query)
        result = curs
        curs.close()
        conn.close()
    except Exception as e:
        logging.error("Unable to access database %s" % str(e))
    return result


def query2(param1, param2):
    result = None 
    logging.info("Connecting to database...")
    try:
        conn = connect(host=host, port=port, database=db)
        curs = conn.cursor()
        curs.execute(query)
        result = curs
        curs.close()
        conn.close()  
    except Exception as e:
        logging.error("Unable to access database %s" % str(e))    
    return result

I guess you can take a look to the threading library from the standard collection. Their is some good posts here explaining how to use python threads (and gathering their return values) like stackoverflow.com/questions/6893968/… (it's not really parallelism on python side, but both requests will be made almost simultaneously). — mgc
– mgc, Commented Jun 22, 2016 at 18:55

th3an0maly · Accepted Answer · 2016-06-23 13:23:31Z

13

Here is a multi-threaded code that does what you're trying to accomplish:

from threading import Thread, Lock

class DatabaseWorker(Thread):
    __lock = Lock()

    def __init__(self, db, query, result_queue):
        Thread.__init__(self)
        self.db = db
        self.query = query
        self.result_queue = result_queue

    def run(self):
        result = None
        logging.info("Connecting to database...")
        try:
            conn = connect(host=host, port=port, database=self.db)
            curs = conn.cursor()
            curs.execute(self.query)
            result = curs
            curs.close()
            conn.close()
        except Exception as e:
            logging.error("Unable to access database %s" % str(e))
        self.result_queue.append(result)

delay = 1
result_queue = []
worker1 = DatabaseWorker("db1", "select something from sometable",
        result_queue)
worker2 = DatabaseWorker("db1", "select something from othertable",
        result_queue)
worker1.start()
worker2.start()

# Wait for the job to be done
while len(result_queue) < 2:
    sleep(delay)
job_done = True
worker1.join()
worker2.join()

edited Jun 23, 2016 at 13:23

answered Jun 22, 2016 at 19:01

th3an0maly

3,5188 gold badges38 silver badges54 bronze badges

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

8 Comments

th3an0maly Over a year ago

Oops, sorry. Edited them to be worker1 and worker2. I had written my test using those variables names first, and later changed them to match your code :)

ArchieTiger Over a year ago

Thank you! by the way I got error on result_queue.size<2. that list has no attribute size so I changed while result_queue.size < 2 to len(result_queue)<2. And how do I access individual result from both workers?

ArchieTiger Over a year ago

got it, result_queue

th3an0maly Over a year ago

@user1128088 Good find again. I've corrected it. Thanks. You can assign identifiers to the result while queueing them. e.g. a job_id. Later, you can look it up in the results. If there are too many parallel queries being run at a time, I would suggest making result_queue a dictionary with keys being the job_ids and values being the actual results.

ShadyBears Over a year ago

Can anyone explain what __lock = Lock() is doing? Thank you!

|

Collectives™ on Stack Overflow

How to use python to query database in parallel

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related