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¶
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 namesif "foo" in db
tests if table “foo” exists in the databasedel 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
- 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 definitionmode="override"
erases the existing table and creates a new one with the field definition- if
mode
is not specified and the table already exists, anIOError
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
Type conversion¶
Conversions between Python types and SQLite field types use the behaviour of the Python SQLite module. datetime.date
, datetime.time
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 = db['dummy']
table.is_date('birthday')
cursor and commit¶
Instances of Database and Table 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() saves the changes to a database after a transaction
db.commit()