pydantic_sqlite


Namepydantic_sqlite JSON
Version 0.4.0 PyPI version JSON
download
home_pagehttps://github.com/Phil997/pydantic-sqlite
SummarySimple package for storing pydantic BaseModels in an in-memory SQLite database.
upload_time2025-07-12 13:30:16
maintainerNone
docs_urlNone
authorYour Name
requires_python<4.0.0,>=3.8.1
licenseMIT
keywords pydantic sqlite-utils sqlite3
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # pydantic-sqlite  <!-- omit in toc -->

![Python](https://img.shields.io/badge/python-3.8%20|%203.9%20|%203.10%20|%203.11%20|%203.12%20|%203.13-blue?logo=python&logoColor=white)
![License: MIT](https://img.shields.io/badge/License-MIT-brightgreen.svg)
[![codecov](https://codecov.io/github/Phil997/pydantic-sqlite/graph/badge.svg?token=MCCXX7XF9V)](https://codecov.io/github/Phil997/pydantic-sqlite)


A lightweight package for storing `pydantic` `BaseModel` in a `SQLite` database.

You can store any `BaseModel` instance directly in the database, and when querying a table, you receive fully reconstructed `BaseModel` objects — ready to use, just like your originals.

- [Installation](#installation)
- [Usage](#usage)
  - [Basic Example](#basic-example)
  - [Nested Example](#nested-example)
  - [Nested Example without Foreign Table](#nested-example-without-foreign-table)
  - [Nested with different primary keys](#nested-with-different-primary-keys)
  - [FailSafeDataBase](#failsafedatabase)

## Installation

```
pip install pydantic-sqlite
```

## Usage

### Basic Example
Create two instances of the class `Person` and store them in the 'Test' table of the database. Then, retrieve and display all records from the 'Test' table through iteration. Per default DataBase uses `uuid` as the primary-key in tha table.

```python
from pydantic_sqlite import DataBase
from pydantic import BaseModel

class Person(BaseModel):
    uuid: str
    name: str
    age: int

# Create two Person instances
person1 = Person(uuid="abc", name="Yoda", age=900)
person2 = Person(uuid="def", name="Leia", age=23)

db = DataBase()
db.add("Test", person1)
db.add("Test", person2)

for x in db("Test"):
    assert isinstance(x, Person)
    print(x)

#>>> uuid='abc' name='Yoda' age=900
#>>> uuid='def' name='Leia' age=23
```

### Nested Example

Instantiate an address object and two person objects, with each person having an attribute of the address type. Upon adding the person to the database, the database requires the foreign table 'Adresses' to establish the foreign key relationship. Consequently, when iterating over the 'Persons' table, it enables the reconstruction of complete 'Person' objects, each possessing an attribute of the 'Address' type.

```python
from pydantic_sqlite import DataBase
from pydantic import BaseModel

class Address(BaseModel):
    uuid: str
    town: str
    street: str
    number: int

class Person(BaseModel):
    uuid: str
    name: str
    address: Address

address = Address(uuid="abc", town="Mos Espa", street="Dustwind Street", number=67)
person1 = Person(uuid="def", name="Anakin", address=address)

db = DataBase()
db.add("Adresses", address)
db.add("Persons", person1, foreign_tables={'address': 'Adresses'})

for x in db("Adresses"):
    assert isinstance(x, Address)
    print(x)

for y in db("Persons"):
    assert isinstance(y, Person)
    print(y)

#>>> uuid='abc' town='Mos Espa' street='Dustwind Street' number=67
#>>> uuid='def' name='Anakin' address=Address(uuid='abc', town='Berlin', street='Dustwind Street', number=67)
```

### Nested Example without Foreign Table
If you prefer to avoid an extra table, you have the option to store an object of the BaseModel type differently.

In this scenario, the address object isn't stored in a separate table but rather as a string within a column of the 'Persons' table. To achieve this, the Address class includes the SQConfig class, which must define the convert method, specifying how the object should be stored in SQLite. Upon retrieval, an Address object is reconstructed from the stored string using a field_validator.

```python
from uuid import uuid4
from pydantic import BaseModel, field_validator
from pydantic_sqlite import DataBase

class Address(BaseModel):
    town: str
    street: str

    class SQConfig:
        special_insert: bool = True

        def convert(obj):
            return f"{obj.town},{obj.street}"

class Person(BaseModel):
    uuid: str
    name: str
    address: Address

    @field_validator('address', mode="before")
    def validate(cls, v):
        if isinstance(v, Address):
            return v
        town, street = v.split(',')
        return Address(town=town, street=street)

address = Address(town="Berlin", street="Bahnhofstraße 67")
person1 = Person(uuid=str(uuid4()), name="Bob", address=address)
person2 = Person(uuid=str(uuid4()), name="Alice", address=address)

db = DataBase()
db.add("Persons", person1)
db.add("Persons", person2)

for y in db("Persons"):
    assert isinstance(y, Person)
    print(y)

#>>> uuid='...' name='Bob' address=Address(town='Berlin', street='Bahnhofstraße 67')
#>>> uuid='...' name='Alice' address=Address(town='Berlin', street='Bahnhofstraße 67')

for y in db("Persons", where='name= :name', where_args={'name': 'Alice'}):
    assert isinstance(y, Person)
    print(y)
#>>> uuid='...' name='Alice' address=Address(town='Berlin', street='Bahnhofstraße 67')
```

### Nested with different primary keys

This example demonstrates how to handle nested models where each table uses a different primary key, and how to manage foreign key relationships between them. Here, a `CarRegistration` contains a `Person` and a `Car`, and the `Car` itself contains a list of `Wheel` objects. Each model has its own unique primary key, and the relationships are established using the `foreign_tables` argument.

```python
from typing import List
from pydantic import BaseModel
from pydantic_sqlite import DataBase

class Person(BaseModel):
    uuid: str
    name: str

class Wheel(BaseModel):
    batch_id: str
    size: int

class Car(BaseModel):
    series_number: str
    model: str
    wheels: List[Wheel]

class CarRegistration(BaseModel):
    id: str
    person: Person
    car: Car

wheels = [Wheel(batch_id=f"P_{i}", size=16) for i in range(4)]
car = Car(series_number="1234", model="Volkswagen Golf", wheels=wheels)
person = Person(uuid="abcd", name="John Doe")
registration = CarRegistration(car=car, person=person, id="fffff")

db = DataBase()

for wheel in wheels:
    db.add("Wheels", wheel, pk='batch_id')
db.add("Cars", car, pk='series_number', foreign_tables={"wheels": "Wheels"})
db.add("Persons", person, pk='uuid')
db.add("CarRegistrations", registration, pk='id', foreign_tables={"car": "Cars", "person": "Persons"})

print(next(db("Persons")))
print(next(db("Cars")))
print(next(db("CarRegistrations")))

#>>> uuid='abcd' name='John Doe'
#>>> series_number='1234' model='Volkswagen Golf' wheels=[Wheel(batch_id='P_0', size=16), Wheel(batch_id='P_1', size=16), Wheel(batch_id='P_2', size=16), Wheel(batch_id='P_3', size=16)]
#>>> id='fffff' person=Person(uuid='abcd', name='John Doe') car=Car(series_number='1234', model='Volkswagen Golf', wheels=[Wheel(batch_id='P_0', size=16), Wheel(batch_id='P_1', size=16), Wheel(batch_id='P_2', size=16), Wheel(batch_id='P_3', size=16)])

```

### FailSafeDataBase
The `FailSafeDataBase` serves as a context manager wrapper for the `DataBase`. The database returned by the context manager functions identically to those in previous examples.

However, the handler offers an added benefit: in case of an exception, it automatically saves a database snapshot with the latest values as `<<dbname>_snapshot.db` (by default). If such a file already exists, the filename is iteratively incremented (e.g., `<<dbname>_snapshot(1).db`).

You can also configure the snapshot suffix using the `snapshot_suffix` argument in the constructor.

For instance, running this example generates two files: `humans.db` and `humans_snapshot.db`. Executing the script again, a snapshot file called `humans_snapshot(1).db` will be created.

```python
from uuid import uuid4
from pydantic import BaseModel
from pydantic_sqlite import FailSafeDataBase

class Person(BaseModel):
    uuid: str
    name: str
    age: int

with FailSafeDataBase("humans", snapshot_suffix="_snapshot.db") as db:
    test1 = Person(uuid=str(uuid4()), name="Bob", age=12)
    db.add("Test", test1)
    for x in db("Test"):
        assert issubclass(x.__class__, BaseModel)
        assert isinstance(x, Person)
        print(x)
    db.save("hello_world.db")

    raise Exception("test")  # simulate an Exception which results in a new snapshot file
```
            

Raw data

            {
    "_id": null,
    "home_page": "https://github.com/Phil997/pydantic-sqlite",
    "name": "pydantic_sqlite",
    "maintainer": null,
    "docs_url": null,
    "requires_python": "<4.0.0,>=3.8.1",
    "maintainer_email": null,
    "keywords": "pydantic, sqlite-utils, sqlite3",
    "author": "Your Name",
    "author_email": "you@example.com",
    "download_url": "https://files.pythonhosted.org/packages/85/4b/0281d47a9ce1a56f6a70b449ee5f75a4e2768dd1d057fee88c998662ab09/pydantic_sqlite-0.4.0.tar.gz",
    "platform": null,
    "description": "# pydantic-sqlite  <!-- omit in toc -->\n\n![Python](https://img.shields.io/badge/python-3.8%20|%203.9%20|%203.10%20|%203.11%20|%203.12%20|%203.13-blue?logo=python&logoColor=white)\n![License: MIT](https://img.shields.io/badge/License-MIT-brightgreen.svg)\n[![codecov](https://codecov.io/github/Phil997/pydantic-sqlite/graph/badge.svg?token=MCCXX7XF9V)](https://codecov.io/github/Phil997/pydantic-sqlite)\n\n\nA lightweight package for storing `pydantic` `BaseModel` in a `SQLite` database.\n\nYou can store any `BaseModel` instance directly in the database, and when querying a table, you receive fully reconstructed `BaseModel` objects \u2014 ready to use, just like your originals.\n\n- [Installation](#installation)\n- [Usage](#usage)\n  - [Basic Example](#basic-example)\n  - [Nested Example](#nested-example)\n  - [Nested Example without Foreign Table](#nested-example-without-foreign-table)\n  - [Nested with different primary keys](#nested-with-different-primary-keys)\n  - [FailSafeDataBase](#failsafedatabase)\n\n## Installation\n\n```\npip install pydantic-sqlite\n```\n\n## Usage\n\n### Basic Example\nCreate two instances of the class `Person` and store them in the 'Test' table of the database. Then, retrieve and display all records from the 'Test' table through iteration. Per default DataBase uses `uuid` as the primary-key in tha table.\n\n```python\nfrom pydantic_sqlite import DataBase\nfrom pydantic import BaseModel\n\nclass Person(BaseModel):\n    uuid: str\n    name: str\n    age: int\n\n# Create two Person instances\nperson1 = Person(uuid=\"abc\", name=\"Yoda\", age=900)\nperson2 = Person(uuid=\"def\", name=\"Leia\", age=23)\n\ndb = DataBase()\ndb.add(\"Test\", person1)\ndb.add(\"Test\", person2)\n\nfor x in db(\"Test\"):\n    assert isinstance(x, Person)\n    print(x)\n\n#>>> uuid='abc' name='Yoda' age=900\n#>>> uuid='def' name='Leia' age=23\n```\n\n### Nested Example\n\nInstantiate an address object and two person objects, with each person having an attribute of the address type. Upon adding the person to the database, the database requires the foreign table 'Adresses' to establish the foreign key relationship. Consequently, when iterating over the 'Persons' table, it enables the reconstruction of complete 'Person' objects, each possessing an attribute of the 'Address' type.\n\n```python\nfrom pydantic_sqlite import DataBase\nfrom pydantic import BaseModel\n\nclass Address(BaseModel):\n    uuid: str\n    town: str\n    street: str\n    number: int\n\nclass Person(BaseModel):\n    uuid: str\n    name: str\n    address: Address\n\naddress = Address(uuid=\"abc\", town=\"Mos Espa\", street=\"Dustwind Street\", number=67)\nperson1 = Person(uuid=\"def\", name=\"Anakin\", address=address)\n\ndb = DataBase()\ndb.add(\"Adresses\", address)\ndb.add(\"Persons\", person1, foreign_tables={'address': 'Adresses'})\n\nfor x in db(\"Adresses\"):\n    assert isinstance(x, Address)\n    print(x)\n\nfor y in db(\"Persons\"):\n    assert isinstance(y, Person)\n    print(y)\n\n#>>> uuid='abc' town='Mos Espa' street='Dustwind Street' number=67\n#>>> uuid='def' name='Anakin' address=Address(uuid='abc', town='Berlin', street='Dustwind Street', number=67)\n```\n\n### Nested Example without Foreign Table\nIf you prefer to avoid an extra table, you have the option to store an object of the BaseModel type differently.\n\nIn this scenario, the address object isn't stored in a separate table but rather as a string within a column of the 'Persons' table. To achieve this, the Address class includes the SQConfig class, which must define the convert method, specifying how the object should be stored in SQLite. Upon retrieval, an Address object is reconstructed from the stored string using a field_validator.\n\n```python\nfrom uuid import uuid4\nfrom pydantic import BaseModel, field_validator\nfrom pydantic_sqlite import DataBase\n\nclass Address(BaseModel):\n    town: str\n    street: str\n\n    class SQConfig:\n        special_insert: bool = True\n\n        def convert(obj):\n            return f\"{obj.town},{obj.street}\"\n\nclass Person(BaseModel):\n    uuid: str\n    name: str\n    address: Address\n\n    @field_validator('address', mode=\"before\")\n    def validate(cls, v):\n        if isinstance(v, Address):\n            return v\n        town, street = v.split(',')\n        return Address(town=town, street=street)\n\naddress = Address(town=\"Berlin\", street=\"Bahnhofstra\u00dfe 67\")\nperson1 = Person(uuid=str(uuid4()), name=\"Bob\", address=address)\nperson2 = Person(uuid=str(uuid4()), name=\"Alice\", address=address)\n\ndb = DataBase()\ndb.add(\"Persons\", person1)\ndb.add(\"Persons\", person2)\n\nfor y in db(\"Persons\"):\n    assert isinstance(y, Person)\n    print(y)\n\n#>>> uuid='...' name='Bob' address=Address(town='Berlin', street='Bahnhofstra\u00dfe 67')\n#>>> uuid='...' name='Alice' address=Address(town='Berlin', street='Bahnhofstra\u00dfe 67')\n\nfor y in db(\"Persons\", where='name= :name', where_args={'name': 'Alice'}):\n    assert isinstance(y, Person)\n    print(y)\n#>>> uuid='...' name='Alice' address=Address(town='Berlin', street='Bahnhofstra\u00dfe 67')\n```\n\n### Nested with different primary keys\n\nThis example demonstrates how to handle nested models where each table uses a different primary key, and how to manage foreign key relationships between them. Here, a `CarRegistration` contains a `Person` and a `Car`, and the `Car` itself contains a list of `Wheel` objects. Each model has its own unique primary key, and the relationships are established using the `foreign_tables` argument.\n\n```python\nfrom typing import List\nfrom pydantic import BaseModel\nfrom pydantic_sqlite import DataBase\n\nclass Person(BaseModel):\n    uuid: str\n    name: str\n\nclass Wheel(BaseModel):\n    batch_id: str\n    size: int\n\nclass Car(BaseModel):\n    series_number: str\n    model: str\n    wheels: List[Wheel]\n\nclass CarRegistration(BaseModel):\n    id: str\n    person: Person\n    car: Car\n\nwheels = [Wheel(batch_id=f\"P_{i}\", size=16) for i in range(4)]\ncar = Car(series_number=\"1234\", model=\"Volkswagen Golf\", wheels=wheels)\nperson = Person(uuid=\"abcd\", name=\"John Doe\")\nregistration = CarRegistration(car=car, person=person, id=\"fffff\")\n\ndb = DataBase()\n\nfor wheel in wheels:\n    db.add(\"Wheels\", wheel, pk='batch_id')\ndb.add(\"Cars\", car, pk='series_number', foreign_tables={\"wheels\": \"Wheels\"})\ndb.add(\"Persons\", person, pk='uuid')\ndb.add(\"CarRegistrations\", registration, pk='id', foreign_tables={\"car\": \"Cars\", \"person\": \"Persons\"})\n\nprint(next(db(\"Persons\")))\nprint(next(db(\"Cars\")))\nprint(next(db(\"CarRegistrations\")))\n\n#>>> uuid='abcd' name='John Doe'\n#>>> series_number='1234' model='Volkswagen Golf' wheels=[Wheel(batch_id='P_0', size=16), Wheel(batch_id='P_1', size=16), Wheel(batch_id='P_2', size=16), Wheel(batch_id='P_3', size=16)]\n#>>> id='fffff' person=Person(uuid='abcd', name='John Doe') car=Car(series_number='1234', model='Volkswagen Golf', wheels=[Wheel(batch_id='P_0', size=16), Wheel(batch_id='P_1', size=16), Wheel(batch_id='P_2', size=16), Wheel(batch_id='P_3', size=16)])\n\n```\n\n### FailSafeDataBase\nThe `FailSafeDataBase` serves as a context manager wrapper for the `DataBase`. The database returned by the context manager functions identically to those in previous examples.\n\nHowever, the handler offers an added benefit: in case of an exception, it automatically saves a database snapshot with the latest values as `<<dbname>_snapshot.db` (by default). If such a file already exists, the filename is iteratively incremented (e.g., `<<dbname>_snapshot(1).db`).\n\nYou can also configure the snapshot suffix using the `snapshot_suffix` argument in the constructor.\n\nFor instance, running this example generates two files: `humans.db` and `humans_snapshot.db`. Executing the script again, a snapshot file called `humans_snapshot(1).db` will be created.\n\n```python\nfrom uuid import uuid4\nfrom pydantic import BaseModel\nfrom pydantic_sqlite import FailSafeDataBase\n\nclass Person(BaseModel):\n    uuid: str\n    name: str\n    age: int\n\nwith FailSafeDataBase(\"humans\", snapshot_suffix=\"_snapshot.db\") as db:\n    test1 = Person(uuid=str(uuid4()), name=\"Bob\", age=12)\n    db.add(\"Test\", test1)\n    for x in db(\"Test\"):\n        assert issubclass(x.__class__, BaseModel)\n        assert isinstance(x, Person)\n        print(x)\n    db.save(\"hello_world.db\")\n\n    raise Exception(\"test\")  # simulate an Exception which results in a new snapshot file\n```",
    "bugtrack_url": null,
    "license": "MIT",
    "summary": "Simple package for storing pydantic BaseModels in an in-memory SQLite database.",
    "version": "0.4.0",
    "project_urls": {
        "Homepage": "https://github.com/Phil997/pydantic-sqlite",
        "Repository": "https://github.com/Phil997/pydantic-sqlite"
    },
    "split_keywords": [
        "pydantic",
        " sqlite-utils",
        " sqlite3"
    ],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "522a1cc18b195921cb607f45139a14b2aeb8100c5f1f6c4195ace698578557f9",
                "md5": "8a761d1976c768c74b8206f5406a3130",
                "sha256": "c5113812b3a87ff4b89eec05f3f2cdc108283eb699778bcf5ba0a2a7c63a86be"
            },
            "downloads": -1,
            "filename": "pydantic_sqlite-0.4.0-py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "8a761d1976c768c74b8206f5406a3130",
            "packagetype": "bdist_wheel",
            "python_version": "py3",
            "requires_python": "<4.0.0,>=3.8.1",
            "size": 12545,
            "upload_time": "2025-07-12T13:30:15",
            "upload_time_iso_8601": "2025-07-12T13:30:15.356761Z",
            "url": "https://files.pythonhosted.org/packages/52/2a/1cc18b195921cb607f45139a14b2aeb8100c5f1f6c4195ace698578557f9/pydantic_sqlite-0.4.0-py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "854b0281d47a9ce1a56f6a70b449ee5f75a4e2768dd1d057fee88c998662ab09",
                "md5": "af31463f67c1f778c396ac91d0535d69",
                "sha256": "e94c588655c63d6dae612493658ee3aca11cfbb778b38ec05f0b47d75ef296bd"
            },
            "downloads": -1,
            "filename": "pydantic_sqlite-0.4.0.tar.gz",
            "has_sig": false,
            "md5_digest": "af31463f67c1f778c396ac91d0535d69",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": "<4.0.0,>=3.8.1",
            "size": 13223,
            "upload_time": "2025-07-12T13:30:16",
            "upload_time_iso_8601": "2025-07-12T13:30:16.416482Z",
            "url": "https://files.pythonhosted.org/packages/85/4b/0281d47a9ce1a56f6a70b449ee5f75a4e2768dd1d057fee88c998662ab09/pydantic_sqlite-0.4.0.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2025-07-12 13:30:16",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "codeberg": false,
    "github_user": "Phil997",
    "github_project": "pydantic-sqlite",
    "travis_ci": false,
    "coveralls": false,
    "github_actions": true,
    "lcname": "pydantic_sqlite"
}
        
Elapsed time: 1.53591s