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.

You don't need the 's around the placeholders... just use %s by itself... eg: values (%s, %s, %s, %s) - the engine will already correctly escape strings, None will be converted to null etc... — Jon Clements
– Jon Clements, Commented Nov 13, 2018 at 14:19
Tried this now, but am getting an error on the date due to the date format i believe, the date input is this " 2003-01-29T23:00:00Z" so i get ERROR: syntax error at or near "T23" — Kevin
– Kevin, Commented Nov 13, 2018 at 14:23
So your date is a string then? If you make it a datetime object instead - it'll work fine. — Jon Clements
– Jon Clements, Commented Nov 13, 2018 at 14:40
Have converted the date strings to datetime objects and that works and can be inserted into the database, I cant find a way to convert a "None" variable to a datetime though and for some reason its still not converted into NULL when i try inserting it to the database. Same thing goes for the "Unit" value. — Kevin
– Kevin, Commented Nov 13, 2018 at 15:13
After removing the quotes around the values like @JonClements sugested i also get an error on the code column. "ERROR: column "testCode" does not exist" so it does not seem to escape the strings on its own. — Kevin
– Kevin, Commented Nov 13, 2018 at 15:15

Parfait · Accepted Answer · 2018-11-13 15:59:54Z

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

answered Nov 13, 2018 at 15:59

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Over a year ago

That did it! Thanks a lot to both of you! Seems i had a misunderstanding on how the parameterization in python really worked, this makes a lot more sense. Glad to have that cleared up, thanks!

Parfait Over a year ago

Great to hear. Yes, this cursor.execute(query, params) approach is consistent across most Python DB-APIs (cxOracle, ibmdb, pymssql, pyodbc, pymysql, psycopg2, sqlite3) per Python's PEP 249 SQL standard. Also, parameterization is an industry-wide best practice not just in Python but any app layer language (Java, C#, PHP, Perl, etc.) running SQL. Happy coding!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related