insert a dictionary into sql data base

I have a dictionary that contains a row of data intended for a data base.

The dictionary keys are the field names. The values are the values to be
inserted.

I am looking for a good pythonic way of expressing this, but I have a
problem with the way lists are represented when converted to strings.

Lets say my dictionary is

data = {"fname": "todd", "lname": "Bush"}
fields = data.keys()
vals = []
for v in fields:
vals.append(data[v])

sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)

but fields and vals are represented as lists. So, then I need to strip the
[] from them, but then ... there must be an easier way.

Any advise?

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

David Bear

David Bear wrote:

> The dictionary keys are the field names. The values are the values to be
> inserted.
>
> I am looking for a good pythonic way of expressing this, but I have a
> problem with the way lists are represented when converted to strings.
>
> Lets say my dictionary is
>
> data = {"fname": "todd", "lname": "Bush"}
> fields = data.keys()
> vals = []
> for v in fields:
> vals.append(data[v])
>
> sql = """INSERT INTO table (%s) VALUES (%s);""" % (fields, vals)
>
> but fields and vals are represented as lists. So, then I need to strip the
> [] from them, but then ... there must be an easier way.
>
> Any advise?

1) use parameters to pass in the values (see
http://www.python.org/peps/pep-0249.html )

2) use parameters to pass in values

3) use parameters to pass in values

4) here's a simplified version of your code:

data = {"fname": "todd", "lname": "Bush"}

fields = data.keys()
values = data.values()

cursor.execute(
"INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)),
*values
)

(this assumes that your database uses %s for parameters; if it uses
? instead, replace "%%s" with "?". see the paramstyle documentation
in the db-api docs (pep 249) for more info)

</F>

Fredrik Lundh

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. 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


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

David Bear

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>

Fredrik Lundh

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 --

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 --

David Bear

On Mon, 05 Dec 2005 18:00:21 -0700, David Bear wrote
> Fredrik Lundh wrote:
> > 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)

It appears that Fredrik gave you good advice but bad example code. The example
he gave you constructs an insert query with only one parameter placeholder.
You'll need as many placeholders as the number of values that are inserted.

The following example should work better:

def insertDict(curs, tablename, data):
fields = data.keys()
values = data.values()
placeholder = "%s"
fieldlist = ",".join(fields)
placeholderlist = ",".join([placeholder] * len(fields))
query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
placeholderlist)
curs.execute(query, values)

The main thing to note here is that we *are* using string formatting to build
a query that's based on a variable table name and a variable column list, but
we *are not* using string formatting to fill in the values.[*]

On a somewhat related note, it's unfortunate that many database modules use %s
as parameter placeholders, because it makes it too tempting to write bad code
such as

cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad, uses
vulnerable and error-prone string formatting

instead of

cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good, uses
parameters.
[*] This code blindly trusts that the table name and dictionary keys don't
contain SQL injection attacks. If the source of these is not completely
trustworthy, the code needs to be hardened against such attacks. I'll leave
that as an exercise for the reader.

Hope this helps,

Carsten.

Carsten Haese

Carsten Haese wrote:

> The
> example he gave you constructs an insert query with only one parameter
> placeholder. You'll need as many placeholders as the number of values that
> are inserted.
>
> The following example should work better:
>
> def insertDict(curs, tablename, data):
> fields = data.keys()
> values = data.values()
> placeholder = "%s"
> fieldlist = ",".join(fields)
> placeholderlist = ",".join([placeholder] * len(fields))
> query = "insert into %s(%s) values (%s)" % (tablename, fieldlist,
> placeholderlist)
> curs.execute(query, values)
>
> The main thing to note here is that we *are* using string formatting to
> build a query that's based on a variable table name and a variable column
> list, but we *are not* using string formatting to fill in the values.[*]
>
> On a somewhat related note, it's unfortunate that many database modules
> use %s
> as parameter placeholders, because it makes it too tempting to write bad
> code
> such as
>
> cur.execute("insert into tab1(spam,eggs) values (%s,%s)" % (a,b)) # Bad,
> uses vulnerable and error-prone string formatting
>
> instead of
>
> cur.execute("insert into tab1(spam,eggs) values (%s,%s)", (a,b)) # Good,
> uses parameters.
>
>[*] This code blindly trusts that the table name and dictionary keys don't
> contain SQL injection attacks. If the source of these is not completely
> trustworthy, the code needs to be hardened against such attacks. I'll
> leave that as an exercise for the reader.
>
> Hope this helps,
>
> Carsten.

Thank you very much for the greater explanation. Yes, I was not
understanding that that %s in one instance was a python string format
operator, and in another instance it was a placeholder sent to the dbapi
objects (and I supposed on down into the data base cursor) for the
parameters following the function call. BIG DIFFERENCE.

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

David Bear