# sqlalchemy-pydantic-orm
This library makes it a lot easier to do nested database operation with
SQLAlchemy. With this library it is for example possible to validate, convert,
and upload a 100-level deep nested JSON (dict) to its corresponding tables in a
given database, within 3 lines of code.
[Pydantic](https://pydantic-docs.helpmanual.io/) is used for creating the
dataclass and validating it. Pydantic already has a function called
[`.from_orm()`](https://pydantic-docs.helpmanual.io/usage/models/#orm-mode-aka-arbitrary-class-instances)
that can do a nested get operation, but it only supports ORM -> Pydantic and
not Pydantic -> ORM. That's exactly where this library fills in, with 2
specific functions `.orm_create()` and `.orm_update()`, and one general
function `.to_orm()` that combines the functionality of the first 2, calling
one or the other, depending on if there is an id provided.
# Requirements
- Python 3.8+
- SQLAlchemy 1.4+
- Pydantic 1.8+
# Installation
```shell
$ pip install AFAS-pydantic-orm
```
To tinker with the code yourself, install the full dependencies with:
```shell
$ pip install AFAS-pydantic-orm[dev]
```
# Useful references
- https://pydantic-docs.helpmanual.io/usage/models/
- https://fastapi.tiangolo.com/tutorial/sql-databases/
# Examples
Below 1 example is provided (more coming).
[comment]: <> (The first one is a more manual setup, the second does all the work for you.)
For a bigger and more detailed example you can look at the /examples/ folder.
## Example 1 - Using manual created schemas
Create your own Pydantic schemas and link them to the SQLAlchemy ORM-models.
### Create your SQLAlchemy ORM-models (one-to-one or one-to-many)
```python
class Parent(Base):
id = Column(Integer, primary_key=True, index=True, nullable=False)
name = Column(String, nullable=False)
car = relationship("Car", cascade="all, delete", uselist=False, back_populates="owner")
children = relationship("Child", cascade="all, delete")
class Car(Base):
id = Column(Integer, primary_key=True, index=True, nullable=False)
color = Column(String, nullable=False)
owner_id = Column(Integer, ForeignKey("parents.id"), nullable=False)
owner = relationship("Parent", back_populates="car")
class Child(Base):
id = Column(Integer, primary_key=True, index=True, nullable=False)
name = Column(String, nullable=False)
parent_id = Column(Integer, ForeignKey("parents.id"), nullable=False)
```
### Create your Pydantic base and CRUD schemas using these ORM models, and the imported ORMBaseSchema
#### Base schemas
```python
from sqlalchemy_pydantic_orm import ORMBaseSchema
from .models import Parent, Car, Child
class ParentBase(ORMBaseSchema):
name: str
_orm_model = PrivateAttr(Parent)
class CarBase(ORMBaseSchema):
color: str
_orm_model = PrivateAttr(models.Car)
class ChildBase(ORMBaseSchema):
name: str
_orm_model = PrivateAttr(models.Child)
```
#### GET schemas
```python
class Parent(ParentBase):
id: int
children: List[Child]
car: Car
class Car(CarBase):
id: int
class Child(ChildBase):
id: int
```
#### CREATE/UPDATE schemas
```python
class ParentCreate(ParentBase):
id: Optional[int]
children: List[ChildCreate]
car: CarCreate
class CarCreate(CarBase):
id: Optional[int]
class ChildCreate(ChildBase):
id: Optional[int]
```
### Use your schemas to do nested CRU~~D~~ operations.
```python
with ConnectionDatabase() as db:
create_schema = schemas.ParentCreate.parse_obj(create_dict)
parent_db = create_schema.orm_create()
db.add(parent_db)
db.commit()
db_create_schema = schemas.Parent.from_orm(parent_db)
print(db_create_schema.dict())
update_schema = schemas.ParentUpdate.parse_obj(update_dict)
update_schema.to_orm(db)
db.commit()
db_update_schema = schemas.Parent.orm_update(parent_db)
print(db_update_schema.dict())
```
Note: with `.orm_create()` you have to call `db.add()`
before calling `db.commit()`.
With orm_update you give the db session as parameter,
and you only have to call `db.commit()`.
## ~~Example 2 - Using generated schemas~~
TODO: Integrate with https://github.com/tiangolo/pydantic-sqlalchemy
Raw data
{
"_id": null,
"home_page": "https://github.com/Alexanderkievit/AFAS_Pydantic_ORM",
"name": "AFAS-pydantic-orm",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "python, pydantic, sqlalchemy, ORM, nested, nesting, CRUD",
"author": "AKievit",
"author_email": "<alexander.kievit@afas.nl>",
"download_url": "https://files.pythonhosted.org/packages/3f/f1/f4b2fb0f4df489aad50d0a65a600f51034986367035ed2f3edbe32cdbaf3/afas_pydantic_orm-1.0.0.tar.gz",
"platform": null,
"description": "# sqlalchemy-pydantic-orm\r\nThis library makes it a lot easier to do nested database operation with \r\nSQLAlchemy. With this library it is for example possible to validate, convert, \r\nand upload a 100-level deep nested JSON (dict) to its corresponding tables in a \r\ngiven database, within 3 lines of code. \r\n\r\n[Pydantic](https://pydantic-docs.helpmanual.io/) is used for creating the \r\ndataclass and validating it. Pydantic already has a function called\r\n[`.from_orm()`](https://pydantic-docs.helpmanual.io/usage/models/#orm-mode-aka-arbitrary-class-instances) \r\nthat can do a nested get operation, but it only supports ORM -> Pydantic and \r\nnot Pydantic -> ORM. That's exactly where this library fills in, with 2 \r\nspecific functions `.orm_create()` and `.orm_update()`, and one general \r\nfunction `.to_orm()` that combines the functionality of the first 2, calling \r\none or the other, depending on if there is an id provided.\r\n\r\n\r\n# Requirements\r\n- Python 3.8+\r\n- SQLAlchemy 1.4+\r\n- Pydantic 1.8+\r\n\r\n# Installation\r\n```shell\r\n$ pip install AFAS-pydantic-orm\r\n```\r\nTo tinker with the code yourself, install the full dependencies with:\r\n```shell\r\n$ pip install AFAS-pydantic-orm[dev]\r\n```\r\n\r\n# Useful references\r\n- https://pydantic-docs.helpmanual.io/usage/models/\r\n- https://fastapi.tiangolo.com/tutorial/sql-databases/\r\n\r\n\r\n# Examples\r\nBelow 1 example is provided (more coming).\r\n\r\n[comment]: <> (The first one is a more manual setup, the second does all the work for you.)\r\nFor a bigger and more detailed example you can look at the /examples/ folder.\r\n\r\n## Example 1 - Using manual created schemas\r\nCreate your own Pydantic schemas and link them to the SQLAlchemy ORM-models.\r\n\r\n### Create your SQLAlchemy ORM-models (one-to-one or one-to-many)\r\n```python\r\nclass Parent(Base):\r\n id = Column(Integer, primary_key=True, index=True, nullable=False)\r\n name = Column(String, nullable=False)\r\n car = relationship(\"Car\", cascade=\"all, delete\", uselist=False, back_populates=\"owner\")\r\n children = relationship(\"Child\", cascade=\"all, delete\")\r\n \r\nclass Car(Base):\r\n id = Column(Integer, primary_key=True, index=True, nullable=False)\r\n color = Column(String, nullable=False)\r\n owner_id = Column(Integer, ForeignKey(\"parents.id\"), nullable=False)\r\n owner = relationship(\"Parent\", back_populates=\"car\")\r\n\r\nclass Child(Base):\r\n id = Column(Integer, primary_key=True, index=True, nullable=False)\r\n name = Column(String, nullable=False)\r\n parent_id = Column(Integer, ForeignKey(\"parents.id\"), nullable=False)\r\n```\r\n\r\n### Create your Pydantic base and CRUD schemas using these ORM models, and the imported ORMBaseSchema\r\n\r\n#### Base schemas\r\n```python\r\nfrom sqlalchemy_pydantic_orm import ORMBaseSchema\r\nfrom .models import Parent, Car, Child\r\n\r\nclass ParentBase(ORMBaseSchema):\r\n name: str\r\n _orm_model = PrivateAttr(Parent)\r\n\r\nclass CarBase(ORMBaseSchema):\r\n color: str\r\n _orm_model = PrivateAttr(models.Car)\r\n\r\nclass ChildBase(ORMBaseSchema):\r\n name: str\r\n _orm_model = PrivateAttr(models.Child)\r\n```\r\n\r\n#### GET schemas\r\n```python\r\nclass Parent(ParentBase):\r\n id: int\r\n children: List[Child]\r\n car: Car\r\n\r\nclass Car(CarBase):\r\n id: int\r\n\r\nclass Child(ChildBase):\r\n id: int\r\n```\r\n\r\n#### CREATE/UPDATE schemas\r\n```python\r\nclass ParentCreate(ParentBase):\r\n id: Optional[int]\r\n children: List[ChildCreate]\r\n car: CarCreate\r\n\r\nclass CarCreate(CarBase):\r\n id: Optional[int]\r\n\r\nclass ChildCreate(ChildBase):\r\n id: Optional[int]\r\n```\r\n\r\n### Use your schemas to do nested CRU~~D~~ operations.\r\n```python\r\nwith ConnectionDatabase() as db:\r\n create_schema = schemas.ParentCreate.parse_obj(create_dict)\r\n parent_db = create_schema.orm_create()\r\n db.add(parent_db)\r\n db.commit()\r\n\r\n db_create_schema = schemas.Parent.from_orm(parent_db)\r\n print(db_create_schema.dict())\r\n\r\n update_schema = schemas.ParentUpdate.parse_obj(update_dict)\r\n update_schema.to_orm(db)\r\n db.commit()\r\n\r\n db_update_schema = schemas.Parent.orm_update(parent_db)\r\n print(db_update_schema.dict())\r\n```\r\nNote: with `.orm_create()` you have to call `db.add()`\r\nbefore calling `db.commit()`. \r\nWith orm_update you give the db session as parameter,\r\nand you only have to call `db.commit()`.\r\n\r\n\r\n## ~~Example 2 - Using generated schemas~~\r\nTODO: Integrate with https://github.com/tiangolo/pydantic-sqlalchemy\r\n",
"bugtrack_url": null,
"license": null,
"summary": "CRUD operations on nested SQLAlchemy ORM-models using Pydantic",
"version": "1.0.0",
"project_urls": {
"Documentation": "https://github.com/Alexanderkievit/AFAS_Pydantic_ORM",
"Homepage": "https://github.com/Alexanderkievit/AFAS_Pydantic_ORM",
"Source": "https://github.com/Alexanderkievit/AFAS_Pydantic_ORM",
"Tracker": "https://github.com/Alexanderkievit/AFAS_Pydantic_ORM/issues"
},
"split_keywords": [
"python",
" pydantic",
" sqlalchemy",
" orm",
" nested",
" nesting",
" crud"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "3ff1f4b2fb0f4df489aad50d0a65a600f51034986367035ed2f3edbe32cdbaf3",
"md5": "fb20fc0088021479d9c05f65b6bdbf01",
"sha256": "879f484c30fe839185192cd8e6ae4450c04891e32e832f6051a80c22eede8e50"
},
"downloads": -1,
"filename": "afas_pydantic_orm-1.0.0.tar.gz",
"has_sig": false,
"md5_digest": "fb20fc0088021479d9c05f65b6bdbf01",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 9736,
"upload_time": "2024-09-17T08:07:01",
"upload_time_iso_8601": "2024-09-17T08:07:01.057630Z",
"url": "https://files.pythonhosted.org/packages/3f/f1/f4b2fb0f4df489aad50d0a65a600f51034986367035ed2f3edbe32cdbaf3/afas_pydantic_orm-1.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-17 08:07:01",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "Alexanderkievit",
"github_project": "AFAS_Pydantic_ORM",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [
{
"name": "annotated-types",
"specs": [
[
"==",
"0.7.0"
]
]
},
{
"name": "appdirs",
"specs": [
[
"==",
"1.4.4"
]
]
},
{
"name": "attrs",
"specs": [
[
"==",
"24.2.0"
]
]
},
{
"name": "backports.tarfile",
"specs": [
[
"==",
"1.2.0"
]
]
},
{
"name": "black",
"specs": [
[
"==",
"24.8.0"
]
]
},
{
"name": "bleach",
"specs": [
[
"==",
"6.1.0"
]
]
},
{
"name": "build",
"specs": [
[
"==",
"1.2.2"
]
]
},
{
"name": "certifi",
"specs": [
[
"==",
"2024.8.30"
]
]
},
{
"name": "cffi",
"specs": [
[
"==",
"1.17.1"
]
]
},
{
"name": "chardet",
"specs": [
[
"==",
"5.2.0"
]
]
},
{
"name": "charset-normalizer",
"specs": [
[
"==",
"3.3.2"
]
]
},
{
"name": "check-manifest",
"specs": [
[
"==",
"0.49"
]
]
},
{
"name": "click",
"specs": [
[
"==",
"8.1.7"
]
]
},
{
"name": "colorama",
"specs": [
[
"==",
"0.4.6"
]
]
},
{
"name": "coverage",
"specs": [
[
"==",
"7.6.1"
]
]
},
{
"name": "cryptography",
"specs": [
[
"==",
"43.0.1"
]
]
},
{
"name": "distlib",
"specs": [
[
"==",
"0.3.8"
]
]
},
{
"name": "docutils",
"specs": [
[
"==",
"0.21.2"
]
]
},
{
"name": "exceptiongroup",
"specs": [
[
"==",
"1.2.2"
]
]
},
{
"name": "filelock",
"specs": [
[
"==",
"3.16.0"
]
]
},
{
"name": "flake8",
"specs": [
[
"==",
"7.1.1"
]
]
},
{
"name": "greenlet",
"specs": [
[
"==",
"3.1.0"
]
]
},
{
"name": "idna",
"specs": [
[
"==",
"3.10"
]
]
},
{
"name": "importlib_metadata",
"specs": [
[
"==",
"8.5.0"
]
]
},
{
"name": "iniconfig",
"specs": [
[
"==",
"2.0.0"
]
]
},
{
"name": "isort",
"specs": [
[
"==",
"5.13.2"
]
]
},
{
"name": "jaraco.classes",
"specs": [
[
"==",
"3.4.0"
]
]
},
{
"name": "jaraco.context",
"specs": [
[
"==",
"6.0.1"
]
]
},
{
"name": "jaraco.functools",
"specs": [
[
"==",
"4.0.2"
]
]
},
{
"name": "jeepney",
"specs": [
[
"==",
"0.8.0"
]
]
},
{
"name": "keyring",
"specs": [
[
"==",
"25.3.0"
]
]
},
{
"name": "Mako",
"specs": [
[
"==",
"1.3.5"
]
]
},
{
"name": "Markdown",
"specs": [
[
"==",
"3.7"
]
]
},
{
"name": "markdown-it-py",
"specs": [
[
"==",
"3.0.0"
]
]
},
{
"name": "MarkupSafe",
"specs": [
[
"==",
"2.1.5"
]
]
},
{
"name": "mccabe",
"specs": [
[
"==",
"0.7.0"
]
]
},
{
"name": "mdurl",
"specs": [
[
"==",
"0.1.2"
]
]
},
{
"name": "more-itertools",
"specs": [
[
"==",
"10.5.0"
]
]
},
{
"name": "mypy",
"specs": [
[
"==",
"1.11.2"
]
]
},
{
"name": "mypy-extensions",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "nh3",
"specs": [
[
"==",
"0.2.18"
]
]
},
{
"name": "packaging",
"specs": [
[
"==",
"24.1"
]
]
},
{
"name": "pathspec",
"specs": [
[
"==",
"0.12.1"
]
]
},
{
"name": "pdoc3",
"specs": [
[
"==",
"0.11.1"
]
]
},
{
"name": "pep517",
"specs": [
[
"==",
"0.13.1"
]
]
},
{
"name": "pkginfo",
"specs": [
[
"==",
"1.10.0"
]
]
},
{
"name": "platformdirs",
"specs": [
[
"==",
"4.3.3"
]
]
},
{
"name": "pluggy",
"specs": [
[
"==",
"1.5.0"
]
]
},
{
"name": "py",
"specs": [
[
"==",
"1.11.0"
]
]
},
{
"name": "pycodestyle",
"specs": [
[
"==",
"2.12.1"
]
]
},
{
"name": "pycparser",
"specs": [
[
"==",
"2.22"
]
]
},
{
"name": "pydantic",
"specs": [
[
"==",
"2.9.1"
]
]
},
{
"name": "pydantic_core",
"specs": [
[
"==",
"2.23.3"
]
]
},
{
"name": "pyflakes",
"specs": [
[
"==",
"3.2.0"
]
]
},
{
"name": "Pygments",
"specs": [
[
"==",
"2.18.0"
]
]
},
{
"name": "pyparsing",
"specs": [
[
"==",
"3.1.4"
]
]
},
{
"name": "pyproject_hooks",
"specs": [
[
"==",
"1.1.0"
]
]
},
{
"name": "pytest",
"specs": [
[
"==",
"8.3.3"
]
]
},
{
"name": "pywin32-ctypes",
"specs": [
[
"==",
"0.2.3"
]
]
},
{
"name": "readme_renderer",
"specs": [
[
"==",
"44.0"
]
]
},
{
"name": "regex",
"specs": [
[
"==",
"2024.9.11"
]
]
},
{
"name": "requests",
"specs": [
[
"==",
"2.32.3"
]
]
},
{
"name": "requests-toolbelt",
"specs": [
[
"==",
"1.0.0"
]
]
},
{
"name": "rfc3986",
"specs": [
[
"==",
"2.0.0"
]
]
},
{
"name": "rich",
"specs": [
[
"==",
"13.8.1"
]
]
},
{
"name": "SecretStorage",
"specs": [
[
"==",
"3.3.3"
]
]
},
{
"name": "six",
"specs": [
[
"==",
"1.16.0"
]
]
},
{
"name": "SQLAlchemy",
"specs": [
[
"==",
"2.0.34"
]
]
},
{
"name": "toml",
"specs": [
[
"==",
"0.10.2"
]
]
},
{
"name": "tomli",
"specs": [
[
"==",
"2.0.1"
]
]
},
{
"name": "tqdm",
"specs": [
[
"==",
"4.66.5"
]
]
},
{
"name": "twine",
"specs": [
[
"==",
"5.1.1"
]
]
},
{
"name": "typed-ast",
"specs": [
[
"==",
"1.5.5"
]
]
},
{
"name": "typing_extensions",
"specs": [
[
"==",
"4.12.2"
]
]
},
{
"name": "urllib3",
"specs": [
[
"==",
"2.2.3"
]
]
},
{
"name": "virtualenv",
"specs": [
[
"==",
"20.26.4"
]
]
},
{
"name": "webencodings",
"specs": [
[
"==",
"0.5.1"
]
]
},
{
"name": "zipp",
"specs": [
[
"==",
"3.20.2"
]
]
}
],
"lcname": "afas-pydantic-orm"
}
