将python字典插入SQLite数据库 [英] Insert a python dict into an SQLite DB

问题描述

我有一个字典，想要将所有值添加到sqlite数据库中.词典中的所有键都存在于数据库中，并且所有键的类型均为字符串.但是，在将值输入数据库时​​遇到了麻烦.每当下面的代码遇到字符串中带有"的字符串时，它们都是丑陋的，不安全的，并且会出错，但是可以正常工作.

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)

但是会引发类型错误.

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.也许值得一提的是，我正在使用Python 2.5.1.

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

推荐答案

Afaik，当查询中包含?"时占位符execute()方法会根据参数类型自动进行正确的转义.因此，以下方法应该起作用:

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)

这篇关于将python字典插入SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！