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

Question

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.

Answer 1

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()