Odtnhj

I am strugeling to insert None values into the date collumn 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 cant 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 idealy 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.

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