Skip to main content

dataclassdb [Early Alpha]

Downloads

This module provides a lightweight sqlite3 ORM for dataclasses.

Installation

To install dataclassdb, simply use 'pip:

pip install dataclassdb

Requirements

Minimum Python version supported by dataclassdb is 3.11. The only dependency is dacite.

Quick Start

from dataclassdb import DataclassDb
from types import Annotated
from dataclasses import dataclass, Annotated

@dataclass
class Foo:
    id: Annotated[int, "PRIMARY KEY"]
    bar: str
    baz: list[int]

with DataclassDb(Foo, "example.db") as db:
    # Insert and get
    db.insert(Foo(0, "x", [1, 2, 3]))
    foo = db.get(0)

    # Arbitrary SQL
    db.SELECT("bar").FROM(FOO).WHERE("id").eq("?")
    selection = db.execute_one(0) # Replaces ? with 0

There are four ways to call get

Description get __getitem__
By key db.get(0) db[0]
By key(s) db.get((0)) db[(0)]
By dict db.get({"id": 0}) db[{"id": 0}]
By keyword arg db.get(id = 0)

Basics

  • db.get(...)
  • db.get_all(...)
  • db.insert(...)
  • db.insert_all(...)
  • db.execute()
  • db.execute_one()
  • Query builder methods
    • db.add: Appends arbitrary data to string builder
    • db.SELECT, db.FROM, db.WHERE... db.<every sqlite statement and function>:
      • Appends the statement/function. Example: db.SELECT("*").FROM(FOO)
    • db.Abs(...), db.Round(...), db.Rtrim(...): appends the function with its arguments:
      • db.Abs(10) = abs(10)

Overview

  • Uses @dataclass classes to generate and interact with database files using sqlite3. No "Model" super class required.
  • SQL table columns are inferred by the dataclass field annotation.
  • Codecs(Encoder/Decoder) translate Python to SQL and back.
  • Build in QueryBuilder with every SQL statement and function for easy query writing
  • Built in DbEngine context manager for managing the db connection, creating tables, and executing queries.

Deeper Dive

  • Each @dataclass fields' SQL type is inferred by the python type or by override. By default, undefined mapping are set to "TEXT".
  • A Codec (Encoder/Decoder) is used to translate between python and SQL. The following are supported:
    • Basic Types: int, str, float, bool
    • Enums: Enum, StrEnum
    • Dates: datetime -> INTEGER (unix time), datetime -> TEXT (ISO 8601)
    • Json: list, dict, @dataclass (If all elements are json encodable)
    • Pickle (Bytes): Any python object. Notably sets are not json encodable. Pickle is not safe and should be used with caution**.
    • Any user defined Codec
  • Constraints such as PRIMARY KEY or UNIQUE can be added as typing.Annotated metadata without affecting the base type.
  • Dataclass field defaults are encoded
  • "NOT NULL" field status is inferred by the

Column type is inferred by the python type and SQL type.

  • The column type is inferred by the python type but can be overridden as the first annotation.
  • This package does not attempt to abstract away SQL behavior. It's simply a lightweight wrapper around sqlite3.

Motivation

1. Easier to write than plaintext

  • All sqlite keywords and functions are supported
  • Queries can be written written without filler words.
  • Lists and args are handled how you'd expect them to be handled.

2. Easier to write than SqlAlchemy, SqlModel, and other more complex abstractions

  • No required imports QueryBuilder
  • Minimal package specific syntax or abstractions to learn

3. Abstracts away Non-SQL, sqlite3, actions

  • Creating connection
  • Executing the query
  • Returning the result as a list or dictionary

Features

DataclassDb

Column Definition

col_name: Annotated(Type, ? [SQL Type], *[Constraints], [Codec])
  • Type: The python type for the dataclass field
  • SQL Type: Override the default mapped SQL type (see next section)
  • Constraints: SQL constraints as text
  • Codec: Overrides the default codec. Any object that is a Codec (has an encode and decode method)

Examples:

Python Field Sqlite Col Definition
id: Annotated[int, "PRIMARY KEY"] id INTEGER PRIMARY KEY NOT NULL
username: Annotated[str, "UNIQUE"] username TEXT UNIQUE NOT NULL
email: str = "" email TEXT NOT NULL DEFAULT ""

Python type to SQL type resolution

Because sqlite has a limited amount of supported types: (INTEGER, TEXT, REAL, NUMERICAL, BLOB), we must map between python types and SQL types.

The "easiest" way to do this is by converting our python object into binary using pickle and storing it as a BLOB. However, there are a few issues with this approach.

  1. Pickle is unsafe (bad actors can run arbitrary code on decode.)
  2. This data cannot be read without decoding.
  3. This data cannot be used in queries without decoding.

This package approaches this issue by using Codecs (Encoder/Decoder) to handle the python -> sqlite -> python conversions. If no SQL type is declared in the Annotated column definition, first checks the below mapping.

If no mapping is found, the column is assumed to be json encodable. This setting can be overridden by either providing a custom Codec or setting the type to "BLOB".

SQL Type python type Codec
TEXT datetime DatetimeTextCodec
TEXT str IdentityCodec
TEXT default JsonCodec
INTEGER datetime DatetimeIntCodec
INTEGER bool BoolCodec
INTEGER int IdentityCodec
REAL float IdentityCodec
BLOB @dataclass DataclassPickleCodec
BLOB default PickleCodec

Query Builder

qb = QueryBuilder()            # str(qb)
qb.SELECT                      # = SELECT
qb("a", "b").Group_concat("c") # = SELECT a b group_concat(c)
  • All SQL statement keywords, such as SELECT, FROM, etc. are available as chainable properties of Query builders query.SELECT or as standalone variables db.SELECT. See StatementBuilder or SQL enum
  • Similarly, all SQL functions, such as count, sum, group_concat are available as chainable functions query.SELECT.Count("type") or as standalone functions db.Count("type"). See QueryBuilder or SQL_FUNC enum
  • QueryBuilders __call__ adds provided arguments to the string as a list, with optional quote and parenthesis options.

QueryBuilder is a fancy SQL flavored String Builder which the following goals:

Convention breaking weirdness

SQL statements leverage a totally legal but questionable hack. Each statement is a @property that returns self. The __call__ handles the case where the object is called directly. Note that SQL functions are NOT properties.

So to for the Query qb.SELECT("a","b")

  1. qb.SELECT: Calls the property SELECT which adds "SELECT" to the string. It returns self.
  2. qb.SELECT("a", "b") calls __call__ function instead of SELECT function.

Comparing QueryBuilder, plain sqlite3 and sqlAlchemy

QueryBuilder

query = QueryBuilder("example.db").
query.SELECT("name").FROM("sqlite_master").WHERE("name='spam'")
result = query.execute()

Sqlite3

#sqlite3
con = sqlite3.connect("example.db")
cur = con.cursor()
res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")

sqlAlchemy

engine = create_engine("sqlite:///example.db")
with engine.connect() as conn:
    query = text("SELECT name FROM sqlite_master WHERE name='spam'")
    res = conn.execute(query)

Bonus functions

  • placeholders(n) adds string "(? ... n)"
  • show() prints the query at its current state
  • br newline
  • lpar "("
  • rpar ")"
  • rpar ")"
  • comma ","
  • execute(*args): Executes the command with the provided args replacing the placeholders.

Created By

Stuart (@stuarts_art)

  • Washed dev who now makes furry art
  • I branched this out of ArtRefSync, a pure python desktop ui to sync reference art from SFW and NSFW image boards.
    • It uses client provided api keys to ethically get data while respecting usage rules
    • User defined black list to avoid the scourge of AI art
    • Is not using excessive ram to spy on you (plus memory is expensive)

Metadata

Release files for dataclassdb 0.1.8

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for dataclassdb 0.1.8
File Size Uploaded Provenance Actions
dataclassdb-0.1.8.tar.gz 36.7 kB Without provenance Details for dataclassdb-0.1.8.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for dataclassdb 0.1.8
File Interpreter ABI Platform Actions
dataclassdb-0.1.8-py3-none-any.whl Python 3 none any Details for dataclassdb-0.1.8-py3-none-any.whl

Total release size: 64.2 kB

Release files / dataclassdb-0.1.8.tar.gz

Download URL dataclassdb-0.1.8.tar.gz
Size 36.7 kB
Tags Source
SHA-256 checksum
How to use checksums
337b7f3ae890ca6e4b722aed611b92e2dd60cde775cdd1b5ca538b9d93fea0cb
BLAKE2b-256 checksum
How to use checksums
18e0eed8b3aa1fe11b06066c7352c80e86e59705f8e953f4f8d926445a5ad3c8
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.11.32 {"installer":{"name":"uv","version":"0.11.32","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Release files / dataclassdb-0.1.8-py3-none-any.whl

Download URL dataclassdb-0.1.8-py3-none-any.whl
Size 27.5 kB
Tags Python 3
SHA-256 checksum
How to use checksums
daf8ffbd7382b364a6ba01d881e4e4279fdff60332daafc9cb2fe8c3bbdbf85c
BLAKE2b-256 checksum
How to use checksums
48d97828d18a88b5c65e8bf27f2def1cc916225a412051357aee86a4495b58e2
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via uv/0.11.32 {"installer":{"name":"uv","version":"0.11.32","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}

Security for dataclassdb 0.1.8

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page