PgManager
=========

PgManager is higher level database adapter for Python and Postgres. There is also MyManager for MySQL. Both are part of `pgtoolkit <http://hg.devl.cz/pgtoolkit>`_.

PgManager offers following convenience functionality over psycopg:

* **Save and reuse database connection parameters**

  - parameters are linked to uniquely named ConnectionInfo structure

  - using this identifier, you can retrieve Connection or Cursor object at any time
    (new database connection is established when needed)

* **Connection pooling**

  - connections with same identifier are pooled and reused

* **Easy query using the with statement**

  - just use cursor() method in with statement

  - PgManager will get a connection from pool, make cursor and clean up when you're finished

* **Dictionary rows**

  - cursor has additional methods like fetchall_dict(), which
    returns OrderedDict row instead of ordinary list-like row


Basic usage
-----------

::

  from pgtoolkit import pgmanager

  pgm = pgmanager.get_instance()
  pgm.create_conn(hostaddr='127.0.0.1', dbname='postgres', isolation_level='autocommit')

  with pgm.cursor() as curs:
      curs.execute('SELECT now() AS now')
      row = curs.fetchone_dict()
      print(row.now)


Advanced usage
--------------

Study this when you cannot go with basic usage and need to know what actually happens.
Look also into source code, there are many details described in docstrings::

  # create new connection info, this will not connect yet
  pgm.create_conn(name='thatdb', hostaddr='127.0.0.1', dbname='postgres')

  # borrow connection from pool (it will connect to DB here and possibly fail)
  conn = pgm.get_conn('thatdb')

  try:
      # do stuff on that connection
      curs = conn.cursor()
      curs.execute('SELECT now() AS now')
      row = curs.fetchone_dict()
      # don't forget to commit when not in autocommit mode
      curs.connection.commit()
      print(row.now)
  finally:
      # return connection back to pool
      # Beware: name must be correct here, it'll not be checked!
      pgm.put_conn(conn, 'thatdb')

  # close all connections in pool
  pgm.close_conn('thatdb')


Logging
-------

PgManager logs all queries before they are executed, exceptions while executing
queries and also notices.
Queries and exceptions are logged into 'pgmanager_sql' logger,
notices into 'pgmanager_notices'. You need to setup logging for
these loggers to send messages into file or elsewhere.

Notices are logged only from cursor() method.
If you are using get_conn() instead, you must call log_notices() directly.


Listen - Notify
---------------

PostgreSQL supports asynchronous messages from server to clients.
This adds little more complexity to client programs but saves a lot of polling.

You should not use connection info created by create_conn() for LISTEN queries.
It will work in basic case, but it will broke when you add some sort of parallelism.

Use create_conn_listen instead. It allows to copy connection info parameters directly from another connection info or set up new one::

  # create connection info without pool and with initial query 'LISTEN CHAT;'
  pgm.create_conn_listen(name='listenconn', channel='CHAT', copy_dsn='thatdb')

  while not done:
      notify = pgm.wait_for_notify('listenconn', timeout=5.0)
      if notify:
          print('NOTIFY:', notify)
      else:
          print('timeout')

  pgm.close_conn('listenconn')


Timeouts
--------

In production, it's very important to set reasonable timeouts for both connection
and query execution. Application should not freeze when database server gets out of reach.

Both timeouts can be set using :meth:`create_conn`::

  pgm.create_conn(host='127.0.0.1', dbname='test',
                  init_statement='SET statement_timeout TO 20000;',  # in ms, so it's 20s
                  connect_timeout=20,                                # also 20s
                  isolation_level='autocommit')


Methods
-------

.. autoclass:: pgtoolkit.pgmanager.PgManager
   :members:
   :undoc-members: