How can I get Sqlalchemy to preserve column order in the sql it generates?

Question

Sqlalchemy doesn't seem to preserve the column order when it generates sql for a table. How can I get sqlalchemy to use the same order that columns are defined?

Example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, MetaData, Column, BigInteger, Integer, String, MetaData, ForeignKey, Date, DateTime

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()


class MyTable(Base):
    __tablename__ = "my_table"

    id = Column(BigInteger, primary_key=True)
    col1 = Column(String(20), name="col1")
    col2 = Column(Integer)
    col3 = Column(BigInteger, name="col_3")

engine = create_engine('sqlite:///foo.db')
Session = sessionmaker(engine)
db = Session()

print(db.query(MyTable).statement.compile(engine))

Output is:

SELECT my_table.col_3, my_table.id, my_table.col1, my_table.col2
FROM my_table

instead of

SELECT my_table.id, my_table.col1, my_table.col2, my_table.col_3
FROM my_table

Sqlalchemy does seem to preserve the column order in the table definition because when it generates the CREATE TABLE command, the column order matches the order columns are defined.

from sqlalchemy.schema import CreateTable
print(CreateTable(MyTable.__table__).compile(engine))

Answer 1

The columns are stored differently in the mapper than in the table. When you do CreateTable(MyTable.__table__) it looks through columns on the table. When you do session.query(MyTable) it looks through _props on the current mapper. _props is not populated in the same way as columns, and due to some decisions in SQLAlchemy internals this can result in a different order.

The answer is ultimately no, you cannot guarantee the order of queried columns because there are other forces at work behind the scenes. If order really matters to you, it should probably be brought up as an issue against SQLAlchemy.

In this case, assigning a column name that does not match the attribute name changes the order that the column is stored internally. Removing name='col_3' from that column will "fix" this.