PyDbLite

SQLite adapter

The main difference with the pure-Python module is the syntax to identify a database and a table, and the need to specify field types on base creation

For compliance with SQLite vocabulary, the module defines two classes, Database and Table

Database(db_path) : db_path is the database path in the file system

Instances of Database support 2 methods :

tables() : returns the list of the table names in the database
has_table(table_name) : returns a boolean indicating if there is a table named table_name in the database

Table(table_name,db) : table_name is the table name, db is either the database path in the file system, or the matching instance of Database

Manipulating a table uses the same syntax as in PyDbLite :


to create the table you must specify an SQLite field type : INTEGER, REAL, TEXT
or BLOB :

db.create(('name','TEXT'),('age','INTEGER'),('size','REAL'))
if other information needs to be provided, put it in the second argument, using the SQL syntax for SQLite :
db.create(('recid','INTEGER PRIMARY KEY AUTOINCREMENT'),

        ('date','BLOB DEFAULT CURRENT_DATE'))


For record insertion, updating, deletion and selection the syntax is the same as for the pure-Python module. The SQLite primary key rowid is used like the key __id__ to identify records

Note that you can't use the drop_field() method, since dropping fields is not supported by SQLite

Conversions between Python types and SQLite field types use the behaviour of the Python SQLite module : datetime.date and datetime.datetime instances are stored as ISO dates/datetimes

Selection methods return dictionaries, with SQLite types converted to Python types like this :


SQLite type Python type
NULL None
TEXT unicode
BLOB str
INTEGER int
REAL float


If you want fields to be returned as instances of datetime.date, datetime.time or datetime.datetime instances, you can specify it when creating or opening the table, using methods is_date(field_name), is_time(field_name) or is_datetime(field_name) :

db = Database('test.sqlite')

table = Table('dummy',db)

table.is_date('birthday')



The Table instance has an attribute cursor, so you can also execute
SQL expressions by db.cursor.execute(some_sql) and get the result
by results = db.cursor.fetchall()