psycopg2 mapping Python : "list of dicts" to Postgres : "array of composite type" for an INSERT statement

Question

Postgres version : 9.1.x.

Say I have the following Schema:

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

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 ]);

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.

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

edit:

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.

edit 2:

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

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.

This :

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))

Creates the following string:

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

Which produces the following error :

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

Nice question, and thanks for following up. In future it's best if you also post your PostgreSQL version, as Pg's capabilities vary significantly from version to version. — Craig Ringer
– Craig Ringer, Commented Aug 15, 2012 at 1:34

Craig Ringer · Accepted Answer · 2012-08-15 01:38:10Z

6

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

Explanation:

If you run:

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

directly in psql you'll get:

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.

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)

edited Aug 15, 2012 at 1:38

answered Aug 15, 2012 at 1:30

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hassan Syed Over a year ago

oh wow , you are a life saver :D I was looking through the documentation for a decoration to %s. I knew I would make sure such a decoration would be possible if I was working on Psycopg :D I'll try and send feedback to the psycopg2 guys to update the documentation.

Hassan Syed Over a year ago

aah ::quotes just gets pasted verbatim into the querry. My bad =D

Craig Ringer Over a year ago

@HassanSyed Glad to help. You were 99% of the way there; sometimes it just takes someone else's eyes when you've been staring at the problem for hours.

Hassan Syed Over a year ago

All wired up an integrated :D now I can have Full text search running off a single table. Yaaay !

Collectives™ on Stack Overflow

psycopg2 mapping Python : "list of dicts" to Postgres : "array of composite type" for an INSERT statement

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related