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.

Rochelle

Answer 1

You can do a cast in SQLAlchemy like this:

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

Jab

what if the column contains both numbers & strings ? how can I still preserve a correct ordering ? – Hunkydory

@Ricky That's not possible. All values in a column must be the same type. – Jab

In PostgressSQL i.e you can do: ORDER BY NULLIF(regexp_replace(my_column_1, E'\\D', '', 'g'), '')::int ( if the value is not INT - it ignores and continue ) this way we can order numeric values correctly and continue with strings as usual, que is - how in SQLAlchemy we do it – Hunkydory

@Ricky That's not a mixed-type column, as you put it, but you can order by the same thing. You can even use that exact string if you wanted to by using text. Try .order_by(cast(func.nullif(func.regexp_replace(tbl.c.my_column_1, "\\D", "", "g"), ""), Integer)). – Jab

for me, the alphanumeric sort I was looking for was .order_by(cast(func.substring(Tbl.name, "([0-9]+)"), DECIMAL).asc(), Tbl.name) – Wring

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.

Wring