Psycopg2字符串格式，带有用于创建类型的变量名 [英] Psycopg2 string formatting with variable names for type creation

问题描述

当使用psycopg2的格式选项传入要在postgres中创建的变量类型名称时，错误地抛出了错误的字符串格式以用于对象名称。是否不允许在查询参数中使用％s来传递名称？

When passing in a variable type name for creation in postgres using psycopg2 using its formatting option, an error was throw over bad string formatting for the name of objects. Are names not allowed to be passed in using %s within the query parameters?

我编写的可解决问题的代码如下（只是在寻找一种更好的方法解决此问题）

The code I wrote that does solve my issues is below (just looking for a better way to resolve this)

cursor.execute("CREATE TYPE {0} AS ENUM %s".format(name), (tuple(set([e.upper() for e in elements])),))

推荐答案

（2）与PostgreSQL完全无关，也许可以从这个问题中对其进行编辑并将其发布到新的问题中？我会回答（1）。

(2) is completely unrelated to PostgreSQL, maybe edit it out of this question and post it in a new question? I'll answer (1).

如您所料，您不能将表名之类的内容作为查询参数。

As you guessed, you can't pass things like table names as query parameters.

它们是作为协议级绑定参数发送的，（严格来说）查询必须可解析为有效的SQL，并以它们作为占位符。就像您要运行一个SQL级别的 PREPARE ，然后单独运行 EXECUTE 。在准备它之前，必须使用适当的标识符引号将它们格式化为SQL字符串。

They're sent as protocol level bind parameters and (critically) the query must be parseable as valid SQL with them as placeholders. It's as if you were to run an SQL-level PREPARE then separate EXECUTE. You must format them into the SQL string before preparing it, using appropriate identifier quoting.

双引号您要替换的标识符，并提防可能的双引号在您正在传递的字符串中，该字符串可能会提前结束引用的序列。这些必须加倍。例如，对于表名 some table ，您将使用：

Double quote the identifier(s) you're substituting in and beware of possible double quotes within the string you're being passed that might prematurely end your quoted sequence. These must be doubled. For example, for the table name some"table you would use:

 'SELECT * FROM "{0}"'.format('some""table');

SQL注入是一个非常严重的风险；必须正确地引用 。理想情况下，找到与PostgreSQL quote_ident SQL函数相对应的客户端

SQL injection is a very serious risk; you must get your quoting exactly right. Ideally find a client-side equivalent of the PostgreSQL quote_ident SQL function.

请注意，双引号标识符区分大小写，请确保始终在数据库中使用双引号将它们创建为相同大小写，不要将双引号与引号混合使用。未加引号的标识符。

Note that double quoted identifiers are case sensitive. Make sure you create them with the same case in the DB by always using them double quoted, don't mix quoted and unquoted identifiers.

这篇关于Psycopg2字符串格式，带有用于创建类型的变量名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！