How-to guide

Warning

This project is no longer maintained as of February 2023. It is recommended that you use a proper ORM like SQLAlchemy or Django’s ORM instead.

Opening a database

A connection to a SQLite database is opened with the Database class, which is designed to be used as a context manager:

with Database("db.sqlite3") as db:
    ...

This is equivalent to:

db = Database("db.sqlite3")
try:
    ...
except Exception as e:
    db.rollback()
    raise e
else:
    db.commit()
finally:
    db.close()

Notice that the scope of the with statement is also the scope of the database transaction, which is either committed at the end or rolled back if an exception occurs. This ensures that either all of the database operations in the with statement are applied, or none of them are, protecting you from leaving the database in an inconsistent state.

Like sqlite3.connect, the Database constructor accepts ":memory:" as a path argument to open an in-memory database. You may also pass readonly=True to open the database in read-only mode.

Reading rows

To retrieve a list of rows from the database, use Database.select:

rows = db.select("employees")

This will return every row in the table. isqlite returns rows as OrderedDict objects, so if your table has an id column, you could access it as row["id"]. Since the dictionaries are ordered, you can iterate over the keys in the same order that they appear in the database schema.

To filter the list of rows, use the where parameter to Database.select:

rows = db.select("employees", where="salary > 50000")

If you need to interpolate values from Python into the where parameter, use the values parameter:

rows = db.select("employees", where="salary > :min_salary", values={"min_salary": 50000})

Do not directly put values into the where string using Python string interpolation as this will leave you vulnerable to a SQL injection attack.

Often you do not need to retrieve every column from the database. To limit the columns that are retrieved, use the columns parameter:

rows = db.select("employees", columns=["id", "salary"])

Database.select supports several additional parameters such as order_by, limit, and offset, which are described in the API reference.

To retrieve a single row, use Database.get:

row = db.get("employees", where="salary > 50000")

Database.get works exactly like Database.select except that it only returns a single row, or None if no matching rows were found.

To retrieve a row by its primary key, use Database.get_by_pk:

row = db.get_by_pk("employees", 123)

To count the number of rows, use Database.count:

n = db.count("employees", where="salary > 50000")

This is equivalent to len(db.select(...)), but it is more efficient because it does not retrieve the actual contents of the rows.

Inserting and updating rows

Database.insert is used to insert a new row into the database:

pk = db.insert("employees", {"name": "John Doe", "salary": 75000})

Database.insert returns the primary key of the row that was inserted, which can then be retrieved with Database.get_by_pk.

To update an existing row or rows, use Database.update:

db.update("employees", {"yearly_bonus": 1000}, where="tenure > 5")

Like Database.select, Database.update accepts where and values parameters to control which rows to update. To update a single row by its primary key, use Database.update_by_pk:

db.update_by_pk("employees", 123, {"yearly_bonus": 500})

Multiple rows can be inserted efficiently using Database.insert_many:

db.insert_many("employees", [{"name": "John Doe"}, {"name": "Jane Doe"}])

This is the same as:

for row in data:
    db.insert(table, row)

But it uses a single SQL statements instead of N statements.

A common pattern is to query for a particular row and insert it if it doesn’t exist. isqlite supports this with Database.get_or_insert:

row = db.get_or_insert("employees", {"name": "John Doe"})

This will query the employees table for a row with the name John Doe and either return it or insert it and return it if it does not exist.

Deleting rows

isqlite provides three methods to delete rows: Database.delete, Database.delete_by_pk, and Database.delete_many_by_pks. Like Database.select and Database.update, Database.delete accepts where and values parameters:

db.delete("employees", where="tenure > 100")

The where parameter is required, to prevent you from accidentally deleting every row in the table with db.delete(table). If you do actually wish to delete every row in the table, you can do db.delete(table, where="1").

Using raw SQL

Sometimes, you may need to write more advanced or fine-tuned SQL queries than the built-in Database methods support. In such cases, you can execute raw SQL using Database.sql:

db.sql("SELECT * FROM employees WHERE salary > :salary", values={"salary": 50000})

If you need access to the underlying sqlite3.Connection object, e.g. for advanced functionality like Connection.set_progress_handler, it is available as Database.connection.

Controlling transactions

By default, the Database object will open a transaction immediately and commit it when the database is closed. More fine-grained control of transactions is available through the Database.transaction method:

with Database("db.sqlite3", transaction=False) as db:
    with db.transaction():
        ...

    with db.transaction():
        ...

Each with statement represents a separate transaction. transaction=False tells the Database object to not open a transaction immediately. This means that any statements run outside of a Database.transaction() block will be committed immediately.

Datbase.transaction is solely intended to be used as a context manager. Its return value should be ignored.

Converters and adapters

Converters and adapters are Python functions that translate values between Python and SQL. In addition to the default datetime.date/DATE and datetime.datetime/TIMESTAMP functions that Python’s sqlite3 module registers, isqlite automatically registers converters and adapters for BOOLEAN, DECIMAL, and TIME columns, for Python bool, decimal.Decimal, and datetime.time objects, respectively.

Note

Since sqlite3 converters and adapters are registered globally, importing isqlite will affect the behavior of all sqlite3 connections in your application, even those that use the sqlite3 module directly.