SQLite adapter

The adapter uses module sqlite :

    from pydblite import sqlite

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[,**kw]) : db_path is the database path in the file system. The keyword arguments are the same as for the method connect() of the Python built-in module sqlite3.

Instances of Database are dictionary-like objects, where keys are the table names and values are instances of the Table class:

  • db["foo"] returns the instance of the Table class for table "foo"
  • db.keys() returns the table names
  • if "foo" in db tests if table "foo" exists in the database
  • del db["foo"] drops the table "foo"

To create a new table :

table = db.create(table_name,*fields[,mode])

The fields must be 2-element tuples (field_name,field_type) where field_type is an SQLite field type : INTEGER, REAL, TEXT or BLOB :


If other information needs to be provided, put it in the second argument, using the SQL syntax for SQLite :

db.create('test',('date','BLOB DEFAULT CURRENT_DATE'))

The optional keyword argument mode specifies what you want to do if a table of the same name already exists in the database:

  • mode="open" opens the table and ignores the field definition
  • mode="override" erases the existing table and creates a new one with the field definition
  • if mode is not specified and the table already exists, an IOError is raised


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.

To insert many records at a time,


will be much faster than

for values in list_of_values:

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 : and datetime.datetime instances are stored as ISO dates/datetimes.

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

SQLite typePython type

If you want fields to be returned as instances of, 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 = db['dummy']

cursor, commit and close

Intances of Database and Table all have :
  • the attribute cursor, the SQLite connections cursor, so you can also execute SQL expressions by
    and get the result by
    results = db.cursor.fetchall()
  • the method commit() to commit changes to a database after a transaction.

Instances of Database support method close() to close the database.