Accessing a database from a worker process

Question

I'm trying to put jobs that need access to a database using Python, Redis and PostgreSQL. I'm doing the following:

Put jobs in a Redis queue using RQ:

def queue_data(self, json_data):
    Queue().enqueue(process_data, json.dumps(json_data))

In process_data, perform queries on a PostgreSQL database using psycopg2:

def process_data(json_data):
    with psycopg2.connect("dbname=pgtest2db user=pgtest2user") as conn:
        with conn.cursor() as cursor:
            # I'm not actually doing this query, but you get the idea
            cursor.execute("SELECT * FROM some_table")
    conn.close()

Obviously this is far from optimal, since each call to process_data is creating a new connection. What is the best practice to achieve this? Who should be responsible for managing a DB connection pool?

Note that I'm forcing myself not to use an ORM, since I'm doing this to teach myself and I'd like to understand these patterns from a more pure perspective.

Edit

I ended up using a custom worker like this one:

import os
import node

import redis
from rq import Worker, Queue, Connection
from psycopg2.pool      import ThreadedConnectionPool

listen = ['high', 'default', 'low']
redis_url = os.getenv('REDISTOGO_URL', 'redis://localhost:6379')
redis_conn = redis.from_url(redis_url)
pool = ThreadedConnectionPool(minconn = 1, maxconn = 10, dsn = "dbname=pgtest2db user=pgtest2user")

def process_data(json_data):
    dbconn = pool.getconn()
    result = perform_some_db_actions(dbconn, json_data)
    pool.putconn(dbconn)
    return result

if __name__ == '__main__':
    with Connection(redis_conn):
        print "Setting up Redis"
        worker = Worker(map(Queue, listen))
        worker.work()

Doesn't `psychopg2.pool` contain a connection pool implementation? — Hans Then, May 08 '14 at 17:39
I definitely need a pool, but who manages it? I can't pass the cursor or the connection through the queue, since those values cannot be "pickled" i.e. represented in a serialised way. — jlhonora, May 08 '14 at 19:20
The point of a pool is that you do not need to pickle it. You can simply ask for a "new" one and you will be given an existing one. However, it appears you do not just want to reuse the connections, but also the cursor state. Am I correct? — Hans Then, May 08 '14 at 20:30
@jjanes sorry I missed that, I'm using [RQ](http://python-rq.org/docs/workers/) — jlhonora, May 08 '14 at 20:56
I think that what you need is a singleton object. That way you can just access the same instance from each one of the workers then get the pool from there. — Josué Padilla, Oct 10 '17 at 20:48

score 0 · Answer 1 · answered Sep 04 '20 at 17:22

I understand that it is too late, but everyone who is wondering how to use psycopg2 among with RQ (especially when you're getting SSL error: decryption failed or bad record mac - just open connection inside worker (i.e task. It is better to create singleton instead of opening connection every time). Not before worker.work() function.

Accessing a database from a worker process

Edit

1 Answers1