insert a dictionary into sql data base

[David Bear]

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?

 

[Fredrik Lundh]

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>

 

[David Bear]

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:
*values)
File "<stdin>", line 1
print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields),
*values)
^
SyntaxError: invalid syntax

 

[Fredrik Lundh]

David Bear wrote

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:

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

 

[David Bear]

David Bear wrote


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.

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?

 

[David Bear]

David Bear wrote


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.


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):
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
(%s);'
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES
($s);'['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
Traceback (most recent call last):
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)

 

[Carsten Haese]

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):
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address)
VALUES
(%s);'
'INSERT INTO nic (addr_code,ip_address,property_control,mac_address)
VALUES
($s);'['p', '129.219.120.134', '6154856', '00:40:50:60:03:02']
Traceback (most recent call last):
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)[/QUOTE]

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.

 

[David Bear]

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.