Search code examples
pythonmysqlsqlalchemylexicographic

SQLAlchemy order_by string column with int values

I have a table with a String column but it holds only integers (as strings).

Problem comes when I want to order_by this column on certain query. SQLAlchemy (or python more specifically) uses lexicographic order for strings, so

>>> '100000' < '99999'
True

even when

>>> 100000 < 99999
False

How can I achieve to order_by a numerical value of this string column?

I currently have a query such as this:

session.query(TableName).filter(TableName.column == 'some_value').order_by(TableName.string_column_holding_integers).all()

Please mind that changing the type of the column to an integer is not an option, I need to order_by correctly (by numerical value) on the present conditions.

Also just found that directly on the DB using SQL (this one is a MySQL DB) I cannot order by this column correctly, so I found that using a CAST(string_column_holding_integers AS unsigned) works here. But haven't found a way to do this one (the CAST part) directly on SQLAlchemy queries.

Solution

  • You can do a cast in SQLAlchemy like this:

    session.query(...).filter(...).order_by(cast(TableName.string_column_holding_integers, Integer))