Make DB API 2.0 Easier to Use — mosql.db

New in version 0.7.

It makes it easier to use the module which conforms Python DB API 2.0.

The context manager for both connection and cursor:

Database([module]) It is a context manager which manages the creation and destruction of a connection and its cursors.

The functions designed for cursor:

extract_col_names(cur) Extracts the column names from a cursor.
one_to_dict([cur, row, col_names]) Fetch one row from a cursor and make it as a dict.
all_to_dicts([cur, rows, col_names]) Fetch all rows from a cursor and make them as dicts in a list.
group(by_col_names[, cur, rows, col_names, ...]) Groups the rows in application-level.
class mosql.db.Database(module=None, *conn_args, **conn_kargs)[source]

It is a context manager which manages the creation and destruction of a connection and its cursors.

Parameters:module – a module which conforms Python DB API 2.0

Initialize a Database instance:

import psycopg2
db = Database(psycopg2, host='127.0.0.1')

Note it just tells Database how to connect to your database. No connection or cursor is created here.

Then get a cursor to communicate with database:

with db as cur:
    cur.execute('select 1')

The connection and cursor are created when you enter the with-block, and they will be closed when you leave. Also, the changes will be committed at the same time, or be rollbacked if there is any exception.

If you need multiple cursors, just say:

with db as cur1, db as cur2:
    cur1.execute('select 1')
    cur2.execute('select 2')

Each Database instance at most has one connection. The cursors share a same connection no matter how many cursors you asked.

It is possible to customize the creating of connection or cursor. If you want to customize, override the attributes you need:

db = Database()
db.getconn = lambda: pool.getconn()
db.putconn = lambda conn: pool.putconn(conn)
db.getcur  = lambda conn: conn.cursor('named-cusor')
db.putcur  = lambda cur : cur.close()

By default, the connection will be closed when you leave with-block. If you want to keep the connection open, set to_keep_conn as True. It will be useful in single threading environment.

db.to_keep_conn = True

New in version 0.10: the to_keep_conn.

Changed in version 0.10: This class is thread-safe now.

Changed in version 0.12: This class supports multithreading and multiprocessing better now.

Changed in version 0.12.3: When the nest with case, it only commits after exit the first with.

mosql.db.all_to_dicts(cur=None, rows=None, col_names=None)[source]

Fetch all rows from a cursor and make them as dicts in a list.

If col_names or rows is provided, it will be used first.

Return type:dicts in list
mosql.db.extract_col_names(cur)[source]

Extracts the column names from a cursor.

Return type:list
mosql.db.group(by_col_names, cur=None, rows=None, col_names=None, to_dict=False)[source]

Groups the rows in application-level.

If col_names or rows is provided, it will be used first.

Return type:row generator

Assume we have a cursor named cur has the data:

col_names = ['id', 'email']
rows = [
    ('alice', 'alice@gmail.com'),
    ('mosky', 'mosky.tw@gmail.com'),
    ('mosky', 'mosky.liu@pinkoi.com')
]

Group the rows in cur by id.

for row in group(['id'], cur):
    print row

The output:

('alice', ['alice@gmail.com'])
('mosky', ['mosky.tw@gmail.com', 'mosky.liu@pinkoi.com'])
mosql.db.one_to_dict(cur=None, row=None, col_names=None)[source]

Fetch one row from a cursor and make it as a dict.

If col_names or row is provided, it will be used first.

Return type:dict