在红移表中为 SMALLINT 列插入 NULL 值时"Error: invalid input syntax for integer:"？

Question

我具有此本地定义的python函数，该功能在将数据插入红移表中正常工作：

def _insert_data(table_name, values_list):
    insert_base_sql = f"INSERT INTO {table_name} VALUES"
    insert_sql = insert_base_sql + ','.join([str(row) for row in values_list])
    <run_local_python_code_to_execute_insert_sql>

values_list是一个元组列表，每个列表的元素数与table_name中的列相同（即使我没有明确地断言/检查此功能中的元素）。但是，我未能找到一种插入NULL列的CC_3值的方法。这是所讨论表的架构（没有将DEFAULT值分配给表创建的列）：

 schemaname |      tablename      |    column    |         type          | encoding | distkey | sortkey | notnull
------------+---------------------+--------------+-----------------------+----------+---------+---------+---------
 public     | table               | col1         | bigint                | lzo      | t       |       1 | f
 public     | table               | col2         | date                  | lzo      | f       |       2 | f
 public     | table               | col3         | smallint              | lzo      | f       |       3 | f
 public     | table               | col4         | smallint              | lzo      | f       |       4 | f
 public     | table               | col5         | double precision      | none     | f       |       0 | f
 public     | table               | col6         | bigint                | lzo      | f       |       0 | f
 public     | table               | col7         | character varying(48) | bytedict | f       |       0 | f

我专门尝试插入col3和col4的NULL值；我尝试使用''和'NULL'创建元组，但会遇到此错误：Error: invalid input syntax for integer: "NULL"。

对于它的价值，这是INSERT语句中的磨砂行最终看起来的样子：('bigint_value', 'dt_value', 'NULL', 'NULL', 'double_value', 'bigint_value', 'string_name')

Answer 1

您所采用的方法本身很危险。使用字符串串联和格式构建查询是错误的，且不安全 - 您使查询易受SQL注入攻击的影响。

相反，将查询正确参数化，将参数列表作为单独的参数列表传递给cursor.executemany()。这是产生占位持有人的一种，不是很漂亮：

placeholders = ", ".join(["%s"] * len(values_list))
query = f"""
    INSERT INTO 
        {table_name} 
    VALUES
        ({placeholders})
"""
cursor.executemany(query, values_list)

_{（请注意，表名不能被参数化 - 对其进行消毒和验证）}

注意使用executemany()-它将为values_list中的每个元组执行准备的查询语句。

但是，如果您使用的是psycopg2，则有一种更好的方法将多个记录插入表 - execute_values()-查看此答案。

，然后回到您的最初问题 - 如果您采用此方法， None占位符值将通过数据库驱动程序自动转换为'NULL'字符串。。