sqlengine-lite 2.2.2

Cute sqlite3 wrapper for sql tables

SqlEngine

My SqlEngine is a cute little wrapper for sqlite3 table manipulations without any third party dependencies.

Home Page | Installation | Quick Start | Documentation

Features

SqlEngine abstracts SQL queries into tiny little methods like insert, insert_many, upsert, and not so tiny (but still cute and little) query builders like select, delete and update. SqlEngine also provides bulk insertion with insert_many and transaction operations like select.fetchmany_iterator. Methods can be executed either in transaction mode (thanks to transaction context manager) or right on the spot.

SqlEngine implements Jupyter integration and dynamic schema building. You can easily instantiate existing database table and view it in a cute little html representation.

from sqlengine import schema

table = schema.table_from_database("temp/chinook.db", "Album")
table

Album

AlbumId	Title	ArtistId
1	For Those About To Rock We Salute You	1
2	Balls to the Wall	2
3	Restless and Wild	2
4	Let There Be Rock	1
5	Big Ones	3
6	Jagged Little Pill	4
7	Facelift	5
8	Warner 25 Anos	6
9	Plays Metallica By Four Cellos	7
10	Audioslave	8
... more rows ...

Note that GitHub's html rendering is simplified.

---

You can preview select statements before fetching data to your variables.

table = schema.table_from_database("temp/chinook.db", "Invoice")

table.select("InvoiceId", "CustomerId", "BillingAddress", "BillingCountry", "Total")\
    .where\
        .gte("Total", 2.0)\
    .then\
        .order_by("CustomerId")\
        .limit(10)

Invoice

InvoiceId	CustomerId	BillingAddress	BillingCountry	Total
98	1	Av. Brigadeiro Faria Lima, 2170	Brazil	3.98
121	1	Av. Brigadeiro Faria Lima, 2170	Brazil	3.96
143	1	Av. Brigadeiro Faria Lima, 2170	Brazil	5.94
327	1	Av. Brigadeiro Faria Lima, 2170	Brazil	13.86
382	1	Av. Brigadeiro Faria Lima, 2170	Brazil	8.91
12	2	Theodor-Heuss-Straße 34	Germany	13.86
67	2	Theodor-Heuss-Straße 34	Germany	8.91
219	2	Theodor-Heuss-Straße 34	Germany	3.96
241	2	Theodor-Heuss-Straße 34	Germany	5.94
99	3	1498 rue Bélanger	Canada	3.98

Purpose

It's a tiny little modern ORM-like that lets you prototype your databases locally with great flexibility. Also, it can be used in production apps to store and retrieve data, because all select, update, delete queries are parametrized. But it does not restrict you from using your own queries which might not be parameterized with methods like select.custom() and where.custom(). Flexibility is a go to for this library.

And last (but not least) is data inspection. If you need to quickly inspect existing .db file but don't want to install yet another heavy ORM with a lot of unused dependencies and features, you might look into SqlEngine, as it uses only native python modules, implements dynamic schema builder and has a good synergy with Jupyter Notebook.

Installation

To install SqlEngine, you can use pip:

pip install sqlengine-lite

Env

You can set environment variable for logging. By default it's WARNING.

SQL_ENGINE_LOG_LEVEL=INFO

Quick Start

Here lays everything you need to know to start working with SqlEngine. For detailed usage, API reference, and advanced examples, see the full documentation.

Table Declaration

All you have to do to create your own cute little table is to inherit SqlTableMixin class or to create your own schema and declare desired types of your table's columns.

from sqlengine import SqlTableMixin, Primary

class Employees(SqlTableMixin):

    ID         : Primary[int]
    Name       : str | None
    Occupation : str
    Salary     : float

More details at Declaration.

Instantiation

Create an instance of the table class with provided path to create or connect to. force_drop=True to overwrite existing table if it exists.

table = Employees("temp/data.db", force_drop=True)

More details at Instantiation.

Row insertion

Insert one row in a *args style.

table.insert(1, "John Doe", "Software Engineer", 75000.0)

Use kwargs mapping to insert/upsert one row

table.insert(2, salary=80000.0, name="Jane Smith", occupation="Data Scientist")

Bulk insert multiple rows.

employees_data = [
    (3, "Alice Johnson", "Product Manager", 90000.0),
    (4, "Bob Brown", "Project Manager", 78000.0),
    (5, "Charlie Davis", "UI/UX Designer", 65000.0),
    (6, "David Wilson", "DevOps Engineer", 82000.0),
    (7, "Eve Taylor", "Customer Support", 45000.0),
    (8, "Frank White", "Quality Assurance", 53000.0),
    (9, "Grace Hall", "Marketing Manager", 68000.0),
    (10, "Henry Lee", "Technical Writer", 52000.0)
]

table.insert_many(employees_data)

Upsert one row.

table.upsert(1, "Jane Doe", "Data Scientist", 80000.0)

Jupyter view

Inspect table in Jupyter Notebook.

table

Employees

ID	Name	Occupation	Salary
1	Jane Doe	Data Scientist	80000.0
2	Jane Smith	Data Scientist	80000.0
3	Alice Johnson	Product Manager	90000.0
4	Bob Brown	Project Manager	78000.0
5	Charlie Davis	UI/UX Designer	65000.0
6	David Wilson	DevOps Engineer	82000.0
7	Eve Taylor	Customer Support	45000.0
8	Frank White	Quality Assurance	53000.0
9	Grace Hall	Marketing Manager	68000.0
10	Henry Lee	Technical Writer	52000.0

Select Query

Helper constants for column names.

ID         = "ID"
Name       = "Name"
Occupation = "Occupation"
Salary     = "Salary"

Query select and fetch in one go.

table.select.where.between(ID, 3, 5).then.fetchall()

# ->
# [(3, 'Alice Johnson', 'Product Manager', 90000.0),
#  (4, 'Bob Brown', 'Project Manager', 78000.0),
#  (5, 'Charlie Davis', 'UI/UX Designer', 65000.0)]

Iterate over select statements.

with table.transaction():
    for id, occupation in table.select(ID, Occupation).where.gt(Salary, 70_000):
        print(id, occupation)

1 Data Scientist
3 Product Manager
4 Project Manager
6 DevOps Engineer

Inspect select query in Jupyter.

table.select(Name, Salary).where.lt(Salary, 70_000)

Employees

Name	Salary
Charlie Davis	65000.0
Eve Taylor	45000.0
Frank White	53000.0
Grace Hall	68000.0
Henry Lee	52000.0

Update Query

# equal to ...update.set(Salary, 50_000)...
table.update(Salary, 50_000).where.eq(Name, "Eve Taylor").then.execute()

Delete Query

table.delete.where.eq(ID, 5).then.execute()

More details at Statements.

Transaction

Operate within a transaction

with table.transaction():
    for row in employees_data:
        table.upsert(*row)

More details at Transaction.

Get Item

Fetch row by primary key.

table[9] # -> (9, 'Grace Hall', 'Marketing Manager', 68000.0)

Fetch slice by integer primary key.

table[4:10:2]

# -> 
# [(4, 'Bob Brown', 'Project Manager', 78000.0),
#  (6, 'David Wilson', 'DevOps Engineer', 82000.0),
#  (8, 'Frank White', 'Quality Assurance', 53000.0)]

More details at Syntax Sugar.

Custom Types

Declare your own non-native SQL type to be compatible with sqlite3.

from datetime import datetime
from sqlengine import SqlTableMixin, Primary

class DateTime(datetime):
    
    def to_sql(self) -> str:
        return self.strftime("%Y-%m-%d %H:%M")

    @classmethod
    def from_sql(cls, sql : bytes):
        return cls.fromisoformat(sql.decode('utf-8'))
    
    def __repr__(self):
        return f"DateTime({self.time})"


class ReservationIndex(SqlTableMixin):

    user_id : Primary[int]
    room_id : Primary[str]
    time_at : Primary[DateTime]
    user_name : str | None


table = ReservationIndex("temp/data.db")

table.insert(
    user_id=1, 
    room_id="loft_1", 
    time_at=DateTime.now()
)
table

ReservationIndex

user_id	room_id	time_at	user_name
1	loft_1	2026-05-29 21:29:00	None

More details at Custom Types.

Pure SQL

Use SQL queries directly.

with table.transaction(autocommit=False):
    table.conn.execute("DELETE FROM ReservationIndex WHERE user_id = 1;")
    table.commit()

Same as:

table.conn.execute("DELETE FROM ReservationIndex WHERE user_id = 1;")

table

ReservationIndex

user_id	room_id	time_at	user_name

Csv Converter

Save table to csv.

from sqlengine.utils import to_csv

to_csv(table, "temp/table.csv")

Save query result to csv.

to_csv(table.select.where.gt(Salary, 70_000), "temp/query.csv")

Stream big tables or query results to csv.

with table.transaction():
    to_csv(table, "temp/query.csv", stream_batch_size=1000)

Pandas-like Converter

Convert tables or query results to pandas DataFrame in one shot.

import pandas as pd
from sqlengine.utils import to_dicts

df = pd.DataFrame(to_dicts(table))
df.set_index("ID", inplace=True)

Or stream them via generator.

import pandas as pd
from sqlengine.utils import to_dicts_stream

df = pd.DataFrame(columns=table.columns)

with table.transaction():
    for batch in to_dicts_stream(table, batch_size=1000):
        df = pd.concat([df, pd.DataFrame(batch)], axis=0)

df.set_index("ID", inplace=True)

Contributions

Your impact is welcome. Install module from source if you want to contribute:

git clone https://github.com/suffermuffin/SQL-Engine.git
cd SQL-Engine

Use uv to sync dependencies and checkout to your new branch:

uv sync
git checkout -b "<your_feature_or_fix_name>"

Don't forget to run tests after the implementation:

uv run python -m unittest discover -s tests

And update api documentation with your docstrings:

pydoc-markdown

License

This project is licensed under the terms of the MIT license.