Need help with implementing pydantic-sqlalchemy

[avico78]

trafficstars

The required input from the user is a nested json,

{'customer_no': 2, 'subscriber': [{'subscriber_no': 2, 'is_active': False}, {'subscriber_no': 1, 'is_active': False}]}

Expected:

sqlalachamy ORM will break this json and insert it to customers and subsciber tables . where the relation one to many .

Issues:

How i can define the Base class so it will not expect the id column but exactly as

{'customer_no': 2, 'subscriber': [{'subscriber_no': 2, 'is_active': False}, {'subscriber_no': 1, 'is_active': False}]}

i face issue defining the class when i remove fields as "id" (which not exists in the expected json), seems those fileds are required but it also changing the expected data to:

{
  "id": 0,
  "customer_no": 0,
  "subscriber": [
    {
      "customer_no": 0,
      "subscriber_no": 0
    }
  ]
}

Second ,what am i doing wrong ? I expect to get 2 pydamtic class and one class which i need to define myself PydanticCustomerWithSubscriberes ,which describe the one--> many relation between customer--> subscriber block/tables. then , isdie fastapi assign the input(json) to this calss , then generate ORM model which i use to push to the DB.

see: added pydantic-sqlalchemy as part of fastapi,

Models:

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


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

added the Pydantic models and route:

PydanticCustomer = sqlalchemy_to_pydantic(CustomerModel)
PydanticSubscriber = sqlalchemy_to_pydantic(SubscriberModel)


class PydanticCustomerWithSubscriberes(PydanticCustomer):
    subscriber: List[PydanticSubscriber] = None

@customer_router.post("/customer/")
def overloaded_create_customer(customer: PydanticCustomerWithSubscriberes, db: Session = Depends(get_db)):
    db_customer = CustomerModel(**dict(customer))
    db.add(db_customer)
    db.commit()
    db.refresh(db_customer)
    return test

Getting error:

 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: 'SubscriberModel' object has no attribute '_sa_instance_state'

[avico78]

is there anyone who find solution for nested objects?

[peterdudfield]

I tried something, but its a bit manual

ModelFlatPydantic = sqlalchemy_to_pydantic(ModelSQL)
ChildPydantic = sqlalchemy_to_pydantic(ChildSQL)

class ModelPydantic(ModelFlatPydantic):

    child: ChildPydantic

Does that help?