Python 列表到 PostgreSQL 数组

Question

我有一个列表:

[u'ABC', u'DEF', u'GHI']

我必须将它插入到 postgresql 数组中:(ALTER TABLE "aTable"ADD COLUMN "Test"text[];)

向postgresql添加数据的语法是:

update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}'

如何将列表转换为正确的格式？

Answer 1

请注意，使用 psycopg2，您不需要对数组进行任何字符串处理。这被认为是不好的做法，因为它容易出错，并且在最坏的情况下可能导致开放注入(inject)攻击!您应该始终使用绑定(bind)参数。在下面的代码中，我将创建一个只有一列且类型为 TEXT[] 的新表(与您的原始问题一样)。然后我将添加一个新行，并更新所有这些。所以您会看到 INSERT 和 UPDATE 操作(尽管两者几乎相同)。

如果您仅使用一个值进行更新，则有一个 Python 陷阱:cur.execute 期望 SQL 语句作为第一个参数和一个包含要绑定(bind)的参数的 iterable作为第二个参数。以下将不工作:

from psycopg2 import connect

conn = connect('dbname=exhuma')
cur = conn.cursor()
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values))
conn.commit()

原因是 (new_values) 被 python 视为 new_values(在这种情况下，parens 被删除，它们不被视为元组)。这将导致您提供 3 个值('a'、'b' 和 'c')作为要绑定(bind)的值的错误, 但查询中只有一个占位符 (%s)。相反，您必须按如下方式指定它(注意末尾添加的逗号):

from psycopg2 import connect

conn = connect('dbname=exhuma')
cur = conn.cursor()
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values,))
conn.commit()

这将使 Python 将 (new_values,) 视为具有一个元素的元组(可迭代)，该元素与查询占位符匹配。有关尾随逗号的更详细说明，请参阅 the official docs on tuples .

或者，您也可以编写 [new_values] 而不是 (new_values,)，但是 - 在我看来 - (new_values,)更清晰，因为元组是不可变的，而列表是可变的。

---

这是我测试的表格:

CREATE TABLE foo (
    values TEXT[]
);

下面是插入和更新值的 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()

在每次执行时，代码将插入一个具有相同数组值的新行，然后执行不带 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']")