# pydantic-sqlite <!-- omit in toc -->
[](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\n\n[](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"
}
JSON
