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