pydbal 0.12

Description:

pydbal 0.12

Database Abstraction Layer (DBAL) for Python 2.6+.
pyDBAL library is the improved and optimised port of Doctrine
DBAL project.

Installation
$ pip install pydbal


Requirements
For using mysql driver MySQLdb library is required. Optionally
lrucache is required to maintain memory safe cache operations.


Basic Usage
To open new connection import Connection from pydbal.connection
package and initialise Connection class for a required driver with
desired parameters.
from pydbal.connection import Connection

conn = Connection('mysql', host='localhost', user='root', database='mydb')
pyDBAL currently supports the following drivers: mysql and
sqlite. You can create a custom driver by inheriting
pydbal.drivers.BaseDriver and passing to Connection constructor.

Query Statements
To SELECT data from the database you may use query method. This
method will return the instance of pydbal.statement.Statement.
# simple fetch generator
for row in conn.query('SELECT * FROM table'):
print(row)

# same as the above but fetch mode can be applied (Connection.FETCH_*)
for row in conn.query('SELECT * FROM table').iterate(fetch_mode=Connection.FETCH_OBJECT):
print(row)

# fetch row by row
result = conn.query('SELECT * FROM table')
row1 = result.fetch()
row2 = result.fetch()

# fetch all rows
rows = conn.query('SELECT * FROM table').fetch_all()

# fetch single value from column
count = conn.query('SELECT COUNT(*) FROM table').fetch_column()

# fetch all values from column by index
ids = conn.query('SELECT id FROM table').fetch_all(fetch_mode=Connection.FETCH_COLUMN, column_index=0)


Execute Statements
To execute INSERT, UPDATE or DELETE statements you may use
execute method. This method will return number of affected rows.
# INSERT
conn.execute('INSERT INTO table VALUES (?)', [val1, val2, val3])
last_insert_id = conn.last_insert_id()

# UPDATE
affected_rows = conn.execute('UPDATE table SET column = ? WHERE id = ?', val1, id_)

# DELETE
affected_rows = conn.execute('DELETE FROM table WHERE id = ?', id_)


Statement Parameters
Both query and execute methods support safe parameter binding by
passing arguments after the first sql argument.
# single positional parameter
row = conn.query('SELECT * FROM table WHERE id = ?', id_).fetch()

# multiple positional parameters
row = conn.query('SELECT * FROM table WHERE id = ? OR id = ?', id1, id2).fetch()

# named parameters
row = conn.query('SELECT * FROM table WHERE id = :id1 OR id = :id2', id1=id1, id2=id2).fetch()

# iterable parameters
row = conn.query('SELECT * FROM table WHERE id IN (?)', [id1, id2]).fetch()


Transactions
pyDBAL supports transactional operations.
conn.begin_transaction()
try:
# ... execute statements ...
conn.commit()
return smth
except:
conn.rollback()
raise

# same as the above
def trans(conn):
# ... execute statements ...
return smth
smth = conn.transaction(trans)
If database platform supports savepoints you may enable and use nested
transactions.
conn.set_nest_transactions_with_savepoints(True)
conn.begin_transaction()
# ... execute statements 1 ...
conn.begin_transaction()
# ... execute statements 2 ...
conn.commit() # commit 1
conn.rollback() # rollback 2

# to control savepoints manually
conn.create_savepoint('MYSAVEPOINT')
conn.release_savepoint('MYSAVEPOINT')
conn.rollback_savepoint('MYSAVEPOINT')


SQL Builder
To make writing SQL statements more simple and flexible it’s suggested
to use pydbal.builder.SQLBuilder.
# SELECT
sqb = (
conn.sql_builder()
.select('t1.id', 't2.id', 'SUM(t1.col) AS special')
.from_('table1', 't1')
.join('t1', 'table2', 't2', 't2.id = t1.id')
.where('t1.col = :val')
.set_parameter('val', val)
.group_by('t1.col')
.having('special IS NOT NULL')
.order_by('t2.id')
)
for row in sqb.execute():
print(row)

# INSERT
last_insert_id = (
conn.sql_builder()
.insert('table')
.values({'col1': val1, 'col2': val2})
).execute()

# UPDATE
affected_rows = (
conn.sql_builder()
.update('table')
.set('col1', val1)
.set('col2', val2)
.where('id = :id')
.set_parameter('id', id_)
).execute()

# DELETE
affected_rows = (
conn.sql_builder()
.delete('table')
.where('id = ?')
.set_parameter(0, id_)
).execute()


Expression Builder
WHERE, HAVING and JOIN ... ON expressions can be created
using pydbal.builder.ExpressionBuilder.
expr = conn.get_expression_builder()
# or via SQL Builder instance
# expr = sqb.expr()

sqb.where(
expr.and_x(expr.eq('a', 'b'), expr.is_null('c'))
.or_x(
expr.and_x('d IS NULL', expr.in_('e', ['1', '2', '3'])),
expr.neq('f', expr.literal('abc'))
)
)


Schema Manager
pyDBAL comes with simple read only SQL schema manager. It supports
listing of databases, tables, views, columns, indexes and foreign keys.
Internal database queries are cached with pydbal.cache mechanisms.
sm = conn.get_schema_manager()

# database names
db_names = sm.get_database_names()

# views
views = sm.get_views()
view_names = sm.get_view_names()

# tables
tables = sm.get_tables()
table_names = sm.get_table_names()

# columns
table_columns = sm.get_table_columns('table')
table_column_names = sm.get_table_column_names('table')

# indexes
table_indexes = sm.get_table_indexes('table')
table_index_names = sm.get_table_index_names('table')

# foreign keys
table_foreign_keys = sm.get_table_foreign_keys('table')
table_foreign_key_names = sm.get_table_foreign_key_names('table')


Thread-safe Connection
pyDBAL v0.10+ supports thread-safe connection functionality implemented in
pydbal.threading module.
from pydbal.threading import SafeConnection

conn = SafeConnection('mysql', host='localhost', user='root', database='mydb')
SafeConnection wrapper class maintains active connections in locked pool
and provides helper methods for manipulating your data. Class implements method
locked() which should be passed to with statement. It generates
isolated connection context, that can be used for sending non-trivial commands
to the original pydbal.connection.Connection object.
# simple fetch generator
for row in conn.query('SELECT * FROM table'):
print(row)

# fetch one row
row = conn.fetch('SELECT * FROM table WHERE id = ?', id_)

# fetch all rows
rows = conn.fetch_all('SELECT * FROM table')

# fetch single value from column
count = conn.fetch_column('SELECT COUNT(*) FROM table')

# UPDATE or DELETE queries
affected_rows = conn.execute('UPDATE table SET column = ? WHERE id = ?', val1, id_)

# INSERT query with last inserted ID
with conn.locked() as _conn:
_conn.execute('INSERT INTO table VALUES (?)', [val1, val2, val3])
last_insert_id = _conn.last_insert_id()

# transaction callback
def trans(_conn):
# ... execute statements ...
return smth
smth = conn.transaction(trans)



License
Library is available under the MIT license. The included LICENSE file
describes this in detail.