CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
ParkDyel
ParkDyel

Reputation: 312

how to set row number with in sqlAlchemy

want to return row with a row number, not IDX.

with in python 3.6.5 SQLAlchemy==1.2.15 aws rds

query = session.query(product)
row_number_column = func.row_number().over(order_by='IDX').label('row_num')
query = query.add_column(row_number_column)

when run query.all() return syntax err.

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY product.IDX) AS ROW_NUM \nFROM product' at line 1") [SQL: 'SELECT product.IDX AS product_IDX, product.PD_SERIAL AS product_PD_SERIAL, product.PD_COUNT AS product_PD_COUNT, /.../ product.UPDATE_TIME AS product_UPDATE_TIME, row_number() OVER (ORDER BY product.IDX) AS ROW_NUM \nFROM product'] (Background on this error at: http://sqlalche.me/e/f405)

Upvotes: 5

Views: 2971

Answers (2)

vog
vog

Reputation: 25647

You are using a database feature not present in your MySQL version.

The "invalid" SQL part is:

row_number() OVER (ORDER BY product.IDX) AS ROW_NUM

This is actually correct SQL code that uses so-called "window functions", of which row_number() is a very simple one. Those are well-known in PostgreSQL, SQL Server, Oracle and almost every other database. MySQL joined the party lately with MySQL 8.0 (resp. MariaDB 10.2):

There are some workarounds, but those are ugly and not generated automatically by SQL Alchemy.

I propose to either upgrade your MySQL or MariaDB server, or to instead count up the row number on Python side while iterating over the result.

Upvotes: 2

Halvor Holsten Strand
Halvor Holsten Strand

Reputation: 20536

A hacky way of doing it without the ROW_NUMBER function (not present in MySQL server < 8.0):

session.execute('SET @row_number = 0')
query = session.query(product).order_by('IDX')
row_number_column = "(@row_number:=@row_number + 1) AS row_num"
query = query.add_column(row_number_column)

Upvotes: 2

Related Questions