CODEWITHSUNDEEP
pythonsqlsqlitesql-update
appleLover
appleLover

Reputation: 15691

Possible Python Sqlite3 Bug

i have a table which has a column "cup" inside table "coffee"

update coffee set cup=cup||'test_add'

this doesnt raise an error but it doesnt add the string 'test_add' to the column! also, the value in column cup is currently nothing. it took me forever that you can't use this syntax adding text when the column has no value to it. this doesn't make sense to me cause the column is defined as a TEXT column, and it has no value, so it seems equivalent to me to

a=""
a+="test_add"

and this syntax works. on the other hand if it is

a=None
a+="test_add"

it throws an error. so it seems to me the code i put at the very top should either add the text or throw an error!!!

Upvotes: 1

Views: 85

Answers (1)

mu is too short
mu is too short

Reputation: 434815

In SQL, doing pretty much anything with NULL gives you NULL back. In particular, this:

NULL || 'test_add'

is NULL. You say that the cup column has no value, that together with the symptoms you describe means that cup IS NULL is true. So your cup || 'test_add' actually does add your string to the column's value, the problem is that NULL || any_string_you_want is NULL.

If you want to treat NULL like an empty string, then use COALESCE:

coalesce(X,Y,...)

The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must be at least 2 arguments.

So you'd do something like this:

update coffee set cup = coalesce(cup, '') || 'test_add'

Python and SQL are different languages, you shouldn't expect Python's None to behave like SQL's NULL. You're not seeing a bug, you're just seeing standard NULL behavior (which often seems like a bug when you're not used to it but that's another story).

Upvotes: 1

Related Questions