Python 3 SQLite3-绑定数不正确 [英] Python 3 SQLite3 - Incorrect number of bindings

问题描述

我了解Stack Overflow上有很多与此错误有关的问题，但是我尝试了许多解决方案，显然它们都失败了。

以下是列表：

sqlite3.ProgrammingError：提供的绑定数量不正确。当前语句使用1，并且提供了74个

SQLite参数替换问题

使用SQLite和Python从数据库中读取：提供的绑定数不正确

SQLite Python插入-提供的绑定数量不正确

我正在尝试存储用户名和哈希密码（由PassLib创建- https://pythonhosted.org/passlib/ ）在SQLite3数据库中。这些分别存储在变量 targetusername和 password中。我的问题是，当我实际尝试将这2个变量插入到名为 Passwords的数据库表中时，出现此错误：

 提供的绑定数不正确。当前语句使用1，并且提供了11。 
  

下面是目标用户名和密码存储的示例：

  targetusername = user4884072 
密码= $ 5 $ rounds = 535000 $ ySH31paWMjEDCUUY $ jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4 
  
 
 
此行显示错误：
  c.executemany （插入到{tn}（{idf}，{cn}）VALUES（targetusername，％s％str（password）。\ 
 format（tn = Passwords））
  
已多次更改以尝试解决此问题（这显然是由Python存储变量的方式引起的），但这是原来是什么：
  c.execute（ INSERT OR IGNORE INTO {tn}（{idf}，{cn}） VALUES（目标用户名，密码）。\ 
格式（tn = Passwords，idf = Username，cn = Password））
  
 
 
解决方案
使用 c.execute（），而不是 c.executemany（），以插入一行数据。 
 
 
 
除此之外，请勿使用字符串替换，而应使用参数化查询。这是一个完整的工作示例：
  import sqlite3 
 
 connection = sqlite3.connect（'：memory ：'）＃在内存数据库中
c = connection.cursor（）
 
 c.execute（'创建表密码（用户名文本，密码文本）'）
 
 targetusername = user4884072 
密码= $ 5 $ rounds = 535000 $ ySH31paWMjEDCUUY $ jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4 
 c.execute（'插入密码（用户名，密码）（？，（？ targetusername，password））
 print c.execute（'select * from Passwords'）。fetchall（）
  
输出：
 
 [[u'user4884072'，u'$ 5 $ rounds = 535000 $ ySH31paWMjEDCUUY $ jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4。） ] 
 
在您发布的代码中，用表或列名称替换值没有意义，因此只需将它们放在查询字符串，如图所示。
 
 
 
这将使用参数化查询，API会在其中插入要使用的值在查询中用？表示的位置输入rname和密码。比使用字符串替换更安全，因为DB API知道如何正确安全地转义传递给它的值，并且避免了对代码的SQL注入攻击。
 
 
 
因为只插入了一行数据，所以它使用 execute（）而不是 executemany（）。
 
I understand there are MANY questions on Stack Overflow about this error but I have tried MANY solutions and obviously they have all failed.

Here's a list:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied

SQLite parameter substitution problem

sqlite3.ProgrammingError: Incorrect number of bindings supplied

Reading from database with SQLite and Python: Incorrect number of binding supplied

SQLite Python Insert - Incorrect Number of Bindings Supplied

I am trying to store a username and hashed password (created by PassLib - https://pythonhosted.org/passlib/ ) in a SQLite3 database. These are stored in the variables "targetusername" and "password" respectively. My problem is that when I actually try to insert these 2 variables into a table of a database called "Passwords", it gives this error:
Incorrect number of bindings supplied. The current statement uses 1, and there are 11 supplied.
Here's an example of what targetusername and password would store:
targetusername = "user4884072"
password = "$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4"
This line gives the error:
c.executemany("INSERT INTO {tn} ({idf}, {cn}) VALUES(targetusername, %s" % str(password).\
format(tn="Passwords"))
It has been changed multiple times to try and fix the issue (which apparently is caused by how Python stores variables), but here is what it was originally:
c.execute("INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES (targetusername, password)".\
format(tn="Passwords", idf="Username", cn="Password"))

解决方案
Use c.execute(), not c.executemany(), to insert a single row of data. This is the immediate cause of the error that you experience.

In addition to that, don't use string substitution, use parameterised queries. Here is a complete working example:
import sqlite3

connection = sqlite3.connect(':memory:')    # in memory database
c = connection.cursor()

c.execute('create table Passwords (Username text, Password text)')

targetusername = "user4884072"
password = "$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4"
c.execute('insert into Passwords (Username, Password) values (?, ?)', (targetusername, password))
print c.execute('select * from Passwords').fetchall()
Output:
[(u'user4884072', u'$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4')]
In the code that you have posted there is no point in substituting values for the table or column names, so just put them in the query string as shown.

This uses a parameterised query where the API inserts the values for username and password into the query at the places denoted by ?. This is safer than using string substitution because the DB API knows how to properly and safely escape the values passed to it, and this avoids SQL injection attacks on your code.

And it uses execute() rather than executemany() since there is only one row of data being inserted.

                        
这篇关于Python 3 SQLite3-绑定数不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！