insert a dictionary into sql data base

[David Bear]

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