Python+PostgreSQL插入SQL构建报错求助：多类错误排查

我要写Python脚本实现读取JSON文件、提取列名并生成PostgreSQL插入SQL的功能，编写的代码如下：

def insert_payout(payout, items, node_name):#TODO add data source in pycharm
    vals = []
    nftDict = json.loads(nft)
    nftList = list(nftDict.keys())
    table = 'consortia.public.' + node_name
    col_list = []

    for i in range(len(nftList)):
        col_list.append(str({nftList[i]}))
        vals.append(payout)

    base_sql = sql.SQL(
        '''INSERT INTO {} ({}) 
        VALUES ({})
        '''). \
        format(sql.Identifier(table),
               sql.SQL(", ").join(map(sql.Identifier, col_list)),
               sql.SQL(', ').join(sql.Placeholder() * len(col_list))
          )
    

    conn = None

    try:
        # read database configuration
        params = setparams()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        #cur.execute(base_sql, vals)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


if __name__ == '__main__':
    insert_payout(7.4, item_json, 'test_write_table')

遇到的错误 #

1. 初始错误：

TypeError: Composed elements must be Composable, got 'N' instead

2. 修正后新错误：

"ItemUniqueName31": [
    "Item_ID_Number31",
    "<something31>",
    "BooleanActive"
],
"ItemUniqueName32": [
    "Item_ID_Number32",
    "<something32>",
    "BooleanActive"
]
}

dict is not a sequence

3. 最新错误：

relation "consortia.public.test_write_table" does not exist

报错行：

LINE 1: INSERT INTO "consortia.public.test_write_table" ("{'ItemUniqu...
^

当前聚焦解决SQL构建问题，请求排查错误。

SQL构建问题排查与修复 #

1. 表名标识符错误 #

你把consortia.public.test_write_table作为单个字符串传给sql.Identifier，导致PostgreSQL将其识别为单个表名（带点的完整字符串），而不是consortia数据库下public schema中的test_write_table表。

正确做法：

• 如果consortia是数据库名，在连接参数params中指定（比如dbname='consortia'），无需写在表名里；
• 表名部分仅指定schema和表，用sql.Identifier传入两个参数：

# 假设consortia是数据库名，已在连接参数中配置
table_schema = 'public'
table_name = node_name
# 生成正确的schema+表标识符
sql.Identifier(table_schema, table_name)

2. 列名格式错误 #

代码中col_list.append(str({nftList[i]}))会生成带大括号的字符串（比如"{'ItemUniqueName31'}"），经sql.Identifier处理后，SQL中的列名会变成"{'ItemUniqueName31'}"，这不是合法的列名。

直接使用JSON的键作为列名即可，无需转成带大括号的字符串：

# 替换原循环逻辑
col_list = nftList  # 直接用JSON键列表作为列名
vals = [payout] * len(col_list)  # 简化重复赋值写法

3. 变量名错误 #

函数参数是items，但代码中使用了未定义的nft变量，需修正为：

nftDict = json.loads(items)  # 使用传入的items参数

4. 执行语句被注释 #

当前代码中cur.execute(base_sql, vals)被注释，即使SQL构建正确也不会执行插入，需取消注释。

修复后的核心代码片段 #

def insert_payout(payout, items, node_name):
    vals = []
    nftDict = json.loads(items)  # 修复变量名错误
    nftList = list(nftDict.keys())
    # 拆分schema和表名，consortia数据库在连接参数中指定
    table_schema = 'public'
    table_name = node_name

    col_list = nftList  # 直接用JSON键作为列名
    vals = [payout] * len(col_list)  # 简化赋值

    base_sql = sql.SQL(
        '''INSERT INTO {} ({}) 
        VALUES ({})
        ''').format(
            sql.Identifier(table_schema, table_name),  # 正确的schema+表标识符
            sql.SQL(", ").join(map(sql.Identifier, col_list)),
            sql.SQL(', ').join(sql.Placeholder() * len(col_list))
        )
    
    try:
        params = setparams()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(base_sql, vals)  # 取消注释执行SQL
        conn.commit()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

内容的提问来源于stack exchange，提问作者Kyle Sponable