向PostgreSQL插入数据时遇TypeError: dict is not a sequence错误求助

错误原因 #

psycopg2 的 executemany 方法要求第二个参数是序列的序列（如列表嵌套列表、元组嵌套元组），但你传入的是字典组成的列表，字典不属于序列类型，因此触发该错误。

解决方案 #

以下提供三种可行的解决方式，按需选择：

方式1：将字典转换为值序列

把每个字典提取为对应顺序的元组/列表，确保值的顺序与列名顺序一致：

import psycopg2

def insert_records(data):
    table_name = "your_table_name"  # 替换为实际表名
    conn = psycopg2.connect(
        database = "postgres",
        user = "some_user",
        password = "some_password",
        host = "***rds.amazonaws.com",
        port="5432"
    )
    
    conn.autocommit = True
    cursor = conn.cursor()
    
    column_names = ', '.join(data[0].keys())
    placeholders = ', '.join(['%s' for _ in range(len(data[0]))])
    insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"
    
    # 将字典列表转换为值元组列表
    records = [tuple(item.values()) for item in data]
    # 若需严格匹配列名顺序（兼容Python3.7以下无序字典），改用：
    # records = [tuple(item[col.strip()] for col in column_names.split(',')) for item in data]
    
    cursor.executemany(insert_query, records)
    
    cursor.close()
    conn.close()

方式2：使用命名占位符（更直观）

改用 %(key)s 格式的命名占位符，直接支持字典作为参数，无需转换：

import psycopg2

def insert_records(data):
    table_name = "your_table_name"  # 替换为实际表名
    conn = psycopg2.connect(
        database = "postgres",
        user = "some_user",
        password = "some_password",
        host = "***rds.amazonaws.com",
        port="5432"
    )
    
    conn.autocommit = True
    cursor = conn.cursor()
    
    column_names = ', '.join(data[0].keys())
    # 生成与字典键对应的命名占位符
    placeholders = ', '.join([f'%({key})s' for key in data[0].keys()])
    insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"
    
    # 直接传入字典列表
    cursor.executemany(insert_query, data)
    
    cursor.close()
    conn.close()

方式3：使用 execute_batch（性能更优）

处理大量数据时，推荐使用 psycopg2.extras.execute_batch，它比原生 executemany 性能更高，同样支持字典列表：

import psycopg2
from psycopg2.extras import execute_batch

def insert_records(data):
    table_name = "your_table_name"  # 替换为实际表名
    conn = psycopg2.connect(
        database = "postgres",
        user = "some_user",
        password = "some_password",
        host = "***rds.amazonaws.com",
        port="5432"
    )
    
    conn.autocommit = True
    cursor = conn.cursor()
    
    column_names = ', '.join(data[0].keys())
    placeholders = ', '.join([f'%({key})s' for key in data[0].keys()])
    insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"
    
    # 使用execute_batch替代executemany
    execute_batch(cursor, insert_query, data)
    
    cursor.close()
    conn.close()

额外建议 #

• 始终定义 table_name 变量（原代码中未定义，会触发NameError）
• 使用 with 上下文管理器自动管理连接和游标，避免资源泄漏：

  def insert_records(data):
      table_name = "your_table_name"
      conn_params = {
          "database": "postgres",
          "user": "some_user",
          "password": "some_password",
          "host": "***rds.amazonaws.com",
          "port": "5432"
      }
      with psycopg2.connect(**conn_params) as conn:
          conn.autocommit = True
          with conn.cursor() as cursor:
              column_names = ', '.join(data[0].keys())
              placeholders = ', '.join([f'%({key})s' for key in data[0].keys()])
              insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"
              cursor.executemany(insert_query, data)
  

内容的提问来源于stack exchange，提问作者VN-