Python sqlite3 how to dynamically search for null-CodePudding

I am try select, modify, or delete rows using sqlite3 which can have a null value in a column. I am also trying to this dynamically so I can search for null value by using the Python literal None.

This works just fine:

name = "John"
cursor.execute("DELETE FROM users WHERE name = ?", (name,))

However, this does not work:

name = None
cursor.execute("DELETE FROM users WHERE name = ?", (name,))

I understand that SQL is strange and I have to do is null, but is there an elegant (and safe) way of doing this in one line?

CodePudding user response：

While you could conjure up a one-liner to construct the right query, it's simpler to branch based on the value.

if name is None:
    stmt = 'DELETE FROM users WHERE name IS NULL'
    cursor.execute(stmt)
else:
    stmt = 'DELETE FROM users WHERE name = ?'
    cursor.execute(stmt, (name,))

If you need something more dynamic - for example multiple columns in the WHERE clause, any one of which might be NULL - I would recommend using a query-building tool or an ORM rather than trying to handle all the permutations yourself.

CodePudding user response：

While I'd go with safety and run your DELETE FROM as two separate statements, with the second explicitly using DELETE FROM users WHERE name = null, it is possible to do what you want:

if inputname = None:
    name = "null"
else:
    name = inputname

cursor.execute(f"""DELETE FROM users WHERE name = '{name}' """)

If you're after a C-like ternary operator, you need at least Python 3.10 iirc - but I'd avoid that in favour of making your operations very clear for whoever has to maintain this code after you.