Run a sql query multiple times concurrently in python

Question

I am trying to execute a sql statement multiple times concurrently using multiprocessing library. Here is the script I am using,

import time
from multiprocessing import Pool
import psycopg2

rec = []
def make_query(connection):
    cursor = connection.cursor()
    print("conn: %s curs:%s pid=%s" % (id(connection), id(cursor), os.getpid()))
    postgreSQL_select_Query = "select * from users"
    cursor.execute(postgreSQL_select_Query)
    records = cursor.fetchall() 

    for row in records:
        rec.append(row[0])

if __name__ == "__main__":
    connection = psycopg2.connect(user="postgres", password="password", host="127.0.0.1", port="5432", database="abc")
    #cursor = connection.cursor()
    pool = Pool()
    start_time = time.time()
    for _ in range(1000):
        pool.apply_async(make_query, (connection,))
    pool.close()
    pool.join()
    print(rec)
    print("--- %s seconds ---" % (time.time() - start_time))

and this is the response I am getting,

[]
--- 0.48270273208618164 seconds ---

am I doing anything wrong here, because I am not able to access any records. But users table has thousands of records.

Tordek · Accepted Answer · 2020-10-31 07:42:48Z

1

You're using multiprocessing. When you spawn a different process, it won't share data back to its parent, so when you do rec.append(row[0]), you're appending that value into the rec list in that particular process. Then once the process finishes evaluating, its memory is discarded.

If you want to share state between processes, there are several primitives available, but the best way is to return data from each process and combine it within the outer function.

result_tasks = []
for _ in range(1000):
    result_tasks.append(pool.apply_async(make_query, (connection,)))
result = [res.get() for res in result_tasks]

answered Oct 31, 2020 at 7:42

Tordek

10.9k4 gold badges40 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

Run a sql query multiple times concurrently in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related