insert a dictionary into sql data base

[David Bear]

Fredrik Lundh wrote:

> David Bear wrote
> 
>> Fredrik Lundh wrote:
>>
>> > cursor.execute(
>> >     "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
>> >     *values
>> >     )
>>
>> Thanks for the hint. However, I don't understand the syntax.
>>
>> I will be inserting in to postgresql 8.x. I assumed the entire string
>> would be evaluated prior to being sent to the cursor.
> 
> Looks like you missed advice 1-3.  I'll take it again: DON'T USE STRING
> FORMATTING TO INSERT VALUES IN A DATABASE.  Sorry for shouting,
> but this is important.  Parameter passing gives you simpler code, and
> fewer security holes.
>

please, shout until I 'get it'... I don't mind. I just don't understand
using the star in front of the values variable; it generates a syntax
exception for me.
 
>> However, when I attempt to manual construct the sql insert statment
>> above I get an error:
>>
>> >>> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
>> *values)
>>   File "<stdin>", line 1
>>     print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
>> *values)
>>                                                                       ^
>> SyntaxError: invalid syntax
> 
> DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT.  Use string
> formatting to insert the field names, but let the database layer deal with
> the values.

since I am so new to this, I didn't know the database layer would handle
this for me. Is the evaluation of the fieldname done by the dbapi layer or
by postgresql? 
> 
> If you want to do things in two steps, do the fields formatting first
> 
>     query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields))
> 
> and pass the query and the values sequence to the database layer:
> 
>     cursor.execute(query, values)

I found this info on the pgdb interface:

http://www.pygresql.org/pg.html

section 4.7 describes the insert method. It is passed the tablename and a
dictionary. But it doesn't describe how it resolves fieldnames and their
values. I assume the dictionary key MUST correspond to a named field.

> 
> The database will take care of the rest.

this is my trouble. I always think I need to do more -- but I can't seem to
find good examples on the http://www.pygresql.org/pgdb.html website.

Do know of any good examples?

> 
> </F>

-- 
David Bear
-- let me buy your intellectual property, I want to own your thoughts --