psycopg2映射Python:“字典列表"到Postgres:“复合类型数组"用于INSERT语句

问题描述

Postgres版本:9.1.x.

Postgres version : 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执行.

And I wish to perform the following insert, shown in SQL, but from Python Psycopg2.

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

实现此目的的语法是什么(没有循环等).我敢肯定，因为文档说 在2.4.3版中进行了更改:增加了对复合类型数组的支持" .该文档仅显示SELECT语句的示例.

What is the syntax to achieve this (without loops and such). I am sure it is possible since the documentation says "Changed in version 2.4.3: added support for array of composite types". The documentation only shows examples of SELECT statements.

注意:我的客户端代码中有一系列字典，这些字典在概念上映射到上面的psuedo-schema.

Note: I have a list of dicts in my client code that conceptually map to the psuedo-schema above.

嗯，我一定从文档中错过了这一点:从Python元组到复合类型的自适应是自动的，不需要适配器注册." .现在找出阵列部分.

Hmm I must have missed this from the documentation : "Adaptation from Python tuples to composite types is automatic instead and requires no adapter registration.". Now to figure out the array part.

修改2:

psycopg2的%s占位符应该起作用.要测试一下:D

psycopg2's %s placeholder should work when the data type passed is list(tuple) or list(dict). Gotta test it out :D

edit3:好的，到此为止，字典在这种情况下不起作用，列表起作用，而元组起作用.但是，我需要将元组字符串表示形式转换为复合记录类型.

edit3:Ok almost there, dicts dont work in this scenario, lists do, and tuples do. However, I need to cast the tuple string representation into the composite record type.

此:

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[]

推荐答案

只需付出一点点努力，怎么样:

Extending your efforts just a tiny bit, how about:

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[]类型的:

Sure enough, telling Pg that your anonymous array is of type quotes[] does the trick:

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)

这篇关于psycopg2映射Python:“字典列表"到Postgres:“复合类型数组"用于INSERT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！