1 PgManager

     2 =========

     3 

     4 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>`_.

     5 

     6 PgManager offers following convenience functionality over psycopg:

     7 

     8 * **Save and reuse database connection parameters**

     9 

    10   - parameters are linked to uniquely named ConnectionInfo structure

    11 

    12   - using this identifier, you can retrieve Connection or Cursor object at any time

    13     (new database connection is established when needed)

    14 

    15 * **Connection pooling**

    16 

    17   - connections with same identifier are pooled and reused

    18 

    19 * **Easy query using the with statement**

    20 

    21   - just use cursor() method in with statement

    22 

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

    24 

    25 * **Dictionary rows**

    26 

    27   - cursor has additional methods like fetchall_dict(), which

    28     returns OrderedDict row instead of ordinary list-like row

    29 

    30 

    31 Basic usage

    32 -----------

    33 

    34 ::

    35 

    36   import pgmanager

    37 

    38   pgm = pgmanager.get_instance()

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

    40 

    41   with pgm.cursor() as curs:

    42       curs.execute('SELECT now() AS now')

    43       row = curs.fetchone_dict()

    44       print(row.now)

    45 

    46 

    47 Advanced usage

    48 --------------

    49 

    50 Study this when you cannot go with basic usage and need to know what actually happens.

    51 Look also into source code, there are many details described in docstrings::

    52 

    53   # create new connection info, this will not connect yet

    54   pgm.create_conn(name='thatdb', hostaddr='127.0.0.1', dbname='postgres')

    55 

    56   # borrow connection from pool (it will connect to DB here and possibly fail)

    57   conn = pgm.get_conn('thatdb')

    58 

    59   try:

    60       # do stuff on that connection

    61       curs = conn.cursor()

    62       curs.execute('SELECT now() AS now')

    63       row = curs.fetchone_dict()

    64       # don't forget to commit when not in autocommit mode

    65       curs.connection.commit()

    66       print(row.now)

    67   finally:

    68       # return connection back to pool

    69       # Beware: name must be correct here, it'll not be checked!

    70       pgm.put_conn(conn, 'thatdb')

    71 

    72   # close all connections in pool

    73   pgm.close_conn('thatdb')

    74 

    75 

    76 Logging

    77 -------

    78 

    79 PgManager logs all queries before they are executed, exceptions while executing

    80 queries and also notices.

    81 Queries and exceptions are logged into 'pgmanager_sql' logger,

    82 notices into 'pgmanager_notices'. You need to setup logging for

    83 these loggers to send messages into file or elsewhere.

    84 

    85 Notices are logged only from cursor() method.

    86 If you are using get_conn() instead, you must call log_notices() directly.

    87 

    88 

    89 Listen - Notify

    90 ---------------

    91 

    92 PostgreSQL supports asynchronous messages from server to clients.

    93 This adds little more complexity to client programs but saves a lot of polling.

    94 

    95 You should not use connection info created by create_conn() for LISTEN queries.

    96 It will work in basic case, but it will broke when you add some sort of parallelism.

    97 

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

    99 

   100   # create connection info without pool and with initial query 'LISTEN CHAT;'

   101   pgm.create_conn_listen(name='listenconn', channel='CHAT', copy_dsn='thatdb')

   102 

   103   while not done:

   104       notify = pgm.wait_for_notify('listenconn', timeout=5.0)

   105       if notify:

   106           print('NOTIFY:', notify)

   107       else:

   108           print('timeout')

   109 

   110   pgm.close_conn('listenconn')

   111 

   112 

   113 Methods

   114 -------

   115 

   116 .. autoclass:: pgtoolkit.pgmanager.PgManager

   117    :members:

   118    :undoc-members:

   119