test exporting/generating Python code SQLAlchemy model from Ingres airline demodb

[clach04]

Ideally test both declarative and also reflection BUT primary aim would be initially

For example using https://github.com/agronholm/sqlacodegen

Also see https://gtpedrosa.github.io/blog/using-sqlalchemy-to-navigate-an-existing-database/

[clach04]

Quick and dirty with no options seems to work 😺

(py312venv) C:\code\py\sqlalchemy-ingres>sqlacodegen ingres:///demodb
from typing import List, Optional

from sqlalchemy import Column, DECIMAL, DateTime, ForeignKeyConstraint, Integer, LargeBinary, NCHAR, SmallInteger, Table, Unicode
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
import datetime

class Base(DeclarativeBase):
    pass


class Country(Base):
    __tablename__ = 'country'

    ct_id: Mapped[int] = mapped_column(Integer)
    ct_code: Mapped[str] = mapped_column(NCHAR(2), primary_key=True)
    ct_name: Mapped[Optional[str]] = mapped_column(Unicode(50))

    airline: Mapped[List['Airline']] = relationship('Airline', back_populates='country')
    airport: Mapped[List['Airport']] = relationship('Airport', back_populates='country')


t_flight_day = Table(
    'flight_day', Base.metadata,
    Column('day_mask', NCHAR(7), nullable=False),
    Column('day_code', SmallInteger, nullable=False),
    Column('day_name', NCHAR(9), nullable=False)
)


t_full_route = Table(
    'full_route', Base.metadata,
    Column('rt_airline', NCHAR(3), nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('rt_flight_num', Integer, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), nullable=False),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False)
)


t_tz = Table(
    'tz', Base.metadata,
    Column('tz_id', Integer, nullable=False),
    Column('tz_code', NCHAR(5)),
    Column('tz_name', NCHAR(40)),
    Column('tz_utc_offset', DECIMAL(5, 2))
)


t_version = Table(
    'version', Base.metadata,
    Column('ver_id', Integer, nullable=False),
    Column('ver_major', Integer, nullable=False),
    Column('ver_minor', Integer, nullable=False),
    Column('ver_release', Integer, nullable=False),
    Column('ver_date', DateTime, nullable=False),
    Column('ver_install', DateTime, nullable=False)
)


class Airline(Base):
    __tablename__ = 'airline'
    __table_args__ = (
        ForeignKeyConstraint(['al_ccode'], ['country.ct_code'], name='$airli_r0000012e00000000'),
    )

    al_id: Mapped[int] = mapped_column(Integer)
    al_iatacode: Mapped[str] = mapped_column(NCHAR(2))
    al_icaocode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    al_name: Mapped[str] = mapped_column(Unicode(60))
    al_ccode: Mapped[str] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airline')
    route: Mapped[List['Route']] = relationship('Route', back_populates='airline')


class Airport(Base):
    __tablename__ = 'airport'
    __table_args__ = (
        ForeignKeyConstraint(['ap_ccode'], ['country.ct_code'], name='$airpo_r0000013a00000000'),
    )

    ap_id: Mapped[int] = mapped_column(Integer)
    ap_iatacode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    ap_place: Mapped[Optional[str]] = mapped_column(Unicode(30))
    ap_name: Mapped[Optional[str]] = mapped_column(Unicode(50))
    ap_ccode: Mapped[Optional[str]] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airport')
    route: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_arrive_to]', back_populates='airport')
    route_: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_depart_from]', back_populates='airport_')
    user_profile: Mapped[List['UserProfile']] = relationship('UserProfile', back_populates='airport')


class Route(Base):
    __tablename__ = 'route'
    __table_args__ = (
        ForeignKeyConstraint(['rt_airline'], ['airline.al_icaocode'], name='$route_r0000014600000000'),
        ForeignKeyConstraint(['rt_arrive_to'], ['airport.ap_iatacode'], name='$route_r0000015a00000000'),
        ForeignKeyConstraint(['rt_depart_from'], ['airport.ap_iatacode'], name='$route_r0000015000000000')
    )

    rt_id: Mapped[int] = mapped_column(Integer)
    rt_airline: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    rt_flight_num: Mapped[int] = mapped_column(Integer, primary_key=True)
    rt_depart_from: Mapped[str] = mapped_column(NCHAR(3))
    rt_arrive_to: Mapped[str] = mapped_column(NCHAR(3))
    rt_depart_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_offset: Mapped[int] = mapped_column(SmallInteger)
    rt_flight_day: Mapped[str] = mapped_column(NCHAR(7), primary_key=True)

    airline: Mapped['Airline'] = relationship('Airline', back_populates='route')
    airport: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_arrive_to], back_populates='route')
    airport_: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_depart_from], back_populates='route_')


class UserProfile(Base):
    __tablename__ = 'user_profile'
    __table_args__ = (
        ForeignKeyConstraint(['up_airport'], ['airport.ap_iatacode'], name='$user__r0000016800000000'),
    )

    up_id: Mapped[int] = mapped_column(Integer)
    up_last: Mapped[str] = mapped_column(Unicode(30))
    up_first: Mapped[str] = mapped_column(Unicode(30))
    up_email: Mapped[str] = mapped_column(Unicode(100), primary_key=True)
    up_airport: Mapped[Optional[str]] = mapped_column(NCHAR(3))
    up_image: Mapped[Optional[bytes]] = mapped_column(LargeBinary)

    airport: Mapped['Airport'] = relationship('Airport', back_populates='user_profile')

Show Installed packages

Installed packages

(py312venv) C:\code\py\sqlalchemy-ingres\sqlacodegen>pip list
Package            Version        Editable project location
------------------ -------------- ----------------------------------------
certifi            2024.2.2
charset-normalizer 3.3.2
docutils           0.20.1
greenlet           3.0.3
idna               3.6
importlib-metadata 7.0.1
inflect            7.3.1
jaraco.classes     3.3.1
keyring            24.3.0
markdown-it-py     3.0.0
mdurl              0.1.2
more-itertools     10.2.0
nh3                0.2.15
pip                23.2.1
pkginfo            1.9.6
Pygments           2.17.2
pypyodbc           1.3.6
pywin32-ctypes     0.2.2
readme-renderer    42.0
requests           2.31.0
requests-toolbelt  1.0.0
rfc3986            2.0.0
rich               13.7.0
setuptools         69.1.0
sqlacodegen        3.0.0rc5.post1 C:\code\py\sqlalchemy-ingres\sqlacodegen
SQLAlchemy         2.0.32
sqlalchemy-ingres  0.0.9.dev0     C:\code\py\sqlalchemy-ingres
twine              5.0.0
typeguard          4.3.0
typing_extensions  4.12.2
urllib3            2.2.0
zipp               3.17.0

[clach04]

Full example of demodb with all generator types.

show generator dataclasses

generator dataclasses

from typing import List, Optional

from sqlalchemy import Column, DECIMAL, DateTime, ForeignKeyConstraint, Integer, LargeBinary, NCHAR, SmallInteger, Table, Unicode
from sqlalchemy.orm import DeclarativeBase, Mapped, MappedAsDataclass, mapped_column, relationship
import datetime

class Base(MappedAsDataclass, DeclarativeBase):
    pass


class Country(Base):
    __tablename__ = 'country'

    ct_id: Mapped[int] = mapped_column(Integer)
    ct_code: Mapped[str] = mapped_column(NCHAR(2), primary_key=True)
    ct_name: Mapped[Optional[str]] = mapped_column(Unicode(50))

    airline: Mapped[List['Airline']] = relationship('Airline', back_populates='country')
    airport: Mapped[List['Airport']] = relationship('Airport', back_populates='country')


t_flight_day = Table(
    'flight_day', Base.metadata,
    Column('day_mask', NCHAR(7), nullable=False),
    Column('day_code', SmallInteger, nullable=False),
    Column('day_name', NCHAR(9), nullable=False)
)


t_full_route = Table(
    'full_route', Base.metadata,
    Column('rt_airline', NCHAR(3), nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('rt_flight_num', Integer, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), nullable=False),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False)
)


t_tz = Table(
    'tz', Base.metadata,
    Column('tz_id', Integer, nullable=False),
    Column('tz_code', NCHAR(5)),
    Column('tz_name', NCHAR(40)),
    Column('tz_utc_offset', DECIMAL(5, 2))
)


t_version = Table(
    'version', Base.metadata,
    Column('ver_id', Integer, nullable=False),
    Column('ver_major', Integer, nullable=False),
    Column('ver_minor', Integer, nullable=False),
    Column('ver_release', Integer, nullable=False),
    Column('ver_date', DateTime, nullable=False),
    Column('ver_install', DateTime, nullable=False)
)


class Airline(Base):
    __tablename__ = 'airline'
    __table_args__ = (
        ForeignKeyConstraint(['al_ccode'], ['country.ct_code'], name='$airli_r0000012e00000000'),
    )

    al_id: Mapped[int] = mapped_column(Integer)
    al_iatacode: Mapped[str] = mapped_column(NCHAR(2))
    al_icaocode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    al_name: Mapped[str] = mapped_column(Unicode(60))
    al_ccode: Mapped[str] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airline')
    route: Mapped[List['Route']] = relationship('Route', back_populates='airline')


class Airport(Base):
    __tablename__ = 'airport'
    __table_args__ = (
        ForeignKeyConstraint(['ap_ccode'], ['country.ct_code'], name='$airpo_r0000013a00000000'),
    )

    ap_id: Mapped[int] = mapped_column(Integer)
    ap_iatacode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    ap_place: Mapped[Optional[str]] = mapped_column(Unicode(30))
    ap_name: Mapped[Optional[str]] = mapped_column(Unicode(50))
    ap_ccode: Mapped[Optional[str]] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airport')
    route: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_arrive_to]', back_populates='airport')
    route_: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_depart_from]', back_populates='airport_')
    user_profile: Mapped[List['UserProfile']] = relationship('UserProfile', back_populates='airport')


class Route(Base):
    __tablename__ = 'route'
    __table_args__ = (
        ForeignKeyConstraint(['rt_airline'], ['airline.al_icaocode'], name='$route_r0000014600000000'),
        ForeignKeyConstraint(['rt_arrive_to'], ['airport.ap_iatacode'], name='$route_r0000015a00000000'),
        ForeignKeyConstraint(['rt_depart_from'], ['airport.ap_iatacode'], name='$route_r0000015000000000')
    )

    rt_id: Mapped[int] = mapped_column(Integer)
    rt_airline: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    rt_flight_num: Mapped[int] = mapped_column(Integer, primary_key=True)
    rt_depart_from: Mapped[str] = mapped_column(NCHAR(3))
    rt_arrive_to: Mapped[str] = mapped_column(NCHAR(3))
    rt_depart_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_offset: Mapped[int] = mapped_column(SmallInteger)
    rt_flight_day: Mapped[str] = mapped_column(NCHAR(7), primary_key=True)

    airline: Mapped['Airline'] = relationship('Airline', back_populates='route')
    airport: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_arrive_to], back_populates='route')
    airport_: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_depart_from], back_populates='route_')


class UserProfile(Base):
    __tablename__ = 'user_profile'
    __table_args__ = (
        ForeignKeyConstraint(['up_airport'], ['airport.ap_iatacode'], name='$user__r0000016800000000'),
    )

    up_id: Mapped[int] = mapped_column(Integer)
    up_last: Mapped[str] = mapped_column(Unicode(30))
    up_first: Mapped[str] = mapped_column(Unicode(30))
    up_email: Mapped[str] = mapped_column(Unicode(100), primary_key=True)
    up_airport: Mapped[Optional[str]] = mapped_column(NCHAR(3))
    up_image: Mapped[Optional[bytes]] = mapped_column(LargeBinary)

    airport: Mapped['Airport'] = relationship('Airport', back_populates='user_profile')

show generator declarative

generator declarative

from typing import List, Optional

from sqlalchemy import Column, DECIMAL, DateTime, ForeignKeyConstraint, Integer, LargeBinary, NCHAR, SmallInteger, Table, Unicode
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
import datetime

class Base(DeclarativeBase):
    pass


class Country(Base):
    __tablename__ = 'country'

    ct_id: Mapped[int] = mapped_column(Integer)
    ct_code: Mapped[str] = mapped_column(NCHAR(2), primary_key=True)
    ct_name: Mapped[Optional[str]] = mapped_column(Unicode(50))

    airline: Mapped[List['Airline']] = relationship('Airline', back_populates='country')
    airport: Mapped[List['Airport']] = relationship('Airport', back_populates='country')


t_flight_day = Table(
    'flight_day', Base.metadata,
    Column('day_mask', NCHAR(7), nullable=False),
    Column('day_code', SmallInteger, nullable=False),
    Column('day_name', NCHAR(9), nullable=False)
)


t_full_route = Table(
    'full_route', Base.metadata,
    Column('rt_airline', NCHAR(3), nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('rt_flight_num', Integer, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), nullable=False),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False)
)


t_tz = Table(
    'tz', Base.metadata,
    Column('tz_id', Integer, nullable=False),
    Column('tz_code', NCHAR(5)),
    Column('tz_name', NCHAR(40)),
    Column('tz_utc_offset', DECIMAL(5, 2))
)


t_version = Table(
    'version', Base.metadata,
    Column('ver_id', Integer, nullable=False),
    Column('ver_major', Integer, nullable=False),
    Column('ver_minor', Integer, nullable=False),
    Column('ver_release', Integer, nullable=False),
    Column('ver_date', DateTime, nullable=False),
    Column('ver_install', DateTime, nullable=False)
)


class Airline(Base):
    __tablename__ = 'airline'
    __table_args__ = (
        ForeignKeyConstraint(['al_ccode'], ['country.ct_code'], name='$airli_r0000012e00000000'),
    )

    al_id: Mapped[int] = mapped_column(Integer)
    al_iatacode: Mapped[str] = mapped_column(NCHAR(2))
    al_icaocode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    al_name: Mapped[str] = mapped_column(Unicode(60))
    al_ccode: Mapped[str] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airline')
    route: Mapped[List['Route']] = relationship('Route', back_populates='airline')


class Airport(Base):
    __tablename__ = 'airport'
    __table_args__ = (
        ForeignKeyConstraint(['ap_ccode'], ['country.ct_code'], name='$airpo_r0000013a00000000'),
    )

    ap_id: Mapped[int] = mapped_column(Integer)
    ap_iatacode: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    ap_place: Mapped[Optional[str]] = mapped_column(Unicode(30))
    ap_name: Mapped[Optional[str]] = mapped_column(Unicode(50))
    ap_ccode: Mapped[Optional[str]] = mapped_column(NCHAR(2))

    country: Mapped['Country'] = relationship('Country', back_populates='airport')
    route: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_arrive_to]', back_populates='airport')
    route_: Mapped[List['Route']] = relationship('Route', foreign_keys='[Route.rt_depart_from]', back_populates='airport_')
    user_profile: Mapped[List['UserProfile']] = relationship('UserProfile', back_populates='airport')


class Route(Base):
    __tablename__ = 'route'
    __table_args__ = (
        ForeignKeyConstraint(['rt_airline'], ['airline.al_icaocode'], name='$route_r0000014600000000'),
        ForeignKeyConstraint(['rt_arrive_to'], ['airport.ap_iatacode'], name='$route_r0000015a00000000'),
        ForeignKeyConstraint(['rt_depart_from'], ['airport.ap_iatacode'], name='$route_r0000015000000000')
    )

    rt_id: Mapped[int] = mapped_column(Integer)
    rt_airline: Mapped[str] = mapped_column(NCHAR(3), primary_key=True)
    rt_flight_num: Mapped[int] = mapped_column(Integer, primary_key=True)
    rt_depart_from: Mapped[str] = mapped_column(NCHAR(3))
    rt_arrive_to: Mapped[str] = mapped_column(NCHAR(3))
    rt_depart_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_at: Mapped[datetime.datetime] = mapped_column(DateTime)
    rt_arrive_offset: Mapped[int] = mapped_column(SmallInteger)
    rt_flight_day: Mapped[str] = mapped_column(NCHAR(7), primary_key=True)

    airline: Mapped['Airline'] = relationship('Airline', back_populates='route')
    airport: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_arrive_to], back_populates='route')
    airport_: Mapped['Airport'] = relationship('Airport', foreign_keys=[rt_depart_from], back_populates='route_')

show generator sqlmodels

generator sqlmodels

from datetime import datetime
from decimal import Decimal
from typing import List, Optional

from sqlalchemy import Column, DECIMAL, DateTime, ForeignKeyConstraint, Integer, LargeBinary, NCHAR, SmallInteger, Table, Unicode
from sqlmodel import Field, Relationship, SQLModel

class Country(SQLModel, table=True):
    ct_id: int = Field(sa_column=mapped_column('ct_id', Integer))
    ct_code: str = Field(sa_column=mapped_column('ct_code', NCHAR(2), primary_key=True))
    ct_name: Optional[str] = Field(default=None, sa_column=mapped_column('ct_name', Unicode(50)))

    airline: List['Airline'] = Relationship(back_populates='country')
    airport: List['Airport'] = Relationship(back_populates='country')


t_flight_day = Table(
    'flight_day', ,
    Column('day_mask', NCHAR(7), nullable=False),
    Column('day_code', SmallInteger, nullable=False),
    Column('day_name', NCHAR(9), nullable=False)
)


t_full_route = Table(
    'full_route', ,
    Column('rt_airline', NCHAR(3), nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('rt_flight_num', Integer, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), nullable=False),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False)
)


t_tz = Table(
    'tz', ,
    Column('tz_id', Integer, nullable=False),
    Column('tz_code', NCHAR(5)),
    Column('tz_name', NCHAR(40)),
    Column('tz_utc_offset', DECIMAL(5, 2))
)


t_version = Table(
    'version', ,
    Column('ver_id', Integer, nullable=False),
    Column('ver_major', Integer, nullable=False),
    Column('ver_minor', Integer, nullable=False),
    Column('ver_release', Integer, nullable=False),
    Column('ver_date', DateTime, nullable=False),
    Column('ver_install', DateTime, nullable=False)
)


class Airline(SQLModel, table=True):
    __table_args__ = (
        ForeignKeyConstraint(['al_ccode'], ['country.ct_code'], name='$airli_r0000012e00000000'),
    )

    al_id: int = Field(sa_column=mapped_column('al_id', Integer))
    al_iatacode: str = Field(sa_column=mapped_column('al_iatacode', NCHAR(2)))
    al_icaocode: str = Field(sa_column=mapped_column('al_icaocode', NCHAR(3), primary_key=True))
    al_name: str = Field(sa_column=mapped_column('al_name', Unicode(60)))
    al_ccode: str = Field(sa_column=mapped_column('al_ccode', NCHAR(2)))

    country: Optional['Country'] = Relationship(back_populates='airline')
    route: List['Route'] = Relationship(back_populates='airline')


class Airport(SQLModel, table=True):
    __table_args__ = (
        ForeignKeyConstraint(['ap_ccode'], ['country.ct_code'], name='$airpo_r0000013a00000000'),
    )

    ap_id: int = Field(sa_column=mapped_column('ap_id', Integer))
    ap_iatacode: str = Field(sa_column=mapped_column('ap_iatacode', NCHAR(3), primary_key=True))
    ap_place: Optional[str] = Field(default=None, sa_column=mapped_column('ap_place', Unicode(30)))
    ap_name: Optional[str] = Field(default=None, sa_column=mapped_column('ap_name', Unicode(50)))
    ap_ccode: Optional[str] = Field(default=None, sa_column=mapped_column('ap_ccode', NCHAR(2)))

    country: Optional['Country'] = Relationship(back_populates='airport')
    route: List['Route'] = Relationship(back_populates='airport')
    route_: List['Route'] = Relationship(back_populates='airport_')
    user_profile: List['UserProfile'] = Relationship(back_populates='airport')


class Route(SQLModel, table=True):
    __table_args__ = (
        ForeignKeyConstraint(['rt_airline'], ['airline.al_icaocode'], name='$route_r0000014600000000'),
        ForeignKeyConstraint(['rt_arrive_to'], ['airport.ap_iatacode'], name='$route_r0000015a00000000'),
        ForeignKeyConstraint(['rt_depart_from'], ['airport.ap_iatacode'], name='$route_r0000015000000000')
    )

    rt_id: int = Field(sa_column=mapped_column('rt_id', Integer))
    rt_airline: str = Field(sa_column=mapped_column('rt_airline', NCHAR(3), primary_key=True))
    rt_flight_num: int = Field(sa_column=mapped_column('rt_flight_num', Integer, primary_key=True))
    rt_depart_from: str = Field(sa_column=mapped_column('rt_depart_from', NCHAR(3)))
    rt_arrive_to: str = Field(sa_column=mapped_column('rt_arrive_to', NCHAR(3)))
    rt_depart_at: datetime = Field(sa_column=mapped_column('rt_depart_at', DateTime))
    rt_arrive_at: datetime = Field(sa_column=mapped_column('rt_arrive_at', DateTime))
    rt_arrive_offset: int = Field(sa_column=mapped_column('rt_arrive_offset', SmallInteger))
    rt_flight_day: str = Field(sa_column=mapped_column('rt_flight_day', NCHAR(7), primary_key=True))

    airline: Optional['Airline'] = Relationship(back_populates='route')
    airport: Optional['Airport'] = Relationship(back_populates='route')
    airport_: Optional['Airport'] = Relationship(back_populates='route_')


class UserProfile(SQLModel, table=True):
    __tablename__ = 'user_profile'
    __table_args__ = (
        ForeignKeyConstraint(['up_airport'], ['airport.ap_iatacode'], name='$user__r0000016800000000'),
    )

    up_id: int = Field(sa_column=mapped_column('up_id', Integer))
    up_last: str = Field(sa_column=mapped_column('up_last', Unicode(30)))
    up_first: str = Field(sa_column=mapped_column('up_first', Unicode(30)))
    up_email: str = Field(sa_column=mapped_column('up_email', Unicode(100), primary_key=True))
    up_airport: Optional[str] = Field(default=None, sa_column=mapped_column('up_airport', NCHAR(3)))
    up_image: Optional[bytes] = Field(default=None, sa_column=mapped_column('up_image', LargeBinary))

    airport: Optional['Airport'] = Relationship(back_populates='user_profile')

show generator tables

generator tables

from sqlalchemy import Column, DECIMAL, DateTime, ForeignKeyConstraint, Integer, LargeBinary, MetaData, NCHAR, SmallInteger, Table, Unicode

metadata = MetaData()


t_country = Table(
    'country', metadata,
    Column('ct_id', Integer, nullable=False),
    Column('ct_code', NCHAR(2), primary_key=True),
    Column('ct_name', Unicode(50))
)

t_flight_day = Table(
    'flight_day', metadata,
    Column('day_mask', NCHAR(7), nullable=False),
    Column('day_code', SmallInteger, nullable=False),
    Column('day_name', NCHAR(9), nullable=False)
)

t_full_route = Table(
    'full_route', metadata,
    Column('rt_airline', NCHAR(3), nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('rt_flight_num', Integer, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), nullable=False),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False)
)

t_tz = Table(
    'tz', metadata,
    Column('tz_id', Integer, nullable=False),
    Column('tz_code', NCHAR(5)),
    Column('tz_name', NCHAR(40)),
    Column('tz_utc_offset', DECIMAL(5, 2))
)

t_version = Table(
    'version', metadata,
    Column('ver_id', Integer, nullable=False),
    Column('ver_major', Integer, nullable=False),
    Column('ver_minor', Integer, nullable=False),
    Column('ver_release', Integer, nullable=False),
    Column('ver_date', DateTime, nullable=False),
    Column('ver_install', DateTime, nullable=False)
)

t_airline = Table(
    'airline', metadata,
    Column('al_id', Integer, nullable=False),
    Column('al_iatacode', NCHAR(2), nullable=False),
    Column('al_icaocode', NCHAR(3), primary_key=True),
    Column('al_name', Unicode(60), nullable=False),
    Column('al_ccode', NCHAR(2), nullable=False),
    ForeignKeyConstraint(['al_ccode'], ['country.ct_code'], name='$airli_r0000012e00000000')
)

t_airport = Table(
    'airport', metadata,
    Column('ap_id', Integer, nullable=False),
    Column('ap_iatacode', NCHAR(3), primary_key=True),
    Column('ap_place', Unicode(30)),
    Column('ap_name', Unicode(50)),
    Column('ap_ccode', NCHAR(2)),
    ForeignKeyConstraint(['ap_ccode'], ['country.ct_code'], name='$airpo_r0000013a00000000')
)

t_route = Table(
    'route', metadata,
    Column('rt_id', Integer, nullable=False),
    Column('rt_airline', NCHAR(3), primary_key=True, nullable=False),
    Column('rt_flight_num', Integer, primary_key=True, nullable=False),
    Column('rt_depart_from', NCHAR(3), nullable=False),
    Column('rt_arrive_to', NCHAR(3), nullable=False),
    Column('rt_depart_at', DateTime, nullable=False),
    Column('rt_arrive_at', DateTime, nullable=False),
    Column('rt_arrive_offset', SmallInteger, nullable=False),
    Column('rt_flight_day', NCHAR(7), primary_key=True, nullable=False),
    ForeignKeyConstraint(['rt_airline'], ['airline.al_icaocode'], name='$route_r0000014600000000'),
    ForeignKeyConstraint(['rt_arrive_to'], ['airport.ap_iatacode'], name='$route_r0000015a00000000'),
    ForeignKeyConstraint(['rt_depart_from'], ['airport.ap_iatacode'], name='$route_r0000015000000000')
)

t_user_profile = Table(
    'user_profile', metadata,
    Column('up_id', Integer, nullable=False),
    Column('up_last', Unicode(30), nullable=False),
    Column('up_first', Unicode(30), nullable=False),
    Column('up_email', Unicode(100), primary_key=True),
    Column('up_airport', NCHAR(3)),
    Column('up_image', LargeBinary),
    ForeignKeyConstraint(['up_airport'], ['airport.ap_iatacode'], name='$user__r0000016800000000')
)

[clach04]

Gen DDL demo using generated models.

"""Generate DDL for objects

"""

import sqlalchemy
import sqlalchemy.sql.ddl

#from sqlalchemy.dialects import postgresql
#from sqlalchemy.dialects import ingres  # fails as not in this namespace

import demodb_tables

# demodb_tables.t_country

all_objects = []
for object_name in dir(demodb_tables):
    if object_name.startswith('t_'):
        db_object = getattr(demodb_tables, object_name)
        all_objects.append(db_object)


engine = None

con_str = 'postgresql://'
con_str = 'mysql://'
con_str = 'ingres://'
engine = sqlalchemy.create_engine(con_str)



for db_object in all_objects:
    if_not_exists= True
    #ddl_object = sqlalchemy.sql.ddl.CreateTable(demodb_tables.t_country, if_not_exists=if_not_exists)
    ddl_object = sqlalchemy.sql.ddl.CreateTable(db_object, include_foreign_key_constraints=db_object.foreign_key_constraints, if_not_exists=if_not_exists)
    print(str(ddl_object))

    if engine:
        statement = sqlalchemy.sql.ddl.CreateTable(db_object)
        #print(statement.compile(dialect=postgresql.dialect()))  # hard coded dialect
        print(statement.compile(dialect=engine.dialect))

[clach04]

An alternative using dataclasses / declarative

# from https://gist.github.com/Daenyth/f190cb47b42c4ff839f2

"""
Print the raw DDL for a sqlalchemy declarative mapping
"""

import sqlalchemy ; print(sqlalchemy.__file__)
from sqlalchemy import create_engine
from sqlalchemy.schema import CreateTable

#engine = create_engine('postgresql://')
#engine = create_engine('ingres://')  # nope
con_str = 'ingres:///demodb'  # local demodb
con_str = 'ingres:///'  # no database works fine
con_str = 'ingres://'  # experiment - initially caused https://github.com/ActianCorp/sqlalchemy-ingres/issues/66

con_str = 'postgresql://'

engine = create_engine(con_str)
print(dir(engine))



# from myapp.model import Base  # your declarative_base

from demodb_dataclasses import Base  # your declarative_base
#from demodb_declarative import Base  # your declarative_base

for table in Base.metadata.tables.values():
    print(CreateTable(table).compile(engine))
print(dir(engine.dialect))

NOTE can fake this for databases withOUT drivers:

(py312venv) C:\code\py\sqlalchemy-ingres>type psycopg2.py
this_is_fake = True

extras = None

paramstyle = 'qmark'

(py312venv) C:\code\py\sqlalchemy-ingres>type MySQLdb.py
this_is_fake = True

extras = None

paramstyle = 'qmark'

[clach04]

Above scripts can now run with fix for #66 merged

NOTE order of creates likely to be out of order/sequence.

[clach04]

Complete.