Issue with replacing NULL sqlite3 database column values with other types in Python 3?-CodePudding

I've run into a problem with the sqlite3 module in Python 3, where I can't seem to figure out how to replace NULL values from the database with other ones, mainly strings and integers.

This command doesn't do the job, but also raises no exceptions: UPDATE table SET animal='cat' WHERE animal=NULL AND id=32

The database table column "animal" is of type TEXT and gets filled with NULLs where no other value has been specified. The column "id" is primary keyed and thus features only unique integer row indices.

If the column "animal" is defined, not NULL, the above command works flawlessly. I can replace existing strings, integers, and floats with it.

What am I overlooking here?

Thanks.

CodePudding user response：

The NULL value in SQL is special, and to compare values against it you need to use the IS and IS NOT operators. So your query should be this:

UPDATE table
SET animal = 'cat'
WHERE animal IS NULL AND id = 32;

NULL by definition means "unknown" in SQL, and so comparing a column directly against it with = also produces an unknown result.