python - 使用 PygreSQL v5.0.6 将 None 值插入从 python 到 PostgreSQL 数据库的 Date 列

Question

我正在努力使用 PygreSQL v5.0.6 从 python 将 None 值插入到 PostgreSQL 数据库的日期列中。

部分代码:

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

我面临几个问题，最大的问题是 date_end 和 unit 的 None 值的可能性，第一个是导致 SQL 错误的日期，例如:

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.

第二个问题是 unit 列中的 None 值，这应该是一个字符串，但 None 现在存储在理想情况下它是 NULL 值的数据库。我已经尝试从查询 vut 中的单位 '%s' 周围删除引号，这只会导致 None 值出现 SQL 错误。

我是 Python 和 PostgreSQL 的新手，所以有很多地方我可能搞砸了，所以非常欢迎所有建议。

Answer 1

简单地使用参数化，这是将 SQL 代码与数据值分开的行业标准，而不是看起来相似的字符串插值，因为两者都使用了占位符 %s。使用这种方法，None 应该解析为 NULL。事实上，PygreSQL docs甚至警告用户这种做法:

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

考虑使用不带引号的 %s 占位符(参见 docs )进行以下调整，之后在 cursor.execute() 调用中绑定(bind)值:

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