python sqlite问题

本文介绍了python sqlite问题-插入方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在下面的代码中，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.