cjklib.dbconnector — SQL database access

Simple read access to (multiple) SQL databases.

A DatabaseConnector connects to one or more SQL databases. It provides four simple methods for retrieving scalars or rows of data:

1. selectScalar(): returns one single value
2. selectRow(): returns only one entry with several columns
3. selectScalars(): returns entries for a single column
4. selectRows(): returns multiple entries for multiple columns

This class takes care to load the correct database(s). It provides for attaching further databases and gives any program that depends on cjklib the possibility to easily add own data in databases outside cjklib extending the library’s information.

DatabaseConnector has a convenience function getDBConnector() that loads an instance with the proper settings for the given project. By default settings for project 'cjklib' are chosen, but this behaviour can be overwritten by passing a different project name: getDBConnector(projectName='My Project'). Connection settings can also be provided manually, omitting automatic searching. Multiple calls with the same connection settings will return the same shared instance.

Example:

>>> from cjklib import dbconnector
>>> from sqlalchemy import select
>>> db = dbconnector.getDBConnector()
>>> db.selectScalar(select([db.tables['Strokes'].c.Name],
...     db.tables['Strokes'].c.StrokeAbbrev == 'T'))
u'\u63d0'

DatabaseConnector is tested on SQLite and MySQL but should support most other database systems through SQLAlchemy.

SQLite and Unicode

SQLite be default only provides letter case folding for alphabetic characters A-Z from ASCII. If SQLite is built against ICU, Unicode methods are used instead for LIKE and upper()/lower(). If ICU is not compiled into the database system DatabaseConnector can register own methods. As this has negative impact on performance, it is disabled by default. Compatibility support can be enabled by setting option 'registerUnicode' to True when given as configuration to __init__() or getDBConnector() or alternatively can be set as default in cjklib.conf.

Multiple database support

A DatabaseConnector instance is attached to a main database. Further databases can be attached at any time, providing further tables. Tables from the main database will shadow any other table with a similar name. A table not found in the main database will be chosen from a database in the order of their attachment.

The tables dictionary allows simple lookup of table objects by short name, without the need of knowing the full qualified name including the database specifier. Existence of tables can be checked using hasTable(); tables will only include table information after the first access. All table names can be retrieved with getTableNames().

Table lookup is designed with a stable data set in mind. Moving tables between databases is not specially supported and while operations through the cjklib.build module will update any information in the tables dictionary, manual creating and dropping of a table or changing its structure will lead to the dictionary having obsolete information. This can be circumvented by deleting keys forcing an update:

>>> del db.tables['my table']

Example:

>>> from cjklib.dbconnector import DatabaseConnector
>>> db = DatabaseConnector({'url': 'sqlite:////tmp/mydata.db',
...     'attach': ['cjklib']})
>>> db.tables['StrokeOrder'].fullname
'cjklib_0.StrokeOrder'

Discovery of attachable databases

DatabaseConnector has the ability to discover databases attaching them to the main database. Specifying databases can be done in three ways:

1. A full URL can be given denoting a single database, e.g. 'sqlite:////tmp/mydata.db'.
2. Giving a directory will add any .db file as SQLite database, e.g. '/usr/local/share/cjklib'.
3. Giving a project name will prompt DatabaseConnector to check for a project config file and add databases specified there and/or scan that project’s default directories, e.g. 'cjklib'.

Functions

cjklib.dbconnector.getDBConnector(configuration=None, projectName='cjklib')

Returns a shared DatabaseConnector instance.

To connect to a user specific database give {'sqlalchemy.url': 'driver://user:pass@host/database'} as configuration.

If no configuration is passed a connection is made to the default database PROJECTNAME.db in the project’s folder.

New in version 0.3.

See also

documentation of sqlalchemy.create_engine()

Parameters:

• configuration – database connection options (includes settings for SQLAlchemy prefixed by 'sqlalchemy.')
• projectName (str) – name of project which will be used as name of the config file

cjklib.dbconnector.getDefaultConfiguration(projectName='cjklib')

Gets the default configuration for the given project. Settings are read from a configuration file.

By default an URL to a database PROJECTNAME.db in the project’s folder is returned and the project’s default directories are searched for attachable databases.

New in version 0.3.

Parameter:projectName (str) – name of project which will be used in search for the configuration and as the name of the default database

Classes

class cjklib.dbconnector.DatabaseConnector(configuration)

Bases: object

Database connection object.

Constructs the DatabaseConnector object and connects to the database specified by the options given in databaseSettings.

To connect to a database give {'sqlalchemy.url': 'driver://user:pass@host/database'} as configuration. Further databases can be attached by passing a list of URLs or names for keyword 'attach'.

See also

documentation of sqlalchemy.create_engine()

Parameter:configuration (dict) – database connection options for SQLAlchemy

attachDatabase(databaseUrl)

Attaches a database to the main database.

New in version 0.3.

Parameter:databaseUrl (str) – database URL Return type:str Returns:the database’s schema used to access its tables, None if that database has been attached before

execute(*options, **keywords)

Executes a request on the given database.

classmethod getDBConnector(*args, **kwargs)

Note

Deprecated method, use getDBConnector() instead.

classmethod getDefaultConfiguration(*args, **kwargs)

Note

Deprecated method, use getDefaultConfiguration() instead.

getTableNames()

Gets the unique list of names of all tables (and views) from the databases.

New in version 0.3.

Return type:iterable Returns:all tables and views

hasTable(tableName)

Returns True if the given table exists in one of the databases.

New in version 0.3.

Parameter:tableName (str) – name of table to be located Return type:bool Returns:True if table is found, False otherwise

iterRows(request)

Executes a select query and returns an iterator of table rows.

New in version 0.3.

Parameter:request – SQL request Returns:an iterator of tuples

iterScalars(request)

Executes a select query and returns an iterator of scalars.

New in version 0.3.

Parameter:request – SQL request Returns:an iterator of scalars

mainHasTable(tableName)

Returns True if the given table exists in the main database.

New in version 0.3.

Parameter:tableName (str) – name of table to be located Return type:bool Returns:True if table is found, False otherwise

selectRow(request)

Executes a select query and returns a single table row.

Parameter:request – SQL request Returns:a list of scalars

selectRows(request)

Executes a select query and returns a list of table rows.

Parameter:request – SQL request Returns:a list of tuples

selectScalar(request)

Executes a select query and returns a single variable.

Parameter:request – SQL request Returns:a scalar

selectScalars(request)

Executes a select query and returns a list of scalars.

Parameter:request – SQL request Returns:a list of scalars