python - 令人困惑的 SQLite3 错误
问题描述
我已经用 SQLite3 在 python 中创建了一个数据库,我试图添加一些数据,但我目前遇到了错误You did not supply a value for binding 1.
。我之前制作了完全相同的数据库(具有不同的值),但没有收到此错误,我在这里做错了什么?
这是有错误的数据库:
def Add_User_Data(self):
self.c.execute("INSERT INTO Employees VALUES (:ID, :FirstName, :Surname, :DOB, :Age, :Gender)",
{'ID: ': self.AddEmployee.ID.get(), 'FirstName: ':self.AddEmployee.FirstName.get(), 'Surname: ':self.AddEmployee.Surname.get(),
'DOB: ':self.AddEmployee.DOB.get(), 'Age: ':self.AddEmployee.Age.get(), 'Gender: ':self.AddEmployee.Gender.get()})
self.conn.commit()
这是没有出错的旧版本。
def addEmployees(self):
self.c.execute("INSERT INTO Employees VALUES (:FirstName, :Surname, :Age, :Postcode, :Wage, :Email, :Hours)",
{'FirstName': 'aa', 'Surname':"Jan" , 'Age':"21" ,
'Postcode':"UB5 7T3" , 'Wage':"1220000" , 'Email':"s@gmail.com" , 'Hours':"230"})
self.conn.commit()
-------------------------------------------------- - - - 回答 - - - - - - - - - - - - - - - - - - - - - - ---------------------
所以我通过将布局更改为这个来解决问题并且它有效:
self.params = (self.AddEmployee.ID.get(), self.AddEmployee.FirstName.get(), self.AddEmployee.Surname.get(),
self.AddEmployee.DOB.get(), self.AddEmployee.Age.get(), self.AddEmployee.Gender.get())
self.c.execute("INSERT INTO Employees VALUES (?, ?, ?, ?, ?, ? )", self.params)
self.conn.commit()
我在以下位置找到了解决方案:sqlite3.OperationalError: no such column:
感谢所有回复并试图提供帮助的人!
解决方案
对于文档显示的未来读者,Python sqlite3 DB-API 支持qmark和命名样式进行参数化。OP 使用qmark样式解决了问题。但是,OP 的原始尝试确实可以使用正确的字典键分配,其中字典键与命名参数完全对应。
:ID, :FirstName, :Surname, ...
具体来说,由于字典键中的冒号和空格,以下字典将无法绑定到命名参数 ( ):
{'ID: ': self.AddEmployee.ID.get(),
'FirstName: ':self.AddEmployee.FirstName.get(),
'Surname: ':self.AddEmployee.Surname.get(),
'DOB: ':self.AddEmployee.DOB.get(),
'Age: ':self.AddEmployee.Age.get(),
'Gender: ':self.AddEmployee.Gender.get()}
但是,以下内容应该适用于相同的准备好的 SQL 语句:
{'ID': self.AddEmployee.ID.get(),
'FirstName': self.AddEmployee.FirstName.get(),
'Surname': self.AddEmployee.Surname.get(),
'DOB': self.AddEmployee.DOB.get(),
'Age': self.AddEmployee.Age.get(),
'Gender': self.AddEmployee.Gender.get()}
总之,对于光标调用:
self.sql = "INSERT INTO Employees VALUES (:ID, :FirstName, :Surname, :DOB, :Age, :Gender)"
self.qparams = {'ID': self.AddEmployee.ID.get(),
'FirstName': self.AddEmployee.FirstName.get(),
'Surname': self.AddEmployee.Surname.get(),
'DOB': self.AddEmployee.DOB.get(),
'Age': self.AddEmployee.Age.get(),
'Gender': self.AddEmployee.Gender.get()}
self.c.execute(self.sql, self.qparams)
self.conn.commit()