lib.db

class lib.db.Database(name, dbapi, connect, formatting='named')

Bases: object

A database abstraction layer based on DB-API2 specification.

It provides basic functionality to access databases using Python driver implementations based on the DB-API2 specification (PEP 249).

The following methods are provided: ‚__init__()‘ - create a new database object ‚connect()‘ - establish the connection to the database ‚close()‘ - close the connection to the database ‚setup()‘ - check/update/upgrade database structure ‚execute()‘ - execute statement (no result returned) ‚fetchone()‘ - execute statement and return first row from result ‚fetchall()‘ - execute statement and reeturn all rows from result ‚cursor()‘ - create a cursor object to execute multiple statements ‚commit()‘ - commit a transaction (if the selcted database supports it) ‚rollback()‘ - rollback a transaction (if the selcted database supports it) ‚lock()‘ - acquire the database lock (prevent simultaneous reads/writes) ‚release()‘ - release the database lock ‚verify()‘ - check database connection and reconnect if required ‚connected()‘ - check if database is connected

The SQL statements executed may have placeholders and parameters which are passed to the execution methods listed above. The following DB-API driver implementations are supported: - qmark: Specify placeholders as „?“ and parameters as list - format: Specify placeholders as „%s“ and parameters as list - numeric: Specify placeholders as „:1“ and parameters as list - named: Specify placeholders as „:name“ and parameters as dict - pyformat: Specify placeholders as „%(arg)s“ and parameters as dict

Further you can choose a different formatting style in your code when using this class. Specify one of the formatting listed above or use the default - which is named.

In case the driver implementation uses a different formatting it will be converted transparently!

close()

Closes the database connection

commit()

Commit the current transaction

connect()

Connects to the database

connected()

Return the connected status

cursor()

Create a new cursor for executing statements

execute(stmt, params=(), formatting=None, cur=None)

Execute the given statement

This will execute the statement specified in the ‚stmt‘ parameter which may contain parameter placeholders (depending on selected formatting style given in constructor).

The parameters can be specified in ‚params‘ parameter as list or dict depending on selected formatting style.

To overwrite the global formatting style given in constructor, the parameter ‚formatting‘ can be used to change the style for the given statement.

If already aqcuired a cursor you can use this cursor by using the ‚cur‘ parameter. If omitted a new cursor will be aqcuire for this statement and released afterwards.

fetchall(stmt, params=(), formatting=None, cur=None)

Execute given statement and fetch all rows from result

This method can be used to fetch all rows from the result. It accepts the same arguments as mentioned in the ‚execute()‘ method.

fetchone(stmt, params=(), formatting=None, cur=None)

Execute given statement and fetch one row from result

This method can be used in case you only want to fetch one row from the result. It accepts the same arguments as mentioned in the ‚execute()‘ method.

lock(timeout=-1)

Acquire a database lock

release()

Release the database lock

rollback()

Rollback the current transaction

setup(queries)

Setup or update the database structure.

This method can be used to setup the database structure by providing the SQL statements to this method. Additionally it will check if the structure is already up to date by checking the data of the version table (which will also be created by this method if it does not exist already).

To setup the database you need to specify the required SQL statements (e.g. ‚CREATE TABLE‘, ‚CREATE INDEX‘ etc.) in the ‚queries‘ parameter. This will be a dictionary where the keys are simple version numbers and values are a two-item list for a rollout and rollback statement.

E.g.::

db.setup({1:[‚CREATE TABLE xyz (…)‘, ‚DROP TABLE xyz‘], 2:[…]})

For an extended example take a look into the ‚database‘ plugin.

verify(retry=5, delay=5)

Verifies the connection status and reconnets if required

The connected status of the connection will be checked by executing a simple SQL statement. If this fails or the connection is not established already a new connection will be opened.

In case the reconnect fails you can specify how many times a reconnect will be executed until it will give up. This can be specified by the ‚retry‘ parameter.

To specify the delay between retries use the delay parameter, which defaults to 5 seconds.