PostgreSQL(含PostGIS)表Python插入失败时插入部分字段+NULL的问题

错误原因分析 #

1. 第一个TypeError错误
   你调用了sql.Identifier('geometry').as_string(conn)，返回的是字符串类型，但sql.SQL.format()要求传入Composable类型（如SQL或Identifier实例），而非普通字符串，因此触发类型错误。

2. 第二个AttributeError错误
   psycopg2.extensions模块不存在ARRAY属性，你误引用了错误的属性路径；且通过cursor.description的type_code判断字段类型不够可靠，容易出现类型映射偏差。

3. 隐藏的参数不匹配问题
   你之前的代码中，为后面的字段添加了(None,)参数，但这些位置已经在SQL语句中写死了NULL，并非占位符，会导致参数数量与占位符数量不匹配，引发潜在执行错误。

修正后的可行代码 #

核心思路：通过information_schema查询表字段的准确类型，针对geometry字段显式指定NULL类型，其他字段直接使用NULL，同时保证参数数量与占位符完全匹配。

try: 
    # 首次插入逻辑
    # your original insert code here
except Exception as e:
    logging.error(f".ERROR inserting record {record_for_db[0]} to table {table_name}:")
    logging.error(str(e))
    conn.rollback()

    try:
        # 1. 从information_schema获取字段类型信息
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT column_name, data_type 
                FROM information_schema.columns 
                WHERE table_name = %s AND table_schema = current_schema()
                ORDER BY ordinal_position
            """, (table_name,))
            column_info = cursor.fetchall()
        column_type_map = {col[0]: col[1] for col in column_info}

        # 2. 构建占位符：前4个用参数占位，其余字段根据类型生成NULL
        placeholders = []
        for idx, column in enumerate(table_columns):
            if idx < 4:
                placeholders.append(sql.Placeholder())
            else:
                col_type = column_type_map.get(column)
                if col_type == 'geometry':
                    # geometry类型显式指定NULL的类型
                    placeholders.append(sql.SQL('NULL::{}').format(sql.Identifier('geometry')))
                else:
                    # 其他类型直接用NULL，PostgreSQL会自动推断类型
                    placeholders.append(sql.SQL('NULL'))
        placeholders = sql.SQL(', ').join(placeholders)

        # 3. 构建插入SQL
        query = sql.SQL("INSERT INTO {} ({}) VALUES ({}) ON CONFLICT DO NOTHING").format(
            sql.Identifier(table_name),
            sql.SQL(', ').join(sql.Identifier(col) for col in table_columns),
            placeholders
        )

        # 4. 仅传入前4个字段的值，其余字段已通过SQL设置为NULL
        values = record_for_db[:4]
        logging.info("..retry values: " + str(values))

        with conn.cursor() as single_cursor:
            # 直接传入query对象，无需调用as_string
            single_cursor.execute(query, values)
            conn.commit()
            logging.info(f"..successfully inserted fallback record {record_for_db[0]}")

    except Exception as e:
        logging.error(f"..ERROR inserting fallback record {record_for_db[0]} to table {table_name}:")
        logging.error(str(e))
        conn.rollback()

关键优化点 #

• 用information_schema获取字段类型，避免依赖psycopg2内部常量，兼容性更强
• 移除多余的None参数，保证参数数量与占位符完全匹配
• 仅对geometry字段显式指定NULL::geometry，其他字段依赖PostgreSQL自动类型推断，简化逻辑
• 直接传递query对象给execute，无需手动调用as_string，避免字符串拼接错误

内容的提问来源于stack exchange，提问作者average.everyman