Franšais

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

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 :

db.create('test',('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('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

Table

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,

table.insert(list_of_values)

will be much faster than

for values in list_of_values:
    table.insert(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 : 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 typePython type
NULLNone
TEXTunicode
BLOBstr
INTEGERint
REALfloat

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 = db['dummy']
table.is_date('birthday')

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
    db.cursor.execute(some_sql)
    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.