SQLAlchemyCRUDRouter - With joined tables

[avico78]

fastapi 0.63.0 fastapi-crudrouter 0.6.1

Trying to use SQLAlchemyCRUDRouter for joined tables , All endpoints created automatically (amazing:)).

Tried using the "create one " for input:

{
  "customer_no": 1,
  "subscriber": [
    {
      "subscriber_no": 1,
      "is_active": false,
      "owner": 1
    }
  ]
}

End up with error:

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 434, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 1086, in _declarative_constructor
    setattr(self, k, kwargs[k])
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 427, in __set__
    self.impl.set(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1512, in set
    collections.bulk_replace(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 817, in bulk_replace
    appender(member, _sa_initiator=initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1131, in append
    item = __set(self, item, _sa_initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1096, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 727, in fire_append_event
    return self.attr.fire_append_event(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1345, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1675, in emit_backref_from_collection_append_event
    child_state, child_dict = instance_state(child), instance_dict(child)
AttributeError: 'dict' object has no attribute '_sa_instance_state'

Code:

Routes:

from config.config import postgress_engine,SessionLocal
from fastapi_crudrouter impor SQLAlchemyCRUDRouter as SQLDBCrudRouter

customer_router = APIRouter()

def get_db():
    session = SessionLocal()
    try:
        yield session
        session.commit()
    finally:
        session.close()

customer_router = SQLDBCrudRouter(schema=Customer,create_schema=CustomerCreate,
             db_model=CustomerModel,db=get_db,prefix='customer')

schemas.py

from pydantic import BaseModel
from typing import List, Optional

class SubscriberBase(BaseModel):
    subscriber_no: int
    is_active: bool = False

class SubscriberCreate(BaseModel):
    pass

class Subscriber(SubscriberBase):
    owner: int

    class Config:
        orm_mode = True

class CustomerCreate(BaseModel):
    customer_no: Optional[int] = None
    subscriber: Optional[List[Subscriber]] =None

class Customer(CustomerCreate):
    id: int
    class Config:
        orm_mode = True

models.py

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String,DateTime
from sqlalchemy.orm import relationship
from config.config import Base

class CustomerModel(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True, index=True)
    customer_no= Column(Integer,index=True)
    subscriber= relationship("SubscriberModel", back_populates="owner")

class SubscriberModel(Base):
    __tablename__ = 'subscriber'
    id = Column(Integer, ForeignKey("customer.id"))
    subscriber_no= Column(Integer, primary_key=True, index=True)
    owner = relationship("CustomerModel", back_populates="subscriber")

[awtkns]

Hi @avico78. So the CRUDRouter currently does not support inserting nested objects. This is something I am looking at potentially implementing in the future.

Currently you have the option of overloading routes to provide custom functionality (docs). You could do something as below to get around your issue.

customer_router = SQLDBCrudRouter(...)

@custom_router.post("")
def custom_create_router(customer: CustomerModel)
   #my custom db logic
   ...

app.include_router(customer_router)

[avico78]

Thanks @awtkns , Support of nested object would be awesome and actually sound like a real challenge , not sure if this can help solving the nested objects , but it maybe it may interest you : https://github.com/brokenloop/jsontopydantic it building a pydantic classes from a Json - even a real nested one. The core of this project coming from datamodel-code-generator (v 0.5.39 )

import json
from pydantic import Json
from typing import Dict, Any
from genson import SchemaBuilder
from datamodel_code_generator.parser.jsonschema import JsonSchemaParser
from datamodel_code_generator.model.pydantic import (
    BaseModel,
    CustomRootType,
    DataModelField,
)

def translate(
    input_text: Dict,
) -> str:
    builder = SchemaBuilder()
    builder.add_object(input_text)
    schema = json.dumps(builder.to_schema())
    parser = JsonSchemaParser(
        BaseModel,
        CustomRootType,
        DataModelField,
        text=schema,
    )

    return parser.parse()

if __name__=="__main__":
    data2 = {"nested.":{"a":{"b":{"c":{"dList":[{"e":{"f":"val"}}]}}}}}
    result = translate(data2)
    print(result)

[awtkns]

Thanks I will keep that in mind. Ok if I close this for now?

[avico78]

Thanks I will keep that in mind. Ok if I close this for now?

sure,i will follow :) hopefully a solution will be find for this , Thanks @awtkns ,great initiative