29 |
29 |
30 """Postgres database connection manager |
30 """Postgres database connection manager |
31 |
31 |
32 PgManager wraps psycopg2 connect function, adding following features: |
32 PgManager wraps psycopg2 connect function, adding following features: |
33 |
33 |
34 * Manage database connection parameters - link connection parameters |
34 * Save and reuse database connection parameters |
35 to an unique identifier, retrieve connection object by this identifier |
35 |
36 |
36 * Connection pooling |
37 * Connection pooling - connections with same identifier are pooled and reused |
37 |
38 |
38 * Easy query using the with statement |
39 * Easy query using the with statement - retrieve cursor directly by connection |
39 |
40 identifier, don't worry about connections |
40 * Dictionary rows |
41 |
41 |
42 * Dict rows - cursor has additional methods like fetchall_dict(), which |
42 Example usage: |
43 returns dict row instead of ordinary list-like row |
43 |
44 |
44 import pgmanager |
45 Example: |
45 |
46 |
46 pgm = pgmanager.get_instance() |
47 import pgmanager |
47 pgm.create_conn(hostaddr='127.0.0.1', dbname='postgres') |
48 |
48 |
49 pgm = pgmanager.get_instance() |
49 with pgm.cursor() as curs: |
50 pgm.create_conn(hostaddr='127.0.0.1', dbname='postgres') |
50 curs.execute('SELECT now() AS now') |
51 |
51 row = curs.fetchone_dict() |
52 with pgm.cursor() as curs: |
52 print(row.now) |
53 curs.execute('SELECT now() AS now') |
|
54 row = curs.fetchone_dict() |
|
55 print row.now |
|
56 |
53 |
57 First, we have obtained PgManager instance. This is like calling |
54 First, we have obtained PgManager instance. This is like calling |
58 PgManager(), although in our example the instance is global. That means |
55 PgManager(), although in our example the instance is global. That means |
59 getting the instance in another module brings us all the defined connections |
56 getting the instance in another module brings us all the defined connections |
60 etc. |
57 etc. |
65 the connection is returned to the pool or closed (depending on number of connections |
62 the connection is returned to the pool or closed (depending on number of connections |
66 in pool and on setting of pool_size parameter). |
63 in pool and on setting of pool_size parameter). |
67 |
64 |
68 The row returned by fetchone_dict() is special dict object, which can be accessed |
65 The row returned by fetchone_dict() is special dict object, which can be accessed |
69 using item or attribute access, that is row['now'] or row.now. |
66 using item or attribute access, that is row['now'] or row.now. |
|
67 |
70 """ |
68 """ |
71 |
69 |
72 from contextlib import contextmanager |
70 from contextlib import contextmanager |
73 from collections import OrderedDict |
71 from collections import OrderedDict |
74 import logging |
72 import logging |
267 |
265 |
268 def create_conn(self, name='default', isolation_level=None, keep_alive=True, init_statement=None, |
266 def create_conn(self, name='default', isolation_level=None, keep_alive=True, init_statement=None, |
269 pool_size=1, dsn=None, **kwargs): |
267 pool_size=1, dsn=None, **kwargs): |
270 '''Create named connection. |
268 '''Create named connection. |
271 |
269 |
272 name -- name for connection (default is "default") |
270 *name* -- name for connection |
273 pool_size -- how many connections will be kept open in pool |
271 |
274 (more connections will still be created but they will be closed by put_conn) |
272 *pool_size* -- how many connections will be kept open in pool. |
275 None - disable pool, always return same connection |
273 More connections will still be created but they will be closed by put_conn. |
276 isolation_level -- "autocommit", "read_committed", "serializable" or None for driver default |
274 `None` will disable pool, get_conn() will then always return same connection. |
277 keep_alive -- set socket to keepalive mode |
275 |
278 dsn -- connection string (parameters or data source name) |
276 *isolation_level* -- `"autocommit"`, `"read_committed"`, `"serializable"` or `None` for driver default |
|
277 |
|
278 *keep_alive* -- set socket to keepalive mode |
|
279 |
|
280 *dsn* -- connection string (parameters or data source name) |
279 |
281 |
280 Other keyword args are used as connection parameters. |
282 Other keyword args are used as connection parameters. |
281 |
283 |
282 ''' |
284 ''' |
283 if name in self.conn_known: |
285 if name in self.conn_known: |
297 |
299 |
298 Disables pool. If you want to use pool, create other connection for that. |
300 Disables pool. If you want to use pool, create other connection for that. |
299 This connection can be used as usual: conn.cursor() etc. |
301 This connection can be used as usual: conn.cursor() etc. |
300 Don't use PgManager's cursor() and put_conn(). |
302 Don't use PgManager's cursor() and put_conn(). |
301 |
303 |
302 name -- name for connection |
304 *name* -- name for connection |
303 channel -- listen on this channel |
305 |
304 copy_dsn -- specify name of other connection and its dsn will be used |
306 *channel* -- listen on this channel |
|
307 |
|
308 *copy_dsn* -- specify name of other connection and its dsn will be used |
305 |
309 |
306 Other parameters forwarded to create_conn(). |
310 Other parameters forwarded to create_conn(). |
307 |
311 |
308 ''' |
312 ''' |
309 if dsn is None and copy_dsn: |
313 if dsn is None and copy_dsn: |
379 return conn |
383 return conn |
380 |
384 |
381 def put_conn(self, conn, name='default'): |
385 def put_conn(self, conn, name='default'): |
382 '''Put connection back to pool. |
386 '''Put connection back to pool. |
383 |
387 |
384 Name must be same as used for get_conn, |
388 *name* must be same as used for get_conn, otherwise things become broken. |
385 otherwise things become broken. |
|
386 |
389 |
387 ''' |
390 ''' |
388 self.lock.acquire() |
391 self.lock.acquire() |
389 try: |
392 try: |
390 if not name in self.conn_known: |
393 if not name in self.conn_known: |
432 def log_notices(self, conn): |
435 def log_notices(self, conn): |
433 for notice in conn.notices: |
436 for notice in conn.notices: |
434 log_notices.info(notice.rstrip()) |
437 log_notices.info(notice.rstrip()) |
435 conn.notices[:] = [] |
438 conn.notices[:] = [] |
436 |
439 |
437 def wait_for_notify(self, name='default', timeout=5.0): |
440 def wait_for_notify(self, name='default', timeout=None): |
438 '''Wait for asynchronous notifies, return the last one. |
441 '''Wait for asynchronous notifies, return the last one. |
439 |
442 |
440 name -- name of connection, must be created using create_conn_listen() |
443 *name* -- name of connection, must be created using `create_conn_listen()` |
441 timeout -- in seconds, floating point (None - wait forever) |
444 |
442 |
445 *timeout* -- in seconds, floating point (`None` means wait forever) |
443 Returns None on timeout. |
446 |
|
447 Returns `None` on timeout. |
444 |
448 |
445 ''' |
449 ''' |
446 conn = self.get_conn(name) |
450 conn = self.get_conn(name) |
447 |
451 |
448 # return any notifies on stack |
452 # return any notifies on stack |