如何使用pg8000.connect向PostgreSQL存储过程传递嵌套数组

错误原因 #

你手动将嵌套数组转换为字符串并通过参数传递，导致PostgreSQL无法正确解析数组字面量：

• 手动拼接的字符串格式不符合PostgreSQL复合类型数组的语法要求
• pg8000的参数绑定会自动处理字符串转义，你手动添加的转义符会失效，导致格式混乱
• 错误提示中的"[" must introduce explicitly-specified array dimensions说明PostgreSQL无法识别你构造的数组格式

解决方案 #

不要手动拼接字符串，直接用Python的嵌套元组/列表结构对应PostgreSQL的自定义类型及数组，让pg8000自动完成类型映射。

正确代码示例 #

import pg8000

sql = """CALL insert_family(
    %s,
    %s::parent_type[]
);"""

with pg8000.connect(host="localhost", database="mydb", user="myuser", password="mypass") as con:
    with con.cursor() as cursor:
        # 用Python嵌套结构对应PostgreSQL的类型：
        # - 外层列表对应 parent_type[] 数组
        # - 每个父元素是元组，对应 parent_type 复合类型（p字段, children数组）
        # - children数组是子元组的列表，对应 child_type[] 数组
        parents_data = [
            ("Zeus", [(1, "Hestia"), (2, "Hades")])
        ]
        cursor.execute(sql, args=('Cronus', parents_data))

原理说明 #

pg8000会自动将Python的：

• 列表转换为PostgreSQL的数组
• 元组转换为PostgreSQL的复合类型（row type）

这样就避免了手动构造字符串的格式错误，同时也更安全（防止SQL注入）。

备选方案：显式构造数组字面量（不推荐） #

如果必须手动构造字符串，需要严格遵循PostgreSQL的数组字面量语法，注意转义和格式：

import pg8000

sql = """CALL insert_family(
    %s,
    %s::parent_type[]
);"""

with pg8000.connect(host="localhost", database="mydb", user="myuser", password="mypass") as con:
    with con.cursor() as cursor:
        # 严格按照PostgreSQL复合类型数组的格式构造字符串
        parent_str = """("Zeus", '{"(1,Hestia)","(2,Hades)"}'::child_type[])"""
        cursor.execute(sql, args=('Cronus', [parent_str]))

注意：这种方式容易出错，且存在SQL注入风险，仅在特殊场景下使用。

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