About the Project
SQLiteFrame is an SQLite ORM for python, designed to be as lightweight, intuitive, and simple to use as possible.
It is designed to closely mimic SQL syntax whilst remaining as pythonic as possible to save developers valuable time (and brain cells) when interacting with SQLite databases, by building reusable SQLite query objects using method-chaining, and abstracting away SQLite's connection and cursor system with a single context manager.
(back to top)
Getting Started
SQLiteFrame is available on PyPI. Simply install the package into your project environment with PIP:
pip install SQLiteFrame
To install specific previous versions, take a look at the version history, locate the version tag (vX.Y.Z)
, and run:
pip install SQLiteFrame==X.Y.Z
SQLiteFrame has ZERO external dependencies - it uses only the standard library's sqlite3
to execute SQLite commands.
(back to top)
Basic Usage
Creating a table
To create a table, use the template below. This will automatically run the CreateTable SQLite command for you:
from sqliteframe import Database, table, String, Integer, Boolean
from pathlib import Path
database = Database(Path("<database_path>.db"), output=False)
@table(database)
class TableName:
primary_key_field = String(primary_key=True)
second_column = Integer
third_column = Boolean(nullable=True)
fourth_column = String(default="This is a default value.")
Managing Connections
Before you can interact with the tables you create, you must first connect to the database:
with database.connection(commit=True)
...
Inserting Data
To insert data into an existing table, use the following query template:
insert_statement = TableName.insert_into({
TableName.primary_key_field: "PrimaryKey1",
TableName.second_column: 1_000,
TableName.third_column: True
})
with database.connection():
insert_statement.execute()
Fetching / Selecting Data
Fetching / selecting data from an existing table with pre-inserted data is done as below:
select_statement = TableName.select(TableName.second_column, TableName.third_column)
with database.connection():
select_statement.execute()
Linking Tables (Foreign Keys)
Linking tables can be done with Foreign Keys in SQLiteFrame:
from sqliteframe import Database, table, String, Integer, Boolean, ForeignKey
from pathlib import Path
database = Database(Path("<database_path>.db"), output=False)
@table(database)
class FirstTableName:
primary_key_field = String(primary_key=True)
second_column = Integer
third_column = Boolean(nullable=True)
@table(database)
class SecondTableName:
primary_key_field = Integer(primary_key=True)
second_column = Boolean(nullable=True)
third_column = String
foreign_key_column = ForeignKey(FirstTableName)
Complex Data Fetching / Selection
To build more complex select queries, you can use join
, where
, and order by
:
from sqliteframe import JoinTypes, OrderTypes
select_statement = FirstTableName.select(SecondTableName.second_column, FirstTableName.third_column).join(
SecondTableName, SecondTableName.foreign_key_column == FirstTableName.primary_key_field, join_type=JoinTypes.LEFT
).where(
SecondTableName.third_column == "Criteria"
).order_by(
FirstTableName.second_column, (OrderTypes.DESCENDING, OrderTypes.NULLS_FIRST)
)
with database.connection():
select_statement.execute()
Editing Data
To edit pre-inserted data, a set
query can be used:
set_statement = FirstTableName.set({
TableName.second_column: 10_000,
TableName.third_column: None
}).where(
(Person.primary_key_column == "PrimaryKey1") & (Person.second_column > 500)
)
with database.connection():
set_statement.execute()
NOTE: The where clause can be emitted from this statement, but this would update every record in the target table.
Deleting Data
To delete pre-inserted table data, use the delete_from
query:
delete_statement = TableName.delete_from().where(
(TableName.second_column <= 250)
)
with database.connection():
delete_statement.execute()
NOTE: The where clause can be emitted from this statement, but this would delete every record in the target table.
Dropping Tables
Dropping tables does not delete the table reference from python - just in the SQL. Tables which others tables depend on / reference cannot be deleted by default to maintain referential integrity. This behaviour can be changed when defining the referencing foreign key column.
To entirely drop (delete) an existing table, use the drop_table
statement:
with database.connection():
SecondTableName.drop_table().execute()
FirstTableName.drop_table().execute()
For more examples and specific detail, please refer to the Documentation
(back to top)
License
Distributed under the MIT License. See LICENSE for more information.
(back to top)