python - 在 Redshift 表中为 SMALLINT 列插入 NULL 值时出现 "Error: invalid input syntax for integer:"？

Question

我有这个本地定义的 python 函数，它在将数据插入 redshift 表时工作正常:

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 中的列相同(尽管我没有明确断言/检查这个功能)。但是，我找不到为 smallint 列插入 NULL 值的方法。这是相关表的架构(在创建表时未将 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".

对于它的值(value)，这是在 INSERT 语句中经过清理的行最终看起来的样子:('bigint_value', 'dt_value', 'NULL', 'NULL', '双值'、'bigint_value'、'string_name')

Answer 1

您所采取的方法本身就很危险。使用字符串连接和格式构造查询容易出错且不安全 - 您使查询容易受到 SQL injection attacks 的攻击.

相反，正确地参数化您的查询，将参数列表作为单独的参数传递给 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() - 看看 this answer .

回到您最初的问题 - 如果您采用这种方法，None 占位符值将被数据库自动转换为 'NULL' 字符串司机。