I have augmented the Base class so it creates the columns used in all tables in my project. However, I would like to control the order of the columns when creating the tables in the database so that the DWH_ID column is the first column in the table and all other columns created in the Base class are last columns of the table.
Let's say that my project is defined as follows:
from sqlalchemy import CHAR, Column, create_engine, DateTime, Integer, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
class Base:
__table_args__ = {'schema': 'some_schema'}
dwh_id = Column(Integer, primary_key=True, autoincrement=True, comment='Surrogate key')
dwh_date_inserted = Column(DateTime(timezone=False), default=func.now(), nullable=False, comment='Date and time when the record has been inserted')
dwh_date_updated = Column(DateTime(timezone=False), nullable=True, comment='Date and time when the record has been updated')
dwh_is_active = Column(CHAR(1), default='Y', nullable=False, comment="'Y' if the record is still available in the source, 'N' otherwise")
engine = create_engine(SOME_CONNECTION_STRING)
Base = declarative_base(cls=Base)
class Person(Base):
__tablename__ = 'persons'
first_name = Column(Text(), nullable=False, comment="First name of the person")
last_name = Column(Text(), nullable=False, comment="Last name of the person")
Base.metadata.create_all(engine, checkfirst=True)
engine.dispose()
The created table looks like this:
dwh_id | dwh_date_inserted | dwh_date_updated | dwh_is_active | first_name | last_name
---------------------------------------------------------------------------------------
I want this order of columns:
dwh_id | first_name | last_name | dwh_date_inserted | dwh_date_updated | dwh_is_active
---------------------------------------------------------------------------------------
I couldn't find anything helpful in the documentation. Any help is appreciated.
In sqlalchemy 2.0+ you can easy do it with the sort_order parameter of mapped_column.