Need help with implementing pydantic-sqlalchemy

[avico78]

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?

[tiangolo]

Thanks for the patience with my reply! 😅

This would be a feature request.

I deprecated this library a while ago in favor of SQLModel which would solve all the same use cases in a much better way, and I'm directing all the effort in that direction.

SQLModel uses Pydantic models to define both data and table (SQL) models, so it's easier to do all these things. 🚀