SQLite3 查询 ORDER BY 参数与 ?符号 [英] SQLite3 query ORDER BY parameter with ? notation

问题描述

我正在尝试使用 python 中的 sqlite3 进行查询，按参数列overall_risk"或latest_risk"(这是双数)进行排序

I am trying to make a query with sqlite3 in python, to be ordered by a parameter column "overall_risk" or "latest_risk" (which is double number)

param = ('overall_risk',)
cur = db.execute("SELECT * FROM users ORDER BY ? DESC", param)

但是，我不会返回按"overall_risk"(或"latest_risk")排序的数据，此外，当我编写如下查询时:

However, I doesn't return the data ordered by "overall_risk" (or "latest_risk"), moreover, when I write the query like:

"SELECT * FROM users ORDER BY " + 'overall_risk' + " DESC"

确实有效.

推荐答案

SQL 参数不能用于 值 以外的任何内容.使用占位符的全部意义在于正确引用值以避免将其解释为 SQL 语句的一部分或对象.

SQL parameters can't be used for anything other than values. The whole point in using a placeholder is to have the value properly quoted to avoid it from being interpreted as part of a SQL statement or as an object.

您正在尝试插入一个对象名称，而不是一个值，因此您不能为此使用 ?.您必须使用字符串格式，并且对允许使用的名称非常小心:

You are trying to insert an object name, not a value, so you can't use ? for this. You'll have to use string formatting and be extremely careful as to what names you allow:

cur = db.execute("SELECT * FROM users ORDER BY {} DESC".format(column_name))

如果 column_name 是从用户输入中获取的，您需要对照现有的列名进行验证.

If column_name is taken from user input, you'll need to validate this against existing column names.

另一种方法是使用像 SQLAlchemy 这样的库来为您生成 SQL.请参阅SQL 表达式语言教程.

The alternative is to use a library like SQLAlchemy to generate your SQL for you. See the SQL Expression Language tutorial.

这篇关于SQLite3 查询 ORDER BY 参数与 ?符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！