Python 在 psycopg2 中将表名作为参数传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13793399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing table name as a parameter in psycopg2
提问by Caligari
I have the following code, using pscyopg2:
我有以下代码，使用 pscyopg2：
sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'
data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql, data)
This outputs:
这输出：
select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;
When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.
当我执行它时，它倒下 - 这是可以理解的，因为表名周围的引号是非法的。
Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:
有没有办法合法地将表名作为参数传递，或者我是否需要进行（明确警告）字符串连接，即：
voyage = 'ss2012_t02'
sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'
Cheers for any insights.
为任何见解干杯。
采纳答案by PearsonArtPhoto
The table name cannot be passed as a parameter, but everything else can. Thus, the table name should be hard coded in your app (Don't take inputs or use anything outside of the program as a name). The code you have should work for this.
表名不能作为参数传递，但其他一切都可以。因此，表名应该在您的应用程序中进行硬编码（不要接受输入或使用程序之外的任何内容作为名称）。您拥有的代码应该适用于此。
On the slight chance that you have a legitimate reason to take an outside table name, make sure that you don't allow the user to directly input it. Perhaps an index could be passed to select a table, or the table name could be looked up in some other way. You are right to be wary of doing this, however. This works, because there are relatively few table names around. Find a way to validate the table name, and you should be fine.
如果您有正当理由使用外部表名，请确保您不允许用户直接输入它。也许可以传递一个索引来选择一个表，或者可以通过其他方式查找表名。但是，您对此保持警惕是正确的。这是有效的，因为周围的表名相对较少。找到一种方法来验证表名，你应该没问题。
It would be possible to do something like this, to see if the table name exists. This is a parameterised version. Just make sure that you do this and verify the output prior to running the SQL code. Part of the idea for this comes from this answer.
可以做这样的事情，看看表名是否存在。这是一个参数化版本。只需确保在运行 SQL 代码之前执行此操作并验证输出即可。这个想法的一部分来自这个答案。
SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1
回答by malthe
If you want to pass the table name as a parameter, you can use this wrapper:
如果要将表名作为参数传递，可以使用此包装器：
class Literal(str):
def __conform__(self, quote):
return self
@classmethod
def mro(cls):
return (object, )
def getquoted(self):
return str(self)
Usage: cursor.execute("CREATE TABLE %s ...", (Literal(name), ))
用法： cursor.execute("CREATE TABLE %s ...", (Literal(name), ))
回答by Tobias
I have created a little utility for preprocessing of SQL statements with variable table (...) names:
我创建了一个小实用程序，用于预处理带有变量表 (...) 名称的 SQL 语句：
from string import letters
NAMECHARS = frozenset(set(letters).union('.'))
def replace_names(sql, **kwargs):
"""
Preprocess an SQL statement: securely replace table ... names
before handing the result over to the database adapter,
which will take care of the values.
There will be no quoting of names, because this would make them
case sensitive; instead it is ensured that no dangerous chars
are contained.
>>> replace_names('SELECT * FROM %(table)s WHERE val=%(val)s;',
... table='fozzie')
'SELECT * FROM fozzie WHERE val=%(val)s;'
"""
for v in kwargs.values():
check_name(v)
dic = SmartDict(kwargs)
return sql % dic
def check_name(tablename):
"""
Check the given name for being syntactically valid,
and usable without quoting
"""
if not isinstance(tablename, basestring):
raise TypeError('%r is not a string' % (tablename,))
invalid = set(tablename).difference(NAMECHARS)
if invalid:
raise ValueError('Invalid chars: %s' % (tuple(invalid),))
for s in tablename.split('.'):
if not s:
raise ValueError('Empty segment in %r' % tablename)
class SmartDict(dict):
def __getitem__(self, key):
try:
return dict.__getitem__(self, key)
except KeyError:
check_name(key)
return key.join(('%(', ')s'))
The SmartDict object returns %(key)sfor every unknown key, preserving them for the value handling. The function could check for the absence of any quote characters, since all quoting now should be taken care of ...
SmartDict 对象%(key)s为每个 unknown返回key，保留它们用于值处理。该函数可以检查是否缺少任何引号字符，因为现在应该处理所有引用...
回答by jczaplew
Per this answeryou can do it as so:
根据这个答案，您可以这样做：
import psycopg2
from psycopg2.extensions import AsIs
#Create your connection and cursor...
cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")})
回答by Derek Bartron
You can just use the module format for the table name and then use the regular paramaterization for the execute:
您可以只对表名使用模块格式，然后对执行使用常规参数化：
xlist = (column, table)
sql = 'select {0} from {1} where utctime > %s and utctime < %s order by utctime asc;'.format(xlist)
Keep in mind if this is exposed to the end user, you will not be protected from SQL injection unless you write for it.
请记住，如果这暴露给最终用户，除非您为它编写，否则您将不会受到 SQL 注入的保护。
回答by Samir Alajmovic
Surprised no one has mentioned doing this:
惊讶没有人提到这样做：
sql = 'select {} from {} where utctime > {} and utctime < {} order by utctime asc;'.format(dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql)
format puts in the string without quotations.
format 放入不带引号的字符串。
回答by Antoine Dusséaux
According to the official documentation:
根据官方文档：
If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module.
如果您需要动态生成 SQL 查询（例如动态选择表名），您可以使用 psycopg2.sql 模块提供的工具。
The sqlmodule is new in psycopg2 version 2.7. It has the following syntax:
该sql模块是 psycopg2 2.7 版中的新模块。它具有以下语法：
from psycopg2 import sql
cur.execute(
sql.SQL("insert into {} values (%s, %s)")
.format(sql.Identifier('my_table')),
[10, 20])
More on: http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql
更多信息：http: //initd.org/psycopg/docs/sql.html#module-psycopg2.sql
[Update 2017-03-24: AsIsshould NOT be used to represent table or fields names, the new sqlmodule should be used instead: https://stackoverflow.com/a/42980069/5285608]
[2017-03-24 更新：AsIs不应用于表示表或字段名称，sql应使用新模块：https: //stackoverflow.com/a/42980069/5285608]
Also, according to psycopg2 documentation:
另外，根据 psycopg2 文档：
Warning: Never, never, NEVERuse Python string concatenation (
+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.
警告：从不、从不、从不使用 Python 字符串连接 (
+) 或字符串参数插值 (%) 将变量传递给 SQL 查询字符串。甚至不是在枪口下。
回答by Benjamin Rothschild
This is a workaround I have used in the past
这是我过去使用的解决方法
query = "INSERT INTO %s (col_1, col_2) VALUES (%%s, %%s)" % table_name
cur.execute(query, (col_1_var, col_2_var))
Hope it help :)
希望它有帮助:)
