Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
clach04 opened this issue Aug 23, 2024 · 6 comments
Open

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

clach04 opened this issue Aug 23, 2024 · 6 comments

Comments

@clach04
Copy link
Member

clach04 commented Aug 23, 2024

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
Copy link
Member Author

clach04 commented Aug 26, 2024

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
Copy link
Member Author

clach04 commented Aug 26, 2024

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
Copy link
Member Author

clach04 commented Aug 26, 2024

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
Copy link
Member Author

clach04 commented Aug 26, 2024

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
Copy link
Member Author

clach04 commented Aug 27, 2024 •
edited
Loading

Above scripts can now run with fix for #66 merged

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

@clach04
Copy link
Member Author

clach04 commented Aug 28, 2024

Complete.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@clach04