boodebr: boodebr.sql.sqliteQ

This module allows you to share an SQLite connection across multiple threads. (Normally, you can only use an SQLite connection within the thread that created it.)

Example usage:

import boodebr.sql.sqliteQ as sQ

# begin queue manager thread - do this before creating
# any sqliteQ objects.
sQ.start()

q = sQ.sqliteQ('mydb.db')

# pass 'q' to as many threads as you'd like, it is threadsafe.
# you can create as many sqliteQ objects as you like; multiple
# sqliteQ objects can even use the same filename without error.

# end queue manager thread - always do this before
# your application exits
sQ.shutdown()

When using sqliteQ, make sure you have a toplevel exception handler that calls shutdown() so your application won't hang on unexpected errors.

sqliteQ API

The sqliteQ API is grouped into two categories:

Low-level querying methods
Higher-level functions, including replacements for functionality missing from SQLite's native ALTER TABLE function.

Querying

run(cmdlist)

Run a list of commands inside a transaction.

The entire transaction is rolled back if any commands fail.

Accepts cmdlist as either:

multiple commands: ((query,args), (query,args), ...)
single command: (query,args)

There is no return value. Raises an exception on error.

Example:

s = sqliteQ('mydb.db')
cmds = [
   ('create table AAA (name text)', None),
   ('create table BBB (name text)', None),
   ]
s.run(cmds)

getrows(query, args=None): Run given query and return result as a list of rows.

Example:
s = sqliteQ('mydb.db')
q = 'select id,name from MyTable where last=?'
a = ('Smith',)
for row in s.getrows(q,a):
print "ID,name", row[0], row[1]

getobjs(query, args=None, klass=None): Run given query and return result as a list of objects.
Objects will be of type klass, if given, or an anonymous type if not.

Example:
s = sqliteQ('mydb.db')
q = 'select id,name from MyTable where last=?'
a = ('Smith',)
for obj in s.getobjs(q,a):
print "ID,name", obj.id, obj.name

get_tables(special=False): Get a list of table names from database.
If special==False, filters out special sqlite_ table names.

Returns list of names.

Table-Alteration

add_table_column(table, colname, coltype, colcomment, initval=None)

Add a column to a table, creating the table if it doesn't yet exist.

table: Name of table to modify.
colname: Name for new column.
coltype: Datatype for new column.
colcomment: Comment for new column. Should be passed WITHOUT the /* */
initval: Value to fill new column with.

If an error occurs, the database is rolled-back to its original state, so the caller doesn't have to do anything but inform the user of the error (a DatabaseError exception will be raised).

There is no return value.

This will also drop any indexes on the table so you'll need to recreate those.

This limitation may be removed at some point, so I recommend you create your indexes using the form CREATE INDEX IF NOT EXISTS ...

Recent versions of SQLite offer an ALTER TABLE ADD COLUMN command. You may want to try both and see which works best for your application.

delete_table(table): Permanently delete a table from the named database.

If an error occurs, the database is rolled-back to its original state, so the caller doesn't have to do anything but inform the user of the error (a DatabaseError exception will be raised).

delete_table_columns(table, columns): Delete a list of columns from the given table.

If an error occurs, the database is rolled-back to its original state, so the caller doesn't have to do anything but inform the user of the error (a DatabaseError exception will be raised).

This will also drop any indexes on the table so you'll need to recreate those.

This limitation may be removed at some point, so I recommend you create your indexes using the form CREATE INDEX IF NOT EXISTS ...

get_table_columns(table)

Get a list of column names & types for the given table.

Returns a list of entries:

(colname, coltype, colcomment)

Where:

colname: Name of column
coltype: The SQL type string
colcomment: Aggregated comments found for column (/* .. */ and/or -- will be removed.)

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.

modify_table_column(table, cur_colname, new_colname, new_coltype=None, new_colcomment=None)

Rename and/or change the type of a column and/or associated comment.

table: Table to modify
cur_colname: Column to change
new_colname: New name for column.
new_coltype: New type for column; if None, don't change the type.
new_colcomment: New comment for column; if None, don't change comment.

If an error occurs, the database is rolled-back to its original state, so the caller doesn't have to do anything but inform the user of the error (a DatabaseError exception will be raised).

rename_table(table, newname, newcomment=None): Rename a table to newname.

If an error occurs, the database is rolled-back to its original state, so the caller doesn't have to do anything but inform the user of the error (a DatabaseError exception will be raised).

There is no return value.

This will also drop any indexes on the table so you'll need to recreate those.

This limitation may be removed at some point, so I recommend you create your indexes using the form CREATE INDEX IF NOT EXISTS ...