isqlite documentation¶
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.
isqlite is an improved Python interface to SQLite. It has a more convenient API, support for schema diffing and migrations, and a command-line interface. It was written by Ian Fisher and the source code is available on GitHub.
Features¶
An improved Python API. * e.g.,
db.insert("people", {"name": "John Doe"})
instead ofcursor.execute("INSERT INTO people VALUES ('John Doe')")
. * Rows are returned asOrderedDict
objects instead of tuples. * Helper methods to simplify common patterns, e.g.get_or_insert
andinsert_many
.Database migrations with automatic schema diffing. * Automatically diff the database against a schema defined in Python and apply the results. * Or, manually alter the database schema from the command-line using commands like
isqlite drop-table
andisqlite rename-column
.A command-line interface.
Usage¶
isqlite includes a convenient Python API that greatly simplifies working with SQL:
from isqlite import Database
with Database(":memory:") as db:
# Insert a new row into the database.
pk = db.insert("employees", {"name": "John Doe", "age": 30})
# Retrieve the row as an OrderedDict.
person = db.get_by_pk("employees", pk)
print(person["name"], person["age"])
# Update the row.
db.update_by_pk("employees", pk, {"age": 35})
# Delete the row.
db.delete_by_pk("employees", pk)
# Filter rows with a query.
employees = db.select(
"employees",
where="name LIKE :name_pattern AND age > 40",
values={"name_pattern": "John%"},
)
# Use raw SQL if necessary.
pairs = db.sql(
"""
SELECT
teams.name, employees.name
FROM
employees
INNER JOIN
teams
ON
employees.team = teams.id
"""
)