doc/pgmanager.rst
author Radek Brich <radek.brich@devl.cz>
Tue, 05 Mar 2013 11:24:47 +0100
changeset 71 4251068a251a
parent 69 1bf7d2f5e00c
child 79 d02a36d0a9b0
permissions -rw-r--r--
Update MyManager.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
69
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
PgManager
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
=========
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     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>`_.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
PgManager offers following convenience functionality over psycopg:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
* **Save and reuse database connection parameters**
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
  - parameters are linked to uniquely named ConnectionInfo structure
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
  - using this identifier, you can retrieve Connection or Cursor object at any time
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
    (new database connection is established when needed)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
* **Connection pooling**
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
  - connections with same identifier are pooled and reused
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
* **Easy query using the with statement**
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
  - just use cursor() method in with statement
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
  - PgManager will get a connection from pool, make cursor and clean up when you're finished
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
* **Dictionary rows**
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
  - cursor has additional methods like fetchall_dict(), which
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
    returns OrderedDict row instead of ordinary list-like row
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
Basic usage
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
-----------
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
::
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
71
4251068a251a Update MyManager.
Radek Brich <radek.brich@devl.cz>
parents: 69
diff changeset
    36
  from pgtoolkit import pgmanager
69
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
  pgm = pgmanager.get_instance()
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
  pgm.create_conn(hostaddr='127.0.0.1', dbname='postgres', isolation_level='autocommit')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    40
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
  with pgm.cursor() as curs:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
      curs.execute('SELECT now() AS now')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
      row = curs.fetchone_dict()
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
      print(row.now)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
Advanced usage
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
--------------
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
Study this when you cannot go with basic usage and need to know what actually happens.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51
Look also into source code, there are many details described in docstrings::
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    52
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
  # create new connection info, this will not connect yet
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
  pgm.create_conn(name='thatdb', hostaddr='127.0.0.1', dbname='postgres')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
  # borrow connection from pool (it will connect to DB here and possibly fail)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    57
  conn = pgm.get_conn('thatdb')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
  try:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    60
      # do stuff on that connection
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
      curs = conn.cursor()
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    62
      curs.execute('SELECT now() AS now')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    63
      row = curs.fetchone_dict()
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    64
      # don't forget to commit when not in autocommit mode
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
      curs.connection.commit()
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
      print(row.now)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    67
  finally:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
      # return connection back to pool
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69
      # Beware: name must be correct here, it'll not be checked!
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    70
      pgm.put_conn(conn, 'thatdb')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    71
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    72
  # close all connections in pool
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    73
  pgm.close_conn('thatdb')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    74
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    75
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    76
Logging
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    77
-------
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    78
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    79
PgManager logs all queries before they are executed, exceptions while executing
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    80
queries and also notices.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    81
Queries and exceptions are logged into 'pgmanager_sql' logger,
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    82
notices into 'pgmanager_notices'. You need to setup logging for
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    83
these loggers to send messages into file or elsewhere.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    84
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    85
Notices are logged only from cursor() method.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    86
If you are using get_conn() instead, you must call log_notices() directly.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    87
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    88
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    89
Listen - Notify
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    90
---------------
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    91
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    92
PostgreSQL supports asynchronous messages from server to clients.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    93
This adds little more complexity to client programs but saves a lot of polling.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    94
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    95
You should not use connection info created by create_conn() for LISTEN queries.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    96
It will work in basic case, but it will broke when you add some sort of parallelism.
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    98
Use create_conn_listen instead. It allows to copy connection info parameters directly from another connection info or set up new one::
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    99
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   100
  # create connection info without pool and with initial query 'LISTEN CHAT;'
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   101
  pgm.create_conn_listen(name='listenconn', channel='CHAT', copy_dsn='thatdb')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   102
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   103
  while not done:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   104
      notify = pgm.wait_for_notify('listenconn', timeout=5.0)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   105
      if notify:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   106
          print('NOTIFY:', notify)
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   107
      else:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   108
          print('timeout')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   109
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   110
  pgm.close_conn('listenconn')
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   111
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   112
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   113
Methods
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   114
-------
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   115
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   116
.. autoclass:: pgtoolkit.pgmanager.PgManager
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   117
   :members:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   118
   :undoc-members:
1bf7d2f5e00c Add doc for PgManager.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   119