Python 列表到 PostgreSQL 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20699196/
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
Python List to PostgreSQL Array
提问by saromba
I have a list:
我有一个清单：
[u'ABC', u'DEF', u'GHI']
I have to insert it into a postgresql array:
(ALTER TABLE "aTable" ADD COLUMN "Test" text[];)
我必须将它插入到 postgresql 数组中：( ALTER TABLE "aTable" ADD COLUMN "Test" text[];)
The syntax for adding data to the postgresql is:
向 postgresql 添加数据的语法是：
update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}'
How can I convert the list to the correct format?
如何将列表转换为正确的格式？
采纳答案by Atul Arvind
list = [u'ABC', u'DEF', u'GHI']
list = str(map(str, list))
list = list.replace('[', '{').replace(']', '}').replace('\'', '\"')
query = '''update "aTable" SET "Test" = '%s\'''' %(list)
print query
Will result in to,
会导致，
update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}'
回答by exhuma
Note that with psycopg2you don't need to do any string processing for arrays. This is considered as bad practice as it is error-prone and can - in the worst case - lead to opening up injection attacks! You should always use bound parameters. In the code below, I will create a new table with only one column with the type TEXT[](as in your original question). Then I will add a new row, and update all of them. So you will see both an INSERTand UPDATEoperation (although both are pretty much identical).
请注意，psycopg2您不需要对数组进行任何字符串处理。这被认为是不好的做法，因为它容易出错，并且可能 - 在最坏的情况下 - 导致打开注入攻击！您应该始终使用绑定参数。在下面的代码中，我将创建一个只有一列类型的新表TEXT[]（如您的原始问题）。然后我将添加一个新行，并更新所有这些。因此，您将同时看到 anINSERT和UPDATE操作（尽管两者几乎相同）。
There is one Python gotcha though if you update with only one value: cur.executeexpects the SQL statement as first argument and an iterablecontaining the parameters to be bound as second argument. The following will notwork:
如果您只更新一个值，则有一个 Python 问题：cur.execute期望 SQL 语句作为第一个参数，以及一个包含要绑定的参数作为第二个参数的可迭代对象。下面将不工作：
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values))
The reason is that (new_values)is seen by python as new_values(the parens are dropped in this case, they are not seen as tuple). This will result in the error that you supply 3 values ('a', 'b'and 'c') as values to be bound, but there's only one placeholder (%s) in the query. Instead you must specify it as follows (notice the added comma at the end):
原因是(new_values)python将其视为new_values（在这种情况下，括号被删除，它们不被视为元组）。这将导致您提供 3 个值 ( 'a','b'和'c') 作为要绑定的值的错误，但%s查询中只有一个占位符 ( )。相反，您必须按如下方式指定它（注意末尾添加的逗号）：
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values,))
This will make Python see (new_values,)as a tuple (which is an iterable) with one element, which matches the query place-holders. For a more detailed explanation of the trailing comma, see the official docs on tuples.
这将使 Python 被(new_values,)视为具有一个元素的元组（它是一个可迭代的），该元素与查询占位符匹配。有关尾随逗号的更详细说明，请参阅有关 tuples 的官方文档。
Alternatively, you could also write [new_values]instead of (new_values,), but - in my opinion - (new_values,)is cleaner as tuples are immutable, whereas lists are mutable.
或者，您也可以编写[new_values]而不是(new_values,)，但是 - 在我看来 -(new_values,)更干净，因为元组是不可变的，而列表是可变的。
Here's the table with which I tested:
这是我测试的表格：
CREATE TABLE foo (
values TEXT[]
);
And here's Python code both inserting and updating values:
这是插入和更新值的 Python 代码：
from psycopg2 import connect
conn = connect('dbname=exhuma')
cur = conn.cursor()
cur.execute('INSERT INTO foo VALUES (%s)', (['a', 'b'], ))
print('>>> Before update')
cur.execute('SELECT * FROM foo')
for row in cur:
print(type(row[0]), repr(row[0]))
print('>>> After update')
cur.execute('UPDATE foo SET example_values = %s',
(['new', 'updated', 'values'],))
cur.execute('SELECT * FROM foo')
for row in cur:
print(type(row[0]), repr(row[0]))
cur.close()
conn.commit()
conn.close()
On each execution, the code will insert a new row with the same array values, then execute an update without WHEREclause, so all values are updated. After a couple of executions, I this gives the following output:
每次执行时，代码都会插入一个具有相同数组值的新行，然后执行不带WHERE子句的更新，因此所有值都被更新。经过几次执行后，我给出了以下输出：
>>> Before update
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['a', 'b']")
>>> After update
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
回答by Peter Eisentraut
You just pass that list as a parameter to execute. You don't need to do anything special. Psycopg converts Python lists into a suitable PostgreSQL array literal.
您只需将该列表作为参数传递给execute. 你不需要做任何特别的事情。Psycopg 将 Python 列表转换为合适的 PostgreSQL 数组文字。
import psycopg2 as dbapi
conn = dbapi.connect(dsn='')
c = conn.cursor()
x = [u'ABC', u'DEF', u'GHI']
c.execute('update "aTable" SET "Test" = %s', [x])
回答by alko
Proper way of handling variables in SQL is usage of bind variables. To my knowledge, this is crucial for MySQL and Oracle DB, and I believe for PostgreSQLas well.
在 SQL 中处理变量的正确方法是使用绑定变量。据我所知，这对于 MySQL 和 Oracle DB 至关重要，我相信对于PostgreSQL也是如此。
So better code would be along the lines
所以更好的代码应该是
def as_array(l):
l2str = ','.join('"{}"'.format(x) for x in l)
return '{{{}}}'.format(l2str)
query = '''update "aTable" SET "Test" = %s'''
lst = [u'ABC', u'DEF', u'GHI']
cur = conn.cursor()
cur.execute(query, as_array(lst))
where conn is a connection to PostgreSQL db.
其中 conn 是到 PostgreSQL 数据库的连接。
