Title: Flexibly Create Nested Database Entries from Incoming Pydantic/SQLModels · Issue #6 · fastapi/sqlmodel · GitHub
Open Graph Title: Flexibly Create Nested Database Entries from Incoming Pydantic/SQLModels · Issue #6 · fastapi/sqlmodel
X Title: Flexibly Create Nested Database Entries from Incoming Pydantic/SQLModels · Issue #6 · fastapi/sqlmodel
Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I already searched in Google "How to X in...
Open Graph Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I...
X Description: First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated searc...
Opengraph URL: https://github.com/fastapi/sqlmodel/issues/6
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Flexibly Create Nested Database Entries from Incoming Pydantic/SQLModels","articleBody":"### First Check\n\n- [X] I added a very descriptive title to this issue.\n- [X] I used the GitHub search to find a similar issue and didn't find it.\n- [X] I searched the SQLModel documentation, with the integrated search.\n- [X] I already searched in Google \"How to X in SQLModel\" and didn't find any information.\n- [X] I already read and followed all the tutorial in the docs and didn't find an answer.\n- [X] I already checked if it is not related to SQLModel but to [Pydantic](https://github.com/samuelcolvin/pydantic).\n- [X] I already checked if it is not related to SQLModel but to [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy).\n\n### Commit to Help\n\n- [X] I commit to help with one of those options 👆\n\n### Example Code\n\n```python\n# Sudo Code Based on Examples in Docs\r\n\r\nclass Team(SQLModel, table=True):\r\n id: Optional[int] = Field(default=None, primary_key=True)\r\n name: str\r\n headquarters: str\r\n\r\n\r\n heroes: List[\"Hero\"] = Relationship(back_populates=\"team\")\r\n\r\n\r\n\r\nclass Hero(SQLModel, table=True):\r\n id: Optional[int] = Field(default=None, primary_key=True)\r\n name: str\r\n secret_name: str\r\n age: Optional[int] = None\r\n\r\n team_id: Optional[int] = Field(default=None, foreign_key=\"team.id\")\r\n\r\n team: Optional[Team] = Relationship(back_populates=\"heroes\")\r\n\r\n\r\npayload = {\r\n \"name\": \"Team Name\",\r\n \"headquarters\": \"Whereever\".\r\n \"heroes\": [\r\n \"name\": \"Name 1\"\r\n // Other Requied Fields... 👇\r\n ]\r\n}\r\n\r\nwith Session(engine) as session:\r\n Team.create_all_nested(session, payload) # or something?\n```\n\n\n### Description\n\nI would like to do what is described in [FastAPI issue #2194](https://github.com/tiangolo/fastapi/issues/2194)\r\n\r\n> How to make nested sqlalchemy models from nested pydantic models (or python dicts) in a generic way and write them to the database in \"one shot\".\r\n\r\nIn the example above, I'd like to pass in the payload to a method and the following to occur.\r\n* Create new Team entry\r\n* Create Hero entry and/or Relate the existing Hero to the Team\r\n\r\nSimilarly, I'd like the same to happen on update. Effectively making writing to the SQL database akin to writing to MongoDB\r\n\r\nI don't believe this is supported or haven't gotten it to work, but my main questions are.\r\n1. Is this supported? \r\n2. If no, is this a use-case you've thought of? \r\n3. Are you interested in a PR to support this either as a utility method or some sort of decorator? \r\n\r\nLoving working with this so far, thanks for all your hard work! \n\n### Operating System\n\nmacOS\n\n### Operating System Details\n\n_No response_\n\n### SQLModel Version\n\n0.0.3\n\n### Python Version\n\n3.9.6\n\n### Additional Context\n\nI have accomplished this with SQLAlchemy in the past by using an `auto_init` decarator.\r\n\r\n\r\n```python\r\nfrom functools import wraps\r\nfrom typing import Union\r\n\r\nfrom sqlalchemy.orm import MANYTOMANY, MANYTOONE, ONETOMANY\r\n\r\n\r\ndef handle_one_to_many_list(relation_cls, all_elements: list[dict]):\r\n elems_to_create = []\r\n updated_elems = []\r\n\r\n for elem in all_elements:\r\n elem_id = elem.get(\"id\", None)\r\n\r\n existing_elem = relation_cls.get_ref(match_value=elem_id)\r\n\r\n if existing_elem is None:\r\n\r\n elems_to_create.append(elem)\r\n\r\n else:\r\n for key, value in elem.items():\r\n setattr(existing_elem, key, value)\r\n\r\n updated_elems.append(existing_elem)\r\n\r\n new_elems = []\r\n for elem in elems_to_create:\r\n new_elems = [relation_cls(**elem) for elem in all_elements]\r\n\r\n return new_elems\r\n\r\n\r\ndef auto_init(exclude: Union[set, list] = None): # sourcery no-metrics\r\n \"\"\"Wraps the `__init__` method of a class to automatically set the common\r\n attributes.\r\n\r\n Args:\r\n exclude (Union[set, list], optional): [description]. Defaults to None.\r\n \"\"\"\r\n\r\n exclude = exclude or set()\r\n exclude.add(\"id\")\r\n\r\n def decorator(init):\r\n @wraps(init)\r\n def wrapper(self, *args, **kwargs): # sourcery no-metrics\r\n \"\"\"\r\n Custom initializer that allows nested children initialization.\r\n Only keys that are present as instance's class attributes are allowed.\r\n These could be, for example, any mapped columns or relationships.\r\n\r\n Code inspired from GitHub.\r\n Ref: https://github.com/tiangolo/fastapi/issues/2194\r\n \"\"\"\r\n cls = self.__class__\r\n model_columns = self.__mapper__.columns\r\n relationships = self.__mapper__.relationships\r\n\r\n session = kwargs.get(\"session\", None)\r\n\r\n for key, val in kwargs.items():\r\n if key in exclude:\r\n continue\r\n\r\n if not hasattr(cls, key):\r\n continue\r\n # raise TypeError(f\"Invalid keyword argument: {key}\")\r\n\r\n if key in model_columns:\r\n setattr(self, key, val)\r\n continue\r\n\r\n if key in relationships:\r\n relation_dir = relationships[key].direction.name\r\n relation_cls = relationships[key].mapper.entity\r\n use_list = relationships[key].uselist\r\n\r\n if relation_dir == ONETOMANY.name and use_list:\r\n instances = handle_one_to_many_list(relation_cls, val)\r\n setattr(self, key, instances)\r\n\r\n if relation_dir == ONETOMANY.name and not use_list:\r\n instance = relation_cls(**val)\r\n setattr(self, key, instance)\r\n\r\n elif relation_dir == MANYTOONE.name and not use_list:\r\n if isinstance(val, dict):\r\n val = val.get(\"id\")\r\n\r\n if val is None:\r\n raise ValueError(f\"Expected 'id' to be provided for {key}\")\r\n\r\n if isinstance(val, (str, int)):\r\n instance = relation_cls.get_ref(match_value=val, session=session)\r\n setattr(self, key, instance)\r\n\r\n elif relation_dir == MANYTOMANY.name:\r\n\r\n if not isinstance(val, list):\r\n raise ValueError(f\"Expected many to many input to be of type list for {key}\")\r\n\r\n if len(val) > 0 and isinstance(val[0], dict):\r\n val = [elem.get(\"id\") for elem in val]\r\n\r\n instances = [relation_cls.get_ref(elem, session=session) for elem in val]\r\n setattr(self, key, instances)\r\n\r\n return init(self, *args, **kwargs)\r\n\r\n return wrapper\r\n\r\n return decorator\r\n\r\n```\r\n\r\n## Usage\r\n\r\n```python\r\n\r\nclass AdminModel(SqlAlchemyBase, BaseMixins):\r\n name = Column(String, index=True)\r\n email = Column(String, unique=True, index=True)\r\n password = Column(String)\r\n is_superuser = Column(Boolean(), default=False)\r\n\r\n @auto_init(exclude={'is_superuser'})\r\n def __init__(self, **_):\r\n this.is_superuser = false\r\n\r\n @classmethod\r\n def get_ref(cls, match_value: str, match_attr: str = \"id\"):\r\n with SessionLocal() as session:\r\n eff_ref = getattr(cls, match_attr)\r\n return session.query(cls).filter(eff_ref == match_value).one_or_none()\r\n\r\n```decorator","author":{"url":"https://github.com/hay-kot","@type":"Person","name":"hay-kot"},"datePublished":"2021-08-24T23:12:28Z","url":"https://github.com/fastapi/sqlmodel/issues/6"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:84d56d19-8756-11eb-ac4c-f1b7dc32c8fa |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | AB8A:2B73A8:8FC454:CA5A9E:6AFE1B99 |
| html-safe-nonce | 47aa602a750d7e259761f93e37547ab2539e71f2673f8eaa57dc2ca8a1539998 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJBQjhBOjJCNzNBODo4RkM0NTQ6Q0E1QTlFOjZBRkUxQjk5IiwidmlzaXRvcl9pZCI6IjE5MTU1MjY0MjkwNzY3NTc0MDEiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | a3e243abcc4d51253c83d6d6a3b96f1f9a0a96677be16ceee808dd31aadbe703 |
| hovercard-subject-tag | issue:978553881 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/fastapi/sqlmodel/6/issue_layout |
| twitter:image | https://opengraph.githubassets.com/5148646d6c27fb97fb2542906700f2a0e2a3767ed45ae21ea4c3a3bbdb7b630e/fastapi/sqlmodel/issues/6 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/5148646d6c27fb97fb2542906700f2a0e2a3767ed45ae21ea4c3a3bbdb7b630e/fastapi/sqlmodel/issues/6 |
| og:image:alt | First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | hay-kot |
| hostname | github.com |
| expected-hostname | github.com |
| None | d0525ab1464526bf4532a5551aa348cf100dff75ef808a96fc67a08b1d946aa9 |
| turbo-cache-control | no-preview |
| go-import | github.com/fastapi/sqlmodel git https://github.com/fastapi/sqlmodel.git |
| octolytics-dimension-user_id | 156354296 |
| octolytics-dimension-user_login | fastapi |
| octolytics-dimension-repository_id | 399495186 |
| octolytics-dimension-repository_nwo | fastapi/sqlmodel |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 399495186 |
| octolytics-dimension-repository_network_root_nwo | fastapi/sqlmodel |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | fef3993a71fa20f7be8e167132fbf9619bc66e12 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width