如何在sqlAlchemy中设置行号

Question

想要返回行号而不是IDX。

在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)

运行query.all()时返回语法错误。

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)

- ParkDyel

Answer 1

您正在使用一个在您的MySQL版本中不存在的数据库功能。

"无效" SQL 部分是：

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

这实际上是正确的SQL代码，使用了所谓的"窗口函数"，其中row_number()是非常简单的一个。这些在PostgreSQL、SQL Server、Oracle和几乎所有其他数据库中都是众所周知的。MySQL最近加入了这个行列，从MySQL 8.0（或MariaDB 10.2）开始：

https://mariadb.com/kb/en/library/row_number/

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

有一些解决方法，但这些方法很丑陋，并且不会由SQL Alchemy自动生成。

我建议要么升级您的MySQL或MariaDB服务器，要么在迭代结果时在Python端计算行号。

- vog

---

这个答案只适用于 MySQL 服务器版本 < 8.0，对吗？ - Halvor Holsten Strand

@HalvorHolstenStrand 感谢您的提示。我已经相应地调整了我的答案。 - vog

Answer 2

一种没有使用ROW_NUMBER函数（MySQL服务器版本低于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)

- Halvor Holsten Strand

---

谢谢你的帮助，我明天早上会运行！ - ParkDyel

经过几天的寻找，我发现很难解释以下现象：在add_column之前query.first() -> <model.pad.Product object at 0x0000017F78B5D358>在add_column之后query.first() = (<model.pad.Product object at 0x0000017F78B5D358>, 1)我认为1应该放在对象内部，但它却粘在外面。 - ParkDyel

@ParkDyel，这对你来说是必须的吗？我认为这可能很难做到。通常我会遍历结果，例如 for product, row_num in result: ...，然后你就可以在结果集循环内部将产品和相关行号作为变量使用。 - Halvor Holsten Strand

正如您所说，我在for语句中直接赋值。我试图在一个SQL语句中操作它，但这不是一种简单的方法。真的非常感谢！在这里祝您感恩节快乐，愿您拥有丰富的财富。 - ParkDyel