How to replace None values in Python SQL Lite?

Question

I am working on the 8 Week SQL Challenge with the following data. I am using Python and SQL Lite.

c.execute('DROP TABLE IF EXISTS runner_orders')

c.execute('CREATE TABLE runner_orders (order_id INTEGER, runner_id INTEGER, pickup_time VARCHAR(19), distance VARCHAR(7), duration VARCHAR(10), cancellation VARCHAR(23))')

more_order2 = [ ('1', '1', '2020-01-01 18:15:34', '20km', '32 minutes', ''),
  ('2', '1', '2020-01-01 19:10:54', '20km', '27 minutes', ''),
  ('3', '1', '2020-01-03 00:12:37', '13.4km', '20 mins', 'NULL'),
  ('4', '2', '2020-01-04 13:53:03', '23.4', '40', 'NULL'),
  ('5', '3', '2020-01-08 21:10:57', '10', '15', 'NULL'),
  ('6', '3', 'null', 'null', 'null', 'Restaurant Cancellation'),
  ('7', '2', '2020-01-08 21:30:45', '25km', '25mins', 'null'),
  ('8', '2', '2020-01-10 00:15:02', '23.4 km', '15 minute', 'null'),
  ('9', '2', 'null', 'null', 'null', 'Customer Cancellation'),
  ('10', '1', '2020-01-11 18:50:20', '10km', '10minutes', 'null')]

c.executemany('INSERT INTO runner_orders VALUES (?,?,?,?,?,?)', more_order2)

c.execute(query)

conn.commit()

I am trying to replace the '' values with null with this code:

query = '''
    UPDATE runner_orders
    SET cancellation = null
    WHERE cancellation IN ('NULL', '')
'''

c.execute(query)

query2 = '''
  SELECT * FROM runner_orders
    '''

for match in c.execute(query2):
    print(match)

For some reason, when I output the values, I got None in all the NULL spaces and it does not replace them with null. How do I fix this?

Answer 1

null is not a string. SQL uses trinary logic: true, false, and null. SQL null roughly means "unknown" or "no value".

If you look at your table in the SQLite client you should see the value is null. Python is translating SQL null into the Python equivalent of None.