sqlengine-lite 2.2.0

Cute sqlite3 wrapper for sql tables

• Sql-Engine
  • Features
  • Purpose
  • Installation
  • Env
• Quick Start
  • Table Declaration
  • Instantiation
  • Row insertion
  • Jupyter view
  • Select Query
  • Update Query
  • Delete Query
  • Transaction
  • Get Item
  • Custom Types
  • Csv Converter
• Full Documentation

Sql-Engine

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

Features

Abstracts SQL queries into tiny little methods like, insert, insert_many, upsert, and not so little and tiny query builders a-la select, delete, update, etc. Sql-Engine also provides bulk insertion and transaction methods, like insert_many and select.fetchmany_iterator. Methods can be executed in transaction mode thanks to transaction context manager.

Sql-Engine implements Jupyter integration and dynamic schema building. You can easily instantiate existing database table and view it in 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 paramerized with methods like select.custom() and where.custom().

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, you might look into Sql-Engine, as it uses only native python modules.

Installation

To install sqlengine, you can use pip:

pip install sqlengine-lite

Env

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

SQL_ENGINE_LOG_LEVEL=INFO

Quick Start

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 properties of your table's columns. They are:

Name of the table that will be used in queries. If omitted in inherited class declaration, then it will take the class name.

__tablename__ : Optional[str]

Column names of the table

__columns__ : list[str] 

Column types of the table

__types__ : list[SqlType | str] 

List of primary keys

__primary__ : list[str] 

Table Declaration

More details at Declaration.

from sqlengine import SqlTableMixin, Primary

# Helper constants for column names
ID = "ID"
Name = "Name"
Occupation = "Occupation"
Salary = "Salary"


class Employees(SqlTableMixin):

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

    # You may overwrite your insert methods for type consistency
    def insert(self, id : int, name : str, occupation : str, salary : float) -> None:
        return super().insert(id, name, occupation, salary)
    
    def upsert(self, id : int, name : str, occupation : str, salary : float) -> None:
        return super().upsert(id, name, occupation, salary)

Instantiation

More details at Instantiation.

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

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

Row insertion

# Insert one row

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

# Bulk insert multiple rows

employees_data = [
    (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)
]

table.insert_many(employees_data)

# Upsert one row

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

Jupyter view

# Inspect tables 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

More details at Statements.

# Query select and fetch

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)]

# Inspect query select 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()

Transaction

More details at Transaction.

# Operate within a transaction

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

Get Item

More details at Syntax Sugar.

# 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)]

Custom Types

More details at Custom Types.

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(1, "loft_1", DateTime.now(), None)
table

ReservationIndex

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

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")

Full Documentation

For detailed usage, API reference, and advanced examples, see the full documentation.