问题描述
我有一个字典，想要将所有值添加到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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！