Sqlite3 Error: You Did Not Supply A Value For Binding 1

def save(): global editor conn = sqlite3.connect('address_book.db') c = conn.cursor() recordID = delete_box.get() c.execute('''UPDATE addresses SET

Solution 1:

The error is due to the extra colon. Replace

'first:': ef_name.get(), 

with

'first': ef_name.get(),

Solution 2:

Welcome to StackOverflow! I think the error you made is that you have used the parameter subtitution style (paramstyle) that is not the default one on sqlite3. You are trying to use the named style instead of the default qmark style. If you used the qmark style your UPDATE would have looked like this:

c.execute(
    'UPDATE addresses SET first_name=?, last_name=?, address=?, city=?, state=?, zipcode=? WHERE old=?', 
    (ef_name.get(), el_name.get(), eaddress.get(), ecity.get(), 
     estate.get(), ezipcode.get(), int(recordID))
)

But if you really want to use the named paramstyle, you can set that attribute for that module. Here's a complete example:

import sqlite3

sqlite3.paramstyle = 'named'

parameters = [
    {
        'old': 1,
        'first': "Tony",
        'last': "Starks",
        'address': '10880 Malibu Point',
        'city': 'Malibu',
        'state': 'California',
        'zipcode': '12345-6789'
    },
    {
        'old': 1,
        'first': "Pepper",
        'last': "Potts",
        'address': '10880 Malibu Point',
        'city': 'Malibu',
        'state': 'California',
        'zipcode': '12345-6789'
    }
]


conn = sqlite3.connect('address_book.db')
c = conn.cursor()

c.execute('CREATE TABLE addresses (old, first_name, last_name, address, city, state, zipcode)')
c.execute('INSERT INTO addresses  VALUES (:old, :first, :last, :address, :city, :state, :zipcode)', parameters[0])
conn.commit()

print('After INSERT')
for row in c.execute('SELECT * FROM addresses'):
    print(row)

c.execute('UPDATE addresses SET first_name=:first, last_name=:last, address=:address, city=:city, state=:state, zipcode=:zipcode WHERE old=:old', parameters[1])
conn.commit()

print('After UPDATE')
for row in c.execute('SELECT * FROM addresses'):
    print(row)

conn.close()

Solution 3:

To avoid the situation reported as https://bugs.python.org/issue41638 i am using a wrapper. See also python sqlite insert named parameters or null for the solution of the more general problem on how to store dicts with different sets of keys which need to be mapped to different colums.

def testBindingError(self):
        '''
        test list of Records with incomplete record leading to
        "You did not supply a value for binding 2"
        see https://bugs.python.org/issue41638
        '''
        listOfRecords=[{'name':'Pikachu', 'type':'Electric'},{'name':'Raichu' }]
        for executeMany in [True,False]:
            try:
                self.checkListOfRecords(listOfRecords,'Pokemon','name',executeMany=executeMany)
                self.fail("There should be an exception")
            except Exception as ex:
                if self.debug:
                    print(str(ex))
                self.assertTrue('no value supplied for column' in str(ex)) 

which leads to:

executeMany:

INSERT INTO Pokemon (name,type) values (:name,:type)
failed: no value supplied for column 'type'

no executeMany:

INSERT INTO Pokemon (name,type) values (:name,:type)
failed: no value supplied for column 'type'
record  #2={'name': 'Raichu'}

The wrapper code is:

def store(self,listOfRecords,entityInfo,executeMany=False):
        '''
        store the given list of records based on the given entityInfo
        
        Args:
          
           listOfRecords(list): the list of Dicts to be stored
           entityInfo(EntityInfo): the meta data to be used for storing
        '''
        insertCmd=entityInfo.insertCmd
        try:
            if executeMany:
                self.c.executemany(insertCmd,listOfRecords)
            else:
                index=0
                for record in listOfRecords:
                    index+=1
                    self.c.execute(insertCmd,record)
            self.c.commit()
        except sqlite3.ProgrammingError as pe:
            msg=pe.args[0]
            if "You did not supply a value for binding" in msg:
                columnIndex=int(re.findall(r'\d+',msg)[0])
                columnName=list(entityInfo.typeMap.keys())[columnIndex-1]
                debugInfo=""
                if not executeMany:
                    if self.errorDebug:
                        debugInfo="\nrecord  #%d=%s" % (index,repr(record))
                raise Exception("%s\nfailed: no value supplied for column '%s'%s" % (insertCmd,columnName,debugInfo))
            else:
                raise pe

for more details see storage/sql.py and tests/testSqlite3.py