CODEWITHSUNDEEP
pythonsqlalchemy
k107
k107

Reputation: 16450

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

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))

Upvotes: 10

Views: 5414

Answers (3)

ashik
ashik

Reputation: 11

You can solve this problem using raw sql, like

result = session.execute(f"select * from <table_name> where <table_name>.<column_name> = required parameter")
result.all()

this way you get your column order according to table column order

Upvotes: -1

davidism
davidism

Reputation: 127390

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.

Upvotes: 4

Tasos Vogiatzoglou
Tasos Vogiatzoglou

Reputation: 2453

It's a bit funny, but if you change col_3 to col3 it emits the columns as declared.

Why is that ?

When the mapper configuration is executed there is this comment

        # look through columns in the current mapper that
        # are keyed to a propname different than the colname
        # (if names were the same, we'd have popped it out above,
        # in which case the mapper makes this combination).
        # See if the superclass has a similar column property.
        # If so, join them together.

your col_3 is going to the properties argument of the mapper class which will be used as the first arguments of the selectable.

Upvotes: 1

Related Questions