dataclassdb [Early Alpha]
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 builderdb.SELECT, db.FROM, db.WHERE... db.<every sqlite statement and function>:- Appends the statement/function. Example:
db.SELECT("*").FROM(FOO)
- Appends the statement/function. Example:
db.Abs(...), db.Round(...), db.Rtrim(...): appends the function with its arguments:db.Abs(10)=abs(10)
Overview
- Uses
@dataclassclasses to generate and interact with database files usingsqlite3. 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
@dataclassfields' 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
pythonobject. Notablysets are not json encodable. Pickle is not safe and should be used with caution**. - Any user defined Codec
- Basic Types:
- Constraints
such as
PRIMARY KEYorUNIQUEcan be added astyping.Annotatedmetadata 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: Maps dataclass class into a sqlite3 table.
- QueryBuilder: Specialized StringBuilder with methods for each sqlite3 keywords and functions. Note that DataclassDb inherits from QueryBuilder.
- DbEngine:
- Codec: Protocol
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.
- Pickle is unsafe (bad actors can run arbitrary code on decode.)
- This data cannot be read without decoding.
- 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 buildersquery.SELECTor as standalone variablesdb.SELECT. See StatementBuilder or SQL enum - Similarly, all SQL functions, such as
count,sum,group_concatare available as chainable functionsquery.SELECT.Count("type")or as standalone functionsdb.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")
qb.SELECT: Calls the propertySELECTwhich adds"SELECT"to the string. It returns self.qb.SELECT("a", "b")calls__call__function instead ofSELECTfunction.
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 statebrnewlinelpar"("rpar")"rpar")"comma","execute(*args): Executes the command with the provided args replacing the placeholders.
Created By
Stuart (@stuarts_art)
- Software dev that now does furry art instead.
- 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.2.3
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| dataclassdb-0.2.3.tar.gz | 37.9 kB | Without provenance | Details for dataclassdb-0.2.3.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| dataclassdb-0.2.3-py3-none-any.whl | Python 3 | none | any | Details for dataclassdb-0.2.3-py3-none-any.whl |
Total release size: 66.0 kB
Release files / dataclassdb-0.2.3.tar.gz
| Download URL | dataclassdb-0.2.3.tar.gz |
|---|---|
| Size | 37.9 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
8ccfebaf199955f929b4e5a3263dc50f7b6df2e8697d519a28c40da932d9b6ff
|
|
BLAKE2b-256 checksum How to use checksums |
294000f0ac3a888a04f58586d0ec1827f7eb20d616539039493719801283a766
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.12.9 {"installer":{"name":"uv","version":"0.12.9","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.2.3-py3-none-any.whl
| Download URL | dataclassdb-0.2.3-py3-none-any.whl |
|---|---|
| Size | 28.1 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
0ce300923de40ff422d5c93b696c209935b14118a7eec03bcebe4dd0d1b14c2b
|
|
BLAKE2b-256 checksum How to use checksums |
96caadc227e5b21d22c719ef26cd3509e9677342ac9dd8b4ddba5a3473a21857
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.12.9 {"installer":{"name":"uv","version":"0.12.9","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.2.3
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.