boodebr: boodebr.sql2

Module boodebr.sql2.api

boodebr : boodebr.sql2

SQL2
A common interface for APSW, pysqlite and ~MySQL

The goals of this module are:

Portability of code/SQL across database types
Stability under high load
Minimal API

The goals are not:

Efficiency (although SQL2 isn't particulary slow, you might prefer using the native interface for optimal performance)
Supporting every feature (only portable semantics are supported; if you need to use database-specific features, use the native interface instead)

This module will eventually replace boodebr.sql. It has a very similar API, yet some API-breaking changes were required to ensure stability under high loads. Rather than breaking code that uses boodebr.sql, a new module was created.

Classes

SqlBusy(Exception)

Exception raised when database is busy and timeout period has expired.

SqlBadBinding(Exception)

Exception raised when an invalid type is passed to run()

SqlConnection(object)

Returned by opensql() - do not instantiate directly.

Methods

add_table_column(self, table, column, initval=None)

Add a column to a table.

table: Table name
column: (name, type)
initval: Initial value to fill column

change_table_column(self, table, oldname, newname, newtype=None)

Rename a column and/or change the datatype of a column.

table: Name of table
oldname: Name of column to change
newname: New name for column (can equal oldname).
newtype: New type for column, or None to keep original.

close(self)

Close connection, releasing any locks.

create_table(self, name, columns)

Create a table, (only) if it does not already exist.

name: Name of table
columns: list of tuples: (name, type)
Where 'type' is one of the basic types (INTEGER, REAL, etc.)

delete_table(self, name)

Permanently delete a table (if it exists) from the named database.

delete_table_column(self, table, colname)

Delete named column from table.

get_table_colnames(self, table)

Get a list of just the column names from table.

get_table_columns(self, table)

Get a list of column names & types for the given table.
Returns a list of tuples (colname, coltype) where:

colname: Name of column
coltype: The SQL type string (not INTEGER, etc., so that arbitrary tables are supported)

The list will be in the same order as the order of the SQL statements used to create the table.

Returns [] if table doesn't exist.

get_tables(self, special=False)

Get a list of all tables in the given SQL database. If special==False, removes special names (database-specific).

has_table(self, name)

Convenience - does database contain table?

last_insert_id(self)

Returns the last autoincrement id generated

rename_table(self, oldname, newname)

Rename a table from oldname to newname.

run(self, stmt, bindings=())

Run query and returns SqlResultSet or raises:

SqlBusy: Database busy and timed-out waiting.
Other: API error etc. Should not retry.

If called from runfunc(), runs as part of current transaction.
Else, runs in its own transaction.

runfunc(self, func, args=(), kwargs={})

Run a user-supplied function inside a transaction.

func: Function, will be called as: func(sql, *args, **kwargs)
Where sql is an SqlProxy for func() to use.
args, kwargs: Args to pass to func.

No return value. On error, raises:

SqlBusy: Database busy and timed-out.
Other: API error or other error in func().

SqlResultSet(object)

The .run() method returns this. You use it like an iterator to fetch results as rows or objects:

Examples:

# as rows
for x,y in sql.run('select x,y from foo'):
    ...

# as objects
for obj in sql.run('select * from foo').obj():
    print obj.x, obj.y

Methods

__init__(self)

__iter__(self)

_next(self, itertype, klass=None)

getobj(self, klass=None)

Get next row of result as object, or None if no more data. Does not change iterator type or iterclass. If klass is None, uses the currently set classtype.

getrow(self)

Get next row of result or None if no more data. Does not change iterator type.

next(self, klass=None)

Get next row or object, depending on currently set iterator type. If iterator is set to return objects, can pass klass to set the returned class (if klass is None, uses the currently set classtype).

obj(self, klass=<class 'boodebr.sql2.common.SqlResultObj'>)

Change iterator to object type and set the class to use for returned objects.

SqlProxy(object)

Callbacks passed to runfunc() receive an object of this type. This is the only API that the called function is allowed to access. (Using self.sql directly is unsupported.)

SqlProxy has the same API as SqlConnection but only supports the following members/functions:

run
last_insert_id
INTEGER
REAL
TEXT
AUTO_PRIMARY_KEY
get_tables
has_table

Methods

__getattr__(self, name)

Functions

APIs()

Return list of available APIs (for 'api' arg in opensql)

opensql(api, *args, **kwargs)

Open a database connection. The exact calling sequence depends on the type of database you are opening. All possibilities are shown below:

APSW: from boodebr.sql2 import *
db = opensql('apsw', filename,
             binder=binder_raw,
             timeout=DEFAULT_LOCK_TIMEOUT,
             expirelock=DEFAULT_LOCK_LIFETIME)
~MySQL: from boodebr.sql2 import *
db = opensql('mysql', host, db, user, passwd,
binder=binder_raw,
timeout=DEFAULT_LOCK_TIMEOUT):
pysqlite: from boodebr.sql2 import *
db = opensql('pysqlite', filename,
             binder=binder_raw,
             timeout=DEFAULT_LOCK_TIMEOUT,
             expirelock=DEFAULT_LOCK_LIFETIME)

versions(api)

Get version information for the given API.

Returns a dict containing at least these fields:

'module_file': Module filename (native interface)
'api': Name of API
'apiversion': Version number string, as '#.#.#'

Individual modules may define extra fields.

binder_raw(value)

Raw binder - performs no conversions. Accepts only types that map trivially to INTEGER, REAL, or TEXT.

This is the default binder used by opensql()