title: How to use nested pydantic models for sqlalchemy in a flexible way
body: <pre class="lang-py prettyprint-override"><code>from fastapi import Depends, FastAPI, HTTPException, Body, Request
  from sqlalchemy import create_engine, Boolean, Column, ForeignKey, Integer, String
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import Session, sessionmaker, relationship
  from sqlalchemy.inspection import inspect
  from typing import List, Optional
  from pydantic import BaseModel
  import json
  
  SQLALCHEMY_DATABASE_URL = &quot;sqlite:///./test.db&quot;
  engine = create_engine(
      SQLALCHEMY_DATABASE_URL, connect_args={&quot;check_same_thread&quot;: False}
  )
  
  SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
  Base = declarative_base()
  app = FastAPI()
  
  
  # sqlalchemy models
  
  class RootModel(Base):
      __tablename__ = &quot;root_table&quot;
      id = Column(Integer, primary_key=True, index=True)
      someRootText = Column(String)
      subData = relationship(&quot;SubModel&quot;, back_populates=&quot;rootData&quot;)
  
  
  class SubModel(Base):
      __tablename__ = &quot;sub_table&quot;
      id = Column(Integer, primary_key=True, index=True)
      someSubText = Column(String)
      root_id = Column(Integer, ForeignKey(&quot;root_table.id&quot;))
      rootData = relationship(&quot;RootModel&quot;, back_populates=&quot;subData&quot;)
  
  
  # pydantic models/schemas
  class SchemaSubBase(BaseModel):
      someSubText: str
  
      class Config:
          orm_mode = True
  
  
  class SchemaSub(SchemaSubBase):
      id: int
      root_id: int
  
      class Config:
          orm_mode = True
  
  
  class SchemaRootBase(BaseModel):
      someRootText: str
      subData: List[SchemaSubBase] = []
  
      class Config:
          orm_mode = True
  
  
  class SchemaRoot(SchemaRootBase):
      id: int
  
      class Config:
          orm_mode = True
  
  
  class SchemaSimpleBase(BaseModel):
      someRootText: str
  
      class Config:
          orm_mode = True
  
  
  class SchemaSimple(SchemaSimpleBase):
      id: int
  
      class Config:
          orm_mode = True
  
  
  Base.metadata.create_all(bind=engine)
  
  
  # database functions (CRUD)
  
  def db_add_simple_data_pydantic(db: Session, root: SchemaRootBase):
      db_root = RootModel(**root.dict())
      db.add(db_root)
      db.commit()
      db.refresh(db_root)
      return db_root
  
  
  def db_add_nested_data_pydantic_generic(db: Session, root: SchemaRootBase):
  
      # this fails:
      db_root = RootModel(**root.dict())
      db.add(db_root)
      db.commit()
      db.refresh(db_root)
      return db_root
  
  
  def db_add_nested_data_pydantic(db: Session, root: SchemaRootBase):
  
      # start: hack: i have to manually generate the sqlalchemy model from the pydantic model
      root_dict = root.dict()
      sub_dicts = []
  
      # i have to remove the list form root dict in order to fix the error from above
      for key in list(root_dict):
          if isinstance(root_dict[key], list):
              sub_dicts = root_dict[key]
              del root_dict[key]
  
      # now i can do it
      db_root = RootModel(**root_dict)
      for sub_dict in sub_dicts:
          db_root.subData.append(SubModel(**sub_dict))
  
      # end: hack
      db.add(db_root)
      db.commit()
      db.refresh(db_root)
      return db_root
  
  
  def db_add_nested_data_nopydantic(db: Session, root):
      print(root)
      sub_dicts = root.pop(&quot;subData&quot;)
      print(sub_dicts)
      db_root = RootModel(**root)
  
      for sub_dict in sub_dicts:
          db_root.subData.append(SubModel(**sub_dict))
      db.add(db_root)
      db.commit()
      db.refresh(db_root)
  
      # problem
      &quot;&quot;&quot;
      if I would now &quot;return db_root&quot;, the answer would be of this:
      {
          &quot;someRootText&quot;: &quot;string&quot;,
          &quot;id&quot;: 24
      }
  
      and not containing &quot;subData&quot;
      therefore I have to do the following.
      Why?
  
      &quot;&quot;&quot;
      from sqlalchemy.orm import joinedload
  
      db_root = (
          db.query(RootModel)
              .options(joinedload(RootModel.subData))
              .filter(RootModel.id == db_root.id)
              .all()
      )[0]
      return db_root
  
  
  # Dependency
  def get_db():
      db = SessionLocal()
      try:
          yield db
      finally:
          db.close()
  
  
  @app.post(&quot;/addNestedModel_pydantic_generic&quot;, response_model=SchemaRootBase)
  def addSipleModel_pydantic_generic(root: SchemaRootBase, db: Session = Depends(get_db)):
      data = db_add_simple_data_pydantic(db=db, root=root)
      return data
  
  
  @app.post(&quot;/addSimpleModel_pydantic&quot;, response_model=SchemaSimpleBase)
  def add_simple_data_pydantic(root: SchemaSimpleBase, db: Session = Depends(get_db)):
      data = db_add_simple_data_pydantic(db=db, root=root)
      return data
  
  
  @app.post(&quot;/addNestedModel_nopydantic&quot;)
  def add_nested_data_nopydantic(root=Body(...), db: Session = Depends(get_db)):
      data = db_add_nested_data_nopydantic(db=db, root=root)
      return data
  
  
  @app.post(&quot;/addNestedModel_pydantic&quot;, response_model=SchemaRootBase)
  def add_nested_data_pydantic(root: SchemaRootBase, db: Session = Depends(get_db)):
      data = db_add_nested_data_pydantic(db=db, root=root)
      return data
  </code></pre>
  <h3>Description</h3>
  <p>My Question is:</p>
  <p><strong>How to make nested sqlalchemy models from nested pydantic models (or python dicts)  in a generic way and write them to the database in &quot;one shot&quot;.</strong></p>
  <p>My example model is called <code>RootModel</code> and has a list of submodels called &quot;sub models&quot; in <code>subData</code> key.</p>
  <p>Please see above for pydantic and sqlalchemy definitions.</p>
  <p>Example:
  The user provides a nested json string:</p>
  <pre><code>{
    &quot;someRootText&quot;: &quot;string&quot;,
    &quot;subData&quot;: [
      {
        &quot;someSubText&quot;: &quot;string&quot;
      }
    ]
  }
  </code></pre>
  <p>Open the browser and call the endpoint <code>/docs</code>.
  You can play around with all endpoints and POST the json string from above.</p>
  /addNestedModel_pydantic_generic
  <p>When you call the endpoint /addNestedModel_pydantic_generic it will fail, because sqlalchemy cannot create the nested model from pydantic nested model directly:
  <code>AttributeError: 'dict' object has no attribute '_sa_instance_state' </code></p>
  ​/addSimpleModel_pydantic
  <p>With a non-nested model  it works.</p>
  <p>The remaining endpoints are showing &quot;hacks&quot; to solve the problem of nested models.</p>
  /addNestedModel_pydantic
  <p>In this endpoint is generate the root model and andd the submodels with a loop in a non-generic way with pydantic models.</p>
  /addNestedModel_pydantic
  <p>In this endpoint is generate the root model and andd the submodels with a loop in a non-generic way with python dicts.</p>
  <p>My solutions are only hacks, I want <strong>a generic way to create nested sqlalchemy models</strong> either from pydantic (preferred) or from a python dict.</p>
  <h3>Environment</h3>
  <ul>
  <li>OS: Windows,</li>
  <li>FastAPI Version : 0.61.1</li>
  <li>Python version: Python 3.8.5</li>
  <li>sqlalchemy: 1.3.19</li>
  <li>pydantic : 1.6.1</li>
  </ul>