CODEWITHSUNDEEP
pythonpython-3.xsqlite
YouKnowMe
YouKnowMe

Reputation: 45

confusing SQLite3 Error

Ive made a database in python with SQLite3 and im trying to add some data but im currently getting the error You did not supply a value for binding 1.. Ive made the exact same database (with different values) before but did not get this error, what am i doing wrong here?

This is the database with the error:

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()

And this is the old one that did not get an error. 

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':"[email protected]" , 'Hours':"230"})

        self.conn.commit()

-------------------------------------------------------- Answer ----------------------------------------------------------------

So i fixed the problem by changing the layout to this and it worked:

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()

I found the solution at: sqlite3.OperationalError: no such column:

Thank you to everyone that has replied and tried to help!

Upvotes: 0

Views: 924

Answers (1)

Parfait
Parfait

Reputation: 107652

For future readers as docs show, the Python sqlite3 DB-API supports both qmark and named styles for parameterization. OP resolved issue using qmark style. However, OP's original attempt can indeed work with proper dictionary key assignment where the dictionary keys correspond exactly to the named parameters.

Specifically, the following dictionary will fail to bind to named parameters (:ID, :FirstName, :Surname, ...) due to the colon and space within dictionary keys:

{'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()}

However, the following should work for the same prepared SQL statement:

{'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()}

Altogether, for cursor call:

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()

Upvotes: 1

Related Questions