#! /usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import psycopg2.extensions
import psycopg2.pool

DSN = ""

class SQLRequest(object):
  def __init__(self, operation, params=(), name=None):
    self._operation = operation
    self._params = params
    d = dict((param, '$%d' % (i,)) for i, param in enumerate(params, 1))
    if name is None:
      name = 'req_%x' % id(self)
    self._prepare_req = 'PREPARE %s AS %s' % (name, (operation % d).replace('%', '%%'))
    self._execute_req = 'EXECUTE %s (%s)' % (name, ', '.join('%%(%s)s' % param for param in params))

  def prepare(self, curs):
    curs.execute(self._prepare_req)

  def __call__(self, conn, *args, **kwargs):
    kwargs.update(zip(self._params, args))
    curs = conn.cursor()
    try:
      curs.execute(self._execute_req, kwargs)
      for row in curs:
        yield row
    finally:
      curs.close()

class PreparedConnectionFactory(object):
  def __init__(self):
    self._requests = []

  def add_request(self, req):
    self._requests.append(req)

  def __call__(self, *args, **kwargs):
    conn = psycopg2.extensions.connection(*args, **kwargs)
    curs = conn.cursor()
    for req in self._requests:
      req.prepare(curs)
    curs.close()
    conn.commit()
    return conn

Connection = PreparedConnectionFactory()

req = SQLRequest("SELECT * FROM my_table WHERE i = %(i)s AND j = %(j)s", ("i", "j"))
Connection.add_request(req)

pool = psycopg2.pool.ThreadedConnectionPool(10, 100, DSN, connection_factory=Connection)

def main():
  conn = pool.getconn()
  for row in req(conn, 1, 5):
    print repr(row)
  conn.commit()
  pool.putconn(conn)

if __name__ == "__main__":
  main()