Python，SQL:将列设置为参数 [英] Python, SQL: Set the columns read as parameter

问题描述

我有一个SQL查询，使用 Psycopg2 在Python中很疯狂.该查询从arches表中读取一些列:

I have a SQL query, mad in Python using Psycopg2. The query reads some columns from the arches table:

rows = archesDB.read_all("""SELECT "+str(columns)[1:-1].replace("'","")+" 
                           FROM arches 
                           WHERE lower(arch) like '%%%s%%'""" % (arch.lower()))

我想对查询进行参数化，以便它不会使用字符串连接指定所需的列，而将其作为参数-一种更为优雅的方法.

I want to parametrize this query, so that it will not specify the columns needed using string concatenation, but as parameters - a far more elegant way.

天真的方法是SELECT *，并过滤掉我需要的列.但这会给数据库和网络增加不必要的数据负担，因此我宁愿避免使用它.

The naïve way is to SELECT *, and filter out the columns I need. But this burdens the DB and network with unneeded data, so I rather avoid it.

有什么想法吗?

亚当

推荐答案

列名不能在SQL字符串中作为DB API中的参数传递.而且这也没有任何意义.

Column names cannot be passed in the SQL string as parameters within the DB API. And it would not make sense to it as well.

如果您需要检查输入的列名，请事先对其进行清理.

If you need to check the column name input, sanitize it beforehand.

like字符串应该作为参数输入

The like string should go in as a parameter though

如果columns变量包含用于列名称的字符串列表，则以下代码示例将有所改进:

The following code sample would be an improvement if columns variable contains a list of strings for the column names:

rows = archesDB.read_all("""SELECT %s 
                       FROM arches 
                       WHERE lower(arch) like %%s""" % (",".join(columns),),
                       ("%%%s%%" % (arch.lower(),),))

首先，将列名插入第一个替换(％s)内，最后的%% s转换为％s. 然后，("%%% s %%"％(arch.lower()，)，组成一个带有％string_content％的字符串. 最后，db api转义了％string_content％并在其中添加了引号，最后进行了查询.

First, the column names are inserted within first substitution (%s) and the last %%s is converted to %s. Then the ("%%%s%%" % (arch.lower(),),) makes a string with %string_content%. And at last, db api escapes the %string_content% and adds quotes to it that finally makes the query.

这篇关于Python，SQL:将列设置为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！