python - psycopg2 映射 Python : "list of dicts" to Postgres : "array of composite type" for an INSERT statement

Question

Postgres 版本:9.1.x。

假设我有以下架构:

DROP TABLE IF EXISTS posts CASCADE;
DROP TYPE IF EXISTS quotes CASCADE;

CREATE TYPE quotes AS
(
  text  CHARACTER VARYING,
  is_direct CHARACTER VARYING
);

CREATE TABLE posts
(
    body  CHARACTER VARYING,
    q     quotes[]
);

我希望执行以下插入，显示在 SQL 中，但来自 Python Psycopg2。

insert into posts(body,q) VALUES('ninjas rock',ARRAY[ ROW('I AGREE',True)::quotes, ROW('I DISAGREE',FALSE)::quotes ]);

实现此目的的语法是什么(没有循环等)。我确信这是可能的，因为 documentation says “在 2.4.3 版中更改:添加了对复合类型数组的支持”。该文档仅显示了 SELECT 语句的示例。

注意:我的客户端代码中有一个字典列表，它们在概念上映射到上面的伪模式。

编辑:

嗯，我一定是从文档中遗漏了这一点:“从 Python 元组到复合类型的适配是自动的，不需要适配器注册。”。现在弄清楚数组部分。

编辑2:

psycopg2 的 %s 占位符应该在传递的数据类型为 list(tuple) 或 list(dict) 时起作用。必须测试一下:D

编辑3:好的，差不多了，在这种情况下听写不起作用，列表可以，元组可以。但是，我需要将元组字符串表示形式转换为复合记录类型。

这个:

quote_1 = ("monkeys rock", "False")
quote_2 = ("donkeys rock",  "True")
q_list = [ quote_1, quote_2]
print cur.mogrify("insert into posts VALUES(%s,%s)", ("animals are good", q_list))

创建以下字符串:

insert into posts VALUES('animals are good',ARRAY[('monkeys rock', 'false'), ('donkeys rock', 'true')])

这会产生以下错误:

psycopg2.ProgrammingError: column "q" is of type quotes[] but expression is of type record[]

Answer 1

稍微延长你的努力，怎么样:

quote_1 = ("monkeys rock", "False")
quote_2 = ("donkeys rock",  "True")
q_list = [ quote_1, quote_2]
print cur.mogrify("insert into posts VALUES(%s,%s::quotes[])", 
                  ("animals are good", q_list))
#
#                 added explicit cast to quotes[]->^^^^^^^^

解释:

如果你运行:

insert into posts 
VALUES('animals are good', ARRAY[
    ('monkeys rock', 'false'),
    ('donkeys rock', 'true')
]);

直接在 psql 中你会得到:

regress=# insert into posts 
regress-# VALUES('animals are good',ARRAY[
regress-#             ('monkeys rock', 'false'),
regress-#             ('donkeys rock', 'true')
regress-#  ]);
ERROR:  column "q" is of type quotes[] but expression is of type record[]
LINE 1: insert into posts VALUES('animals are good',ARRAY[('monkeys ...
                                                    ^
HINT:  You will need to rewrite or cast the expression.

果然，告诉 Pg 你的匿名数组是 quotes[] 类型就可以了:

regress=# insert into posts 
regress-# VALUES('animals are good',ARRAY[
regress-#           ('monkeys rock', 'false'),
regress-#           ('donkeys rock', 'true')
regress-# ]::quotes[]);
INSERT 0 1

regress=# select * from posts;
       body       |                           q                            
------------------+--------------------------------------------------------
 animals are good | {"(\"monkeys rock\",false)","(\"donkeys rock\",true)"}
(1 row)