sqlengine-lite 2.1.1

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
  • 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", "Invoice")
table

Invoice

InvoiceId	CustomerId	InvoiceDate	BillingAddress	BillingCity	BillingState	BillingCountry	BillingPostalCode	Total
1	2	2021-01-01 00:00:00	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	1.98
2	4	2021-01-02 00:00:00	Ullevålsveien 14	Oslo	None	Norway	0171	3.96
3	8	2021-01-03 00:00:00	Grétrystraat 63	Brussels	None	Belgium	1000	5.94
4	14	2021-01-06 00:00:00	8210 111 ST NW	Edmonton	AB	Canada	T6G 2C7	8.91
5	23	2021-01-11 00:00:00	69 Salem Street	Boston	MA	USA	2113	13.86
6	37	2021-01-19 00:00:00	Berger Straße 10	Frankfurt	None	Germany	60316	0.99
7	38	2021-02-01 00:00:00	Barbarossastraße 19	Berlin	None	Germany	10779	1.98
8	40	2021-02-01 00:00:00	8, Rue Hanovre	Paris	None	France	75002	1.98
9	42	2021-02-02 00:00:00	9, Place Louis Barthou	Bordeaux	None	France	33000	3.96
10	46	2021-02-03 00:00:00	3 Chatham Street	Dublin	Dublin	Ireland	None	5.94
... more rows ...

---

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

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

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


class Employees(SqlTableMixin):

    __columns__   = [ID, Name, Occupation, Salary]
    __types__     = [int, str, str, float]
    __primary__   = [ID]

    # 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

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

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

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.