Обсуждение: insert into with a dictionary, or generally with a variable number of columns

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.klaver@gmail.com

El 03/07/2012 11:59, Matthieu Rigal escribió:
> 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.
>

I am using this to create dynamic inserts:

 >>> data = {"type": "car", "age": 10, "model": "nissan"}
 >>>
 >>> query = "insert into mytable (%s) values (%s)" % (",".join(['"%s"'
% k for k in data]), ",".join(["%s",]*len(data.keys())))
 >>>
 >>> params = [data[k] for k in data]
 >>>
 >>> print query
insert into mytable ("age","type","model") values (%s,%s,%s)
 >>> print params
[10, 'car', 'nissan']
 >>>
 >>> cursor.execute(query, params)




--
Oswaldo