SQL标识符替换-使用列名列表 [英] SQL identifier substitution - Using a list of column names

问题描述

我正在尝试运行以下形式的查询:

I am trying to run a query of the form:

SELECT {}，其中foo = VALUE

但是我希望能够提供一个列表来替换{}

But I want to be able to provide a list to replace the {}

根据psycopg文档，为了安全地执行此操作，您需要使用 sql.Identifier 函数，以便正确地转义参数，然后执行以下操作:

According to the psycopg docs, in order to do this safely, you need to use the sql.Identifier function, in order to properly escape the parameter, and then do something like this:

SQL = sql.SQL(
    "SELECT {} FROM foo WHERE bar = %s"
).format(identifier)

cursor.execute(SQL, [VALUE])

当 identifier 是单个元素时，此方法有效，但我需要将其设置为任意数字.例如，如果:

This works, when identifier is a single element, but I need it to be an arbitrary number. For example if:

identifier = ["abc"，"def"]

和

VALUE = 4

SQL =从foo WHERE bar = 4中选择abc def

我尝试为 identifier 的每个成员运行 sql.Identifier(x)，但是这给了"abc""def" ，这显然是不正确的.

I've tried running sql.Identifier(x) for every member of identifier, but that gave "abc""def", which is clearly not correct.

推荐答案

您需要使用 sql.join()，以使其像列表理解一样工作:

You need to use sql.join(), to make it work like a list comprehension:

from psycopg2 import sql

cols = ["abc", "def"]
query = sql.SQL(
    "select {0} from {1} where abc = %s").format(
        sql.SQL(', ').join([sql.Identifier(c) for c in cols]),
        sql.Identifier('foo')
)
cur = conn.cursor()
print(query)
print(cur.mogrify(query, ('1', )))
cur.execute(query, ('1', ))
print (cur.rowcount, cur.fetchall())

输出:

Composed([SQL('select '), Composed([Identifier('abc'), SQL(', '), Identifier('def')]), SQL(' from '), Identifier('foo'), SQL(' where abc = %s')])
select "abc", "def" from "foo" where abc = '1'
(1, [('1', '2')])

这篇关于SQL标识符替换-使用列名列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！