Inserting None value into Date column from python to PostgreSQL database using PygreSQL v5.0.6

I am struggling to insert None values into the date column of a PostgreSQL database from python using PygreSQL v5.0.6.

Some code:

def _update_traits_db(self, code, date_start, date_end, unit):
    sql = ("Insert into traits (code, date_start, date_end, unit) "
           "VALUES ('%s', '%s', '%s', '%s') "
           "ON CONFLICT (code) DO UPDATE "
           "SET date_start = excluded.date_start, date_end = excluded.date_end, unit = excluded.unit "
           % (code, date_start, date_end, unit))
    try:
        self._connect()
        self._cur.execute(sql)
        self._con.commit()
        self._close()
    except Exception as e:
        self._close()
        raise e

There are a couple issues I am facing, the biggest being the the possibility of None values for date_end and unit, the first being a date causing SQL errors like:

ERROR: invalid input syntax for type date: "None"
LINE 1: ...d, unit) VALUES ('AWGHT', '2003-01-29T23:00:00Z', 'None', 'N... ^ If I replace the none value with a hardcoded NULL then it works but from reading around I figured it should be handled py PyGreSQL automatically converting None to NULL but I can't get that to work.

A second issue is with None values in the unit column, this is supposed to be a string but None is now stored in the database where it ideally would be a NULL value. I have tried removing the quotes from the around the '%s' for unit in the query vut that only causes SQL error on the None value.

I am fairly new to Python and PostgreSQL so there are many potential places i might have messed up so all suggestions are more than welcome.

Solution

Simply use parameterization, the industry standard to separate SQL code from data values, and not string interpolation which looks similar since the placeholder %s is used in both. With this approach, None should resolve as NULL. In fact, PygreSQL docs even warns users on the practice:

Warning
Remember to never insert parameters directly into your queries using the % operator. Always pass the parameters separately.

Consider following adjustment using unquoted %s placeholders (see docs) with values later binded in cursor.execute() call:

def _update_traits_db(self, code, date_start, date_end, unit):
    # PREPARED STATEMENT (NO DATA VALUES INTERPOLATED)
    sql =  """INSERT INTO traits (code, date_start, date_end, unit)
              VALUES (%s, %s, %s, %s)
              ON CONFLICT (code) DO UPDATE
              SET date_start = excluded.date_start, 
                  date_end = excluded.date_end, 
                  unit = excluded.unit
           """
    try:
        self._connect()
        # BIND PARAMETERS WITH TUPLE OF VALUES
        self._cur.execute(sql, (code, date_start, date_end, unit))
        self._con.commit()

    except Exception as e:
        raise e

    finally:
        self._close()