Skip site navigation (1) Skip section navigation (2)

Re: insert into with a dictionary, or generally with a variable number of columns

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Matthieu Rigal <mar(at)rapideye(dot)net>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: insert into with a dictionary, or generally with a variable number of columns
Date: 2012-07-03 14:34:41
Message-ID: 4FF30301.30605@gmail.com (view raw or flat)
Thread:
Lists: psycopg
On 07/03/2012 02:59 AM, Matthieu Rigal wrote:
> Hi folks,
>
> To make it very simple, I want to save a dictionary to a database.
>
> I have a function that is reading various entries and returns a dictionary.
> But the content of this dictionary depends on what it was "given" before.
> Sometimes I want to insert a new row with 4 attributes, sometimes with 6...
>
> Therefore, I was hoping to be able to do something like
> cursor.mogrify('''INSERT INTO processing.raster_dataset %(columns)s VALUES
> %(values)s ''', {'columns' :tuple(metaValues.keys()), 'values'  :
> tuple(metaValues.values())})
>
> But it is not working, it returns all the column names in bracket and fails.
>
> I tried specifying the columns as :
>   'columns' : ', '.join(metaValues.keys())
>
> And as you can expect, it neither works.
>
> Does anybody has a suggestion ? I'm sorry I could not find anything on
> forums or previous threads, but maybe I was missing some keywords.

Currently the only way I know to do this from psycopg2 is to use the 
hstore adapter. See the on going thread 'Change in datetime type 
casting' for the current limitations and proposed enhancements. In the docs:
http://initd.org/psycopg/docs/extras.html#hstore-data-type

If the input data resides in the database and you are just moving it 
around, you may want to look at plpythonu:
http://www.postgresql.org/docs/9.1/static/plpython.html

>
> For the real enthousiasts, my final request is in fact even a bit harder.
> But I think to be able to make once I can do the previous step. One of the
> entry of the dictionary can be a geometry, with the value being the WKT
> text of a geometry. And I want to convert it to a PostGIS geometry using
> the "GeometryFromText()" function when passing the value.
>
> Best Regards,
> Matthieu
>
>
> RapidEye AG
> Molkenmarkt 30
> 14776 Brandenburg an der Havel
> Germany
> Follow us on Twitter! www.twitter.com/rapideye_global
> <http://www.twitter.com/rapideye_global>
> Head Office/Sitz der Gesellschaft: Brandenburg an der Havel
> Management Board/Vorstand: Ryan Johnson
> Chairman of Supervisory Board/Vorsitzender des Aufsichtsrates: Robert
> Johnson
> Commercial Register/Handelsregister Potsdam HRB 24742 P
> Tax Number/Steuernummer: 048/100/00320
> VAT-Ident-Number/Ust.-ID: DE 815303842
> DIN EN ISO 9001 certified
>


-- 
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com



In response to

psycopg by date

Next:From: OswaldoDate: 2012-07-04 09:11:11
Subject: Re: insert into with a dictionary, or generally with a variable number of columns
Previous:From: Matthieu RigalDate: 2012-07-03 09:59:02
Subject: insert into with a dictionary, or generally with a variable number of columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group