title: Accessing a database from a worker process
body: <p>I'm trying to put jobs that need access to a database using Python, Redis and PostgreSQL. I'm doing the following:</p>
  
  <ol>
  <li><p>Put jobs in a Redis queue using <a href="http://python-rq.org/docs/workers/" rel="noreferrer">RQ</a>:</p>
  
  <pre><code>def queue_data(self, json_data):
      Queue().enqueue(process_data, json.dumps(json_data))
  </code></pre></li>
  <li><p>In <code>process_data</code>, perform queries on a PostgreSQL database using psycopg2:</p>
  
  <pre><code>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()
  </code></pre></li>
  </ol>
  
  <p>Obviously this is far from optimal, since each call to <code>process_data</code> is creating a new connection. What is the best practice to achieve this? Who should be responsible for managing a DB connection pool?</p>
  
  <p>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.</p>
  
  <h1>Edit</h1>
  
  <p>I ended up using a custom worker like this one:</p>
  
  <pre><code>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()
  </code></pre>