将 Python 字典列表转换为 Postgresql json 数组

我正在尝试将 jsonb 元素的 Python(2.7) 列表插入到具有数据类型列的 Postgresql(9.4) 表中：jsonb[]。

这是一些代码：

import json
anArray = [{"name":"Joe","age":51,"yob":1964,"gender":"male"},{"name":"George","age":41,"dob":1974,"gender":"male"},{"name":"Nick","age":31,"dob":1984,"gender":"male"}]
myArray = []
#here's what I have so far: 
for e in anArray:
    myArray.append(json.dumps(e))
#this gives me
myArray = ['{"name":"Joe","age":51,"yob":1964,"gender":"male"}','{"name":"George","age":41,"dob":1974,"gender":"male"}','{"name":"Nick","age":31,"dob":1984,"gender":"male"}']
#insert commands
insert_sql = "INSERT INTO my_table (data) VALUES (%s);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

现在，当我尝试插入 myArray 时，psycopg2 给我一个错误

psycopg2.ProgrammingError: column "data" is of type jsonb[] but expression is of type text[]

我不太确定将此数据插入表中的正确语法是什么。任何帮助/指示将不胜感激。

Solution感谢 piro，这是一个快速的解决方案。

insert_sql = "INSERT INTO my_table (columns) VALUES (%s::jsonb[]);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

这不是 psycopg 错误：这是 psycopg 只是依赖的 PostgreSQL 错误。

该错误似乎表明没有隐式 text[]->jsonb[] 转换，因此您需要添加手动转换：

INSERT INTO my_table (columns) VALUES (%s::jsonb[]);