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

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