Can't update the sqlite3 database in python

Question

I have been making a python project using tkinter. I am also using SQLite3 database(tables). The project has a function to update the database, but the messagebox throws an exceptional error.I tried to remove the exception code but still the database won't get updated.

the database name is: 'criminal_records'.

#update code
    def update_data(self):
        if self.var_case_id.get()=="":
            messagebox.showerror('Error','All fields are required')
        else:
            try:
                update=messagebox.askyesno('Update', "Are you sure you want to update this record?")
                if update>0:
                    conn=sqlite3.connect('criminal_records.db')
                    my_cursor=conn.cursor() 
                    my_cursor.execute('UPDATE criminal_records SET Case ID=?, Criminal Name=?,Case Number=?,Date of crime=?, Crime Type=?, Description=?,Block=?,Location=?,Arrest Date=?, Age=?, Gender=?, WHERE Case Id=?', (
                        self.var_case_id.get(),
                        self.var_name.get(),
                        self.var_case_number.get(),
                        self.var_date_of_crime.get(),
                        self.var_crime_type.get(),
                        self.var_description.get(),
                        self.var_block.get(),
                        self.var_location.get(),
                        self.var_arrest_date.get(),
                        self.var_age.get(),
                        self.var_gender.get(),
                        
                    ))

                else:
                    if not update:
                        return
                conn.commit()
                self.fetch_data()
                self.clear_data()
                conn.close()
                messagebox.showinfo('Successful', 'Criminal record has been updated')
            except Exception as es:
                messagebox.showerror('Error',f'Due to{str(es)}')  

OUTPUT

Answer 1

You have to quote the column names that contain spaces with backticks.

my_cursor.execute('UPDATE criminal_records SET `Case ID`=?, `Criminal Name`=?,`Case Number`=?,`Date of crime`=?, `Crime Type`=?, Description=?,Block=?,Location=?,`Arrest Date`=?, Age=?, Gender=? WHERE `Case Id`=?, (

You also have an extra , before WHERE, and you're missing the value for WHERE `Case ID`=? in the parameter tuple.

Answer 2

The error message in the screen shot comes from the code Due to{str(es)}, meaning that es is "near Case: syntax error" There is a syntax error near Case because your column is Case ID, which includes a space, but that space is not handled in your UPDATE statement (and other columns likewise). You should encapsulate the column in single or double quotes:

UPDATE criminal_records SET "Case ID"=?, "Criminal Name"=?,"Case Number"=?,"Date of crime"=?, "Crime Type"=?, Description=?,Block=?,Location=?,"Arrest Date"=?, Age=?, Gender=? WHERE "Case Id"=?

Note I have also removed the extra comma before WHERE

Answer 3

There are issues in your SQL statement:

• need to enclose those column names with space in double-quote
• extra comma before WHERE

Also the number of values provided (11) does not match the number of placeholders (12) in the SQL statement.

Actually you don't need to update the column "Case ID" because it is the searching condition.

Below is the updated SQL statement with correct order and number of provided values:

my_cursor.execute('''\
    UPDATE criminal_records
    SET "Criminal Name" = ?,
        "Case Number" = ?,
        "Date of crime" = ?,
        "Crime Type" = ?,
        "Description" = ?,
        "Block" = ?,
        "Location" = ?,
        "Arrest Date" = ?,
        "Age" = ?,
        "Gender" = ?
    WHERE "Case ID" = ?''',
    (
        self.var_name.get(),
        self.var_case_number.get(),
        self.var_date_of_crime.get(),
        self.var_crime_type.get(),
        self.var_description.get(),
        self.var_block.get(),
        self.var_location.get(),
        self.var_arrest_date.get(),
        self.var_age.get(),
        self.var_gender.get(),
        self.var_case_id.get(),
    )
)