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

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

postgres version : 9.1.x.

say have next schema:

drop table if exists posts cascade; drop type if exists quotes cascade; create type quotes ( text character varying, is_direct character varying ); create table posts ( body character varying, q quotes[] );

and wish perform next insert, shown in sql, python psycopg2.

insert posts(body,q) values('ninjas rock',array[ row('i agree',true)::quotes, row('i disagree',false)::quotes ]);

what syntax accomplish (without loops , such). sure possible since documentation says "changed in version 2.4.3: added back upwards array of composite types". documentation shows examples of select statements.

note: have list of dicts in client code conceptually map psuedo-schema above.

edit:

hmm must have missed documentation : "adaptation python tuples composite types automatic instead , requires no adapter registration.". figure out array part.

edit 2:

psycopg2's %s placeholder should work when info type passed list(tuple) or list(dict). gotta test out :d

edit3: ok there, dicts dont work in scenario, lists do, , tuples do. however, need cast tuple string representation composite record type.

this :

quote_1 = ("monkeys rock", "false") quote_2 = ("donkeys rock", "true") q_list = [ quote_1, quote_2] print cur.mogrify("insert posts values(%s,%s)", ("animals good", q_list))

creates next string:

insert posts values('animals good',array[('monkeys rock', 'false'), ('donkeys rock', 'true')])

which produces next error :

psycopg2.programmingerror: column "q" of type quotes[] look of type record[]

extending efforts tiny bit, how about:

class="lang-python prettyprint-override">quote_1 = ("monkeys rock", "false") quote_2 = ("donkeys rock", "true") q_list = [ quote_1, quote_2] print cur.mogrify("insert posts values(%s,%s::quotes[])", ("animals good", q_list)) # # added explicit cast quotes[]->^^^^^^^^

explanation:

if run:

class="lang-sql prettyprint-override">insert posts values('animals good', array[ ('monkeys rock', 'false'), ('donkeys rock', 'true') ]);

directly in psql you'll get:

class="lang-none prettyprint-override">regress=# insert posts regress-# values('animals good',array[ regress-# ('monkeys rock', 'false'), regress-# ('donkeys rock', 'true') regress-# ]); error: column "q" of type quotes[] look of type record[] line 1: insert posts values('animals good',array[('monkeys ... ^ hint: need rewrite or cast expression.

sure enough, telling pg anonymous array of type quotes[] trick:

class="lang-none prettyprint-override">regress=# insert posts regress-# values('animals good',array[ regress-# ('monkeys rock', 'false'), regress-# ('donkeys rock', 'true') regress-# ]::quotes[]); insert 0 1 regress=# select * posts; body | q ------------------+-------------------------------------------------------- animals | {"(\"monkeys rock\",false)","(\"donkeys rock\",true)"} (1 row)

python postgresql orm relational-database psycopg2