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

问题描述

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

I am trying to insert a Python(2.7) list of jsonb elements into a Postgresql(9.4) table with a column of datatype: 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给我一个错误

Now when I try to insert myArray, psycopg2 gives me an error

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

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

I'm not quite sure what the correct syntax is to insert this data into the table. Any help/pointers will be appreciated.

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

SolutionThanks to piro, it was a quick solution.

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

推荐答案

这不是psycopg错误:这是psycopg所依赖的PostgreSQL错误.

This is not a psycopg error: it is a PostgreSQL error psycopg is just relying.

错误似乎表明没有隐式text []-> jsonb []强制转换，因此您需要添加一个手动文本:

The error seems suggesting there is no implicit text[]->jsonb[] cast so you need to add a manual one:

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

这篇关于将字典的Python列表转换为JSON的Postgresql数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！