How to add a row number column to an SqlAlchemy query?

Question

I would like to have the row number as a column of my queries. Since I am using MySql, I cannot use the built-in func.row_number() of SqlAlchemy. The result of this query is going to be paginated, therefore I would like to keep the row number before the split happen.

session.query(MyModel.id, MyModel.date, "row_number")

I tried to use an hybrid_property to increment a static variable inside MyModel class that I reset before my query, but it didn't work.

@hybrid_property
def row_number(self):
    cls = self.__class__
    cls.row_index = cls.row_index + 1
    return literal(self.row_index)

@row_number.expression
def row_number(cls):
    cls.row_index = cls.row_index + 1
    return literal(cls.row_index)

I also tried to mix a subquery with this solution :

 session.query(myquery.subquery(), literal("@rownum := @rownum + 1 AS row_number"))

But I didn't find a way to make a textual join for (SELECT @rownum := 0) r.

Any suggestions?

EDIT

For the moment, I am looping on the results of the paginated query and I am assigning the calculated number from the current page to each row.

score 5 · Answer 1 · answered Aug 18 '14 at 19:09

SQLAlchemy allows you to use text() in some places, but not arbitrarily. I especially cannot find an easy/documented way of using it in columns or joins. However, you can write your entire query in SQL and still get ORM objects out of it. Example:

query = session.query(Foobar, "rownum")
query = query.from_statement(
    "select foobar.*, cast(@row := @row + 1 as unsigned) as rownum"
    " from foobar, (select @row := 0) as init"
    )

That being said, I don't really see the problem with something like enumerate(query.all()) either. Note that if you use a LIMIT expression, the row numbers you get from MySQL will be for the final result and will still need to have the page start index added. That is, it's not "before the split" by default. If you want to have the starting row added for you in MySQL you can do something like this:

prevrow = 42
query = session.query(Foobar, "rownum")
query = query.from_statement(sqlalchemy.text(
    "select foobar.*, cast(@row := @row + 1 as unsigned) as rownum"
    " from foobar, (select @row := :prevrow) as init"
    ).bindparams(prevrow=prevrow))

In this case the numbers will start at 43 since it's pre-incrementing.

I understand the limitations, unfortunately, I cannot write the query in SQL since we use an engine to dynamically create queries for UI components. I will have to stick to the `for-loop` and `enumerate`. Thanks for answering! — jpmorin, Aug 18 '14 at 21:34

How to add a row number column to an SqlAlchemy query?

1 Answers1