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.
> 
>> 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.
> 
> 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)
> 
> The database will take care of the rest.
> 
> </F>

I think I'm missing some important documentation somewhere. Here's what I
tried (using both % and $ signs):

>>> sql
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
(%s);'

>>> sql2
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
($s);'
>>> values
['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']

>>> cursor.execute(sql1, values)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
NameError: name 'sql1' is not defined
>>> cursor.execute(sql, values)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute
    self.executemany(operation, (params,))
  File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in
executemany
    raise OperationalError, "internal error in '%s': %s" % (sql,err)
pg.OperationalError: internal error in 'INIT': not all arguments converted
during string formatting

I get the same error if using $ sign.

When I look at the pygresql dbapi official site at
http://www.pygresql.org/pgdb.html

"this section needs to be written"...

I would really appreciate some more examples on using pgdb (pygresql)


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