在 Python 的 SELECT 语句中传递列名 [英] Passing a column name in a SELECT statement in Python

问题描述

    if count == 1:
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ?", filters[0], parameters[0])
        all_rows = cursor.fetchall()

    elif count == 2:
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ? AND ? = ?", filters[0], parameters[0], filters[1], parameters[1])
        all_rows = cursor.fetchall()

    elif count == 3 :
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ? AND ? = ? AND ? = ?", filters[0], parameters[0], filters[1], parameters[1], filters[2], parameters[2])
        all_rows = cursor.fetchall()

这是我程序中的代码片段.我打算做的是在查询中传递列名和参数.

This is a code snippet in my program. What I'm planning to do is pass the column name and the parameter in the query.

过滤器数组包含列名，参数数组包含参数.计数是用户设置的过滤器数量.过滤器和参数数组已经准备好了，没有问题.我只需要将它传递给查询即可执行.这给了我一个错误类型错误:函数最多需要 2 个参数"

The filters array contains the columnnames, the parameter array contains the parameters. The count is the number of filters set by the user. The filters and paramters array are already ready and have no problem. I just need to pass it to the query for it to execute. This give me an error of "TypeError: function takes at most 2 arguments"

推荐答案

只能使用 ? 设置参数，不能使用表名或列名.

You can only set parameters using ?, not table or column names.

您可以使用预定义的查询构建字典.

You could build a dict with predefined queries.

queries = {
    "foo": "SELECT * FROM PacketManager WHERE foo = ?",
    "bar": "SELECT * FROM PacketManager WHERE bar = ?",
    "foo_bar": "SELECT * FROM PacketManager WHERE foo = ? AND bar = ?",
}

# count == 1
cursor.execute(queries[filters[0], parameters[0])

# count == 2
cursor.execute(queries[filters[0] + "_" + queries[filters[1], parameters[0])

这种方法将使您避免在 filters[0] 中的 SQL 注入.

This approach will make you save from SQL injection in filters[0].

这篇关于在 Python 的 SELECT 语句中传递列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！