python sqlite问题-插入方法 [英] python sqlite question - Insert method
问题描述
在下面的代码中,row
是一个包含 200 个元素(数字)的元组,listOfVars
是一个包含 200 个字符串的元组,它们是 testTable代码>.
tupleForm
是一个元组列表,每个元组中有 200 个元素.
In the code below row
is a tuple of 200 elements (numbers) and listOfVars
is a tuple of 200 strings that are variable names in the testTable
. tupleForm
is a list of tuples, 200 elements in each tuple.
以下代码不起作用.它返回一个语法错误:
The following code does not work. It returns a syntax error:
for row in tupleForm:
cmd = '''INSERT INTO testTable listOfVars values row'''
cur.execute(cmd)
但是,以下工作正常.有人可以解释为什么吗?我发现 sqlite 太不直观了.
However, the following works fine. Can someone explain why? I am finding sqlite so non-intuitive.
for row in tupleForm:
cmd = '''INSERT INTO testTable %s values %s'''%(listOfVars, row)
cur.execute(cmd)
感谢您的帮助.
推荐答案
insert_query = '''INSERT INTO testTable ({0}) VALUES ({1})'''.format(
(','.join(listOfVars)), ','.join('?'*len(listOfVars)))
cur.executemany(insert_query, tupleForm)
请不要对数据库查询使用普通的字符串插值.
我们很幸运有 DB-API 详细解释了如何做你需要什么.
Please do not use normal string interpolation for database queries.
We are fortunate to have the DB-API which explains in detail how to do what you require.
这种方法比第二次尝试更好的几个简单原因:
A couple of quick reasons why this method is better than your 2nd attempt:
- 通过使用内置的字符串转义工具,我们可以避免 SQL 注入攻击,并且
executemany
接受元组列表作为参数.
- by using the built-in string-escaping tools we avoid SQL injection attacks, and
executemany
accepts a list of tuples as an argument.
这篇关于python sqlite问题-插入方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!