python sqlite query that only updates values if the variables used are not NULL

Question

My sqlite query is updating columns with the empty variables that are being passed in instead of ignoring the empty variables and keeping the current value in the column.

My program askes the user to input info into each of those variables or leave blank if that column does not need to be changed. Variables left blank need to be ignored and the current value in the column needs to kept. This is my way of letting the user update multiple columns at once instead of only updating one at a time and then having to start over.

I am using python 3 and sqlite3.

Here is the code chunk that I tried running.

                c.execute('''UPDATE filaments SET brand= CASE WHEN ? IS NOT NULL THEN ? ELSE brand END, 
                            material= CASE WHEN ? IS NOT NULL THEN ? ELSE material END, 
                            color= CASE WHEN ? IS NOT NULL THEN ? ELSE color END, 
                            spools= CASE WHEN ? IS NOT NULL THEN ? ELSE spools END,
                            size= CASE WHEN ? IS NOT NULL THEN ? ELSE size END,
                            weight= CASE WHEN ? IS NOT NULL THEN ? ELSE weight END,
                            price= CASE WHEN ? IS NOT NULL THEN ? ELSE price END 
                            WHERE id = ?''', (b,b,m,m,co,co,sc,sc,ss,ss,sw,sw,p,p,slct))
                conn.commit()
                print("Process complete!")

Answer 1

Just to have the answer here, make sure that you're passing None instead of empty strings.

Also it would be much cleaner if instead of the case statement you use COALESCE.

size = COALESCE(?, size)

This way you reduce the parameters to the query in half as you specify one column only once.