Insert a python dict into an SQLite DB

Question

I have a dictionary that I want to add all the values to an sqlite database. All the keys in the dictionary exist in the database, and all the keys are of type string. Yet, I am having trouble with getting the values into the database. The following code is ugly, insecure, and errors whenever it comes across a string with a " in it, but it sort of works.

Query="INSERT INTO packages VALUES("
    for tag in Tags:
       Query=Query + '"' + Package[tag] + '", '
    Query=Query[:-2]+")"
cursor.execute(Query)

How can I elegantly fix this so that it is secure and accepts inputs with " in the string? I've come across a handful of other methods. For example:

fields = Package.keys()
values = Package.values()
query = "INSERT INTO packages (%s) VALUES (%%s);" % (",".join(fields))
cursor.execute(query, values)

but it throws a type error.

TypeError: function takes at most 2 arguments (38 given)

The most elegant solution I have come across so far appears to be

sql_insert = ('INSERT INTO packages (%s) VALUES (%s)' % 
             (','.join('%s' % name for name in Package),
             ','.join('%%(%s)s' % name for name in Package)))
cursor.execute(sql_insert, Package)

but it throws an operational error, saying

sqlite3.OperationalError: near "%": syntax error

Once again, my question is how can I elegantly safely add the values from a dictionary to a database?

P.S. It may also be worthy to note that I am using Python 2.5.1.

real4x · Accepted Answer · 2012-01-11 04:55:45Z

3

Afaik, when query has a "?" placeholder execute() method does right escaping automatically basing on argument types. So, the following should work:

query = 'INSERT INTO packages VALUES(%s)' % ','.join(['?'] * len(Tags))
cursor.execute(query, Tags)

answered Jan 11, 2012 at 4:55

real4x

1,47312 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Tolonen Over a year ago

Except, if Tags is a dictionary as the OP is using, Tags will just pass the key names and they will be in arbitrary order.

Trcx Over a year ago

Unfortunately this didn't actually work as it created DB entries with the field name as the data value. I thought it was working, because this script was running without throwing errors, how ever when I looked at the database this morning (let it run overnight) it did not have the appropriate values.

Trcx Over a year ago

In the end I ended up converting the dictionary values to a list, and then inserting them with code similar to this answer.

moooff · Accepted Answer · 2013-08-23 09:33:12Z

2

I have come across the same problem however i had the problem that not all dictionary entries contained all columns of the table therefore i created the following solution

keys, values = zip(*Package.items())
insert_str =   "INSERT INTO packages (%s) values (%s)" % (",".join(keys),",".join(['?']*len(keys)))
cursor.execute(insert_str,values)

answered Aug 23, 2013 at 9:33

moooff

211 bronze badge

Collectives™ on Stack Overflow

Insert a python dict into an SQLite DB

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related