SQLAlchemy order_by string column with int values

Question

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.

Answer 1

You can do a cast in SQLAlchemy like this:

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

Answer 2

The best approach I could come up with for Alphanumeric was this:

from sqlalchemy import DECIMAL, cast, func

session.query(...).filter(...).order_by(
        func.ascii(TableName.column_name).asc(),
        cast(
            func.nullif(func.regexp_replace(TableName.column_name, "\\D", "", "g"), ""),
            DECIMAL,
        ).asc(),
    )

Which sorts things like:

-----+-------------
 1   
 100 
 2   
 20  
 A1  
 A2  
 B1  

Not perfect (ideally, 100 would be after 20 ...). I hope the community can help me answer this better someday.