confusing SQLite3 Error

Question

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!

This is a typographic error. Simply, remove the space and colon in parameter dictionary keys. Also, IDs are usually auto-generated. — Parfait
– Parfait, Commented Aug 5, 2018 at 13:09
Yeah, i changed it just like you said, I also just printed self.AddEmployee.ID.get() as an test and got the right data. Im starting to think its not even my fault this error is occurring lol — YouKnowMe
– YouKnowMe, Commented Aug 5, 2018 at 16:36
@Parfait It was just ID but i just changed it to IDNumber which still gave me the same annoying error :( — YouKnowMe
– YouKnowMe, Commented Aug 5, 2018 at 19:07
@Parfait check out my code if you want. - github.com/Sharjeel50/Database-System/blob/master/… — YouKnowMe
– YouKnowMe, Commented Aug 5, 2018 at 19:11

Parfait · Accepted Answer · 2018-08-06 14:11:41Z

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

Collectives™ on Stack Overflow

confusing SQLite3 Error

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related