Suggestion - Integration with sqlacodegen

[avico78]

sqlacodegen is a tool which reads the structure of an existing database and generates the appropriate SQLAlchemy model code: https://github.com/agronholm/sqlacodegen

So if pyantic-sqlalchemy could work with this pacakge - it could be really AWESOME! as it will reduce the need of predefine the sqlachamy model as it will be auto generate by sqlacodegen .

I tried playing with it and somehow combine it with pyantic-sqlalchemy and ... it didn't work:(

For the following tables set:

I call the script and generate below models:

/usr/local/bin/sqlacodegen  --outfile  models.py postgresql://admin:[email protected]:5432/mydata

# coding: utf-8
from sqlalchemy import Boolean, Column, Date, DateTime, ForeignKey, Integer, Numeric, SmallInteger, String, text
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Addres(Base):
    __tablename__ = 'address'

    address_id = Column(Integer, primary_key=True, server_default=text("nextval('address_address_id_seq'::regclass)"))
    address = Column(String(50), nullable=False)
    address2 = Column(String(50))
    district = Column(String(20), nullable=False)
    city_id = Column(SmallInteger, nullable=False, index=True)
    postal_code = Column(String(10))
    phone = Column(String(20), nullable=False)
    last_update = Column(DateTime, nullable=False, server_default=text("now()"))


class Customer(Base):
    __tablename__ = 'customer'

    customer_id = Column(Integer, primary_key=True, server_default=text("nextval('customer_customer_id_seq'::regclass)"))
    store_id = Column(SmallInteger, nullable=False, index=True)
    first_name = Column(String(45), nullable=False)
    last_name = Column(String(45), nullable=False, index=True)
    email = Column(String(50))
    address_id = Column(ForeignKey('address.address_id', ondelete='RESTRICT', onupdate='CASCADE'), nullable=False, index=True)
    activebool = Column(Boolean, nullable=False, server_default=text("true"))
    create_date = Column(Date, nullable=False, server_default=text("('now'::text)::date"))
    last_update = Column(DateTime, server_default=text("now()"))
    active = Column(Integer)

    address = relationship('Addres')


class Payment(Base):
    __tablename__ = 'payment'

    payment_id = Column(Integer, primary_key=True, server_default=text("nextval('payment_payment_id_seq'::regclass)"))
    customer_id = Column(ForeignKey('customer.customer_id', ondelete='RESTRICT', onupdate='CASCADE'), nullable=False, index=True)
    staff_id = Column(SmallInteger, nullable=False, index=True)
    rental_id = Column(Integer, nullable=False, index=True)
    amount = Column(Numeric(5, 2), nullable=False)
    payment_date = Column(DateTime, nullable=False)

    customer = relationship('Customer')

I tried play around with above model and have the ability to generate the Pydamtic class --> read the data from DB . By adding the sql2pydantic conversion:

PydanticCustomer = sqlalchemy_to_pydantic(Customer)
PydanticPaymet = sqlalchemy_to_pydantic(Payment)
PydanticAddress = sqlalchemy_to_pydantic(Addres)



class PydanticCustomerAll(PydanticCustomer):
    payments: List[PydanticPaymet] = []
    addresses: List[PydanticAddress] = []


#metadata = Base.metadata


Base.metadata.create_all(postgress_dvd_engine)

Fetching the data:

def demo():
    customer = db.query(Customer).first()
    pydanticcustomerall = PydanticCustomerAll.from_orm(customer)
    data = pydanticcustomerall.dict()
    return data

Json created but without the data of the nested objects (payments,addresses)

{
  "customer_id": 524,
  "store_id": 1,
  "first_name": "Jared",
  "last_name": "Ely",
  "email": "[email protected]",
  "address_id": 530,
  "activebool": true,
  "create_date": "2006-02-14",
  "last_update": "2013-05-26T14:49:45.738000",
  "active": 1,
  **"payments": [],
  "addresses": []**
}

What is missing here ? seems sqlacodegen generate accurate structure and relation . It is really interesting to know if it possible to combine it with pydantic-sqlalchemy, first, to make it work . second , create the models on-the-fly (without the need for preparing a modely.py in advance.

[pvahabi]

Look, I think sqlacodegen is doing a great job in dumping and creating the sqlalchemy model. What we need is just to dump at the same time the pydantic class definition as well from a db. I don't think that sqlalchemy_to_pydantic is the solution, because it's doing this on the fly (slowing down the code execution, plus I really want to check the class definition).

Perhaps the solution is https://sqlmodel.tiangolo.com/.

@tiangolo any thoughts?