Python for Postgres 中 jsonb 数组的正确格式是什么？

Question

我有一个看起来像的模式

Column                  |            Type             |

-------------------------------------------------------

message_id              | integer                     | 

 user_id                | integer                     |

 body                   | text                        |

 created_at             | timestamp without time zone |

 source                 | jsonb                       |

 symbols                | jsonb[]                     |

我正在尝试使用 psycopg2 通过 psycopg2.Cursor.copy_from() 插入数据，但我遇到了很多问题，试图弄清楚应该如何格式化 jsonb[] 对象。当我做一个 JSON 对象的直接列表时，我得到一个错误，看起来像

psycopg2.errors.InvalidTextRepresentation: malformed array literal: "[{'id': 13016, 'symbol':

.... 

DETAIL:  "[" must introduce explicitly-specified array dimensions.

我在双引号和大括号上尝试过许多不同的转义。如果我对我的数据执行 json.dumps()，我会收到以下错误。

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type json

DETAIL:  Token "'" is invalid.

从此代码段收到此错误

messageData = []

symbols = messageObject["symbols"]

newSymbols = []

for symbol in symbols:

    toAppend = symbol

    toAppend = refineJSON(json.dumps(symbol))

    toAppend = re.sub("{", "\{", toAppend)

    toAppend = re.sub("}", "\}", toAppend)

    toAppend = re.sub('"', '\\"', toAppend)

    newSymbols.append(toAppend)

messageData.append(set(newSymbols))

我也愿意将列定义为不同的类型（例如，文本），然后尝试进行转换，但我也无法做到这一点。

messageData 是调用 psycopg2.Cursor.copy_from() 的辅助函数的输入

def copy_string_iterator_messages(connection, messages, size: int = 8192) -> None:

    with connection.cursor() as cursor:

        messages_string_iterator = StringIteratorIO((

            '|'.join(map(clean_csv_value, (messageData[0], messageData[1], messageData[2], messageData[3], messageData[4], messageData[5], messageData[6], messageData[7], messageData[8], messageData[9], messageData[10], 

                messageData[11],

            ))) + '\n'

            for messageData in messages

        ))

        # pp.pprint(messages_string_iterator.read())

        cursor.copy_from(messages_string_iterator, 'test', sep='|', size=size)

        connection.commit()

Answer 1

你的问题让我很好奇。下面这个对我有用。我怀疑是否可以解决转义到 CSV 或从 CSV 转义的问题。我的表：=# \d jbarray&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Table "public.jbarray"&nbsp;Column&nbsp; |&nbsp; Type&nbsp; &nbsp;| Collation | Nullable |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Default---------+---------+-----------+----------+-------------------------------------&nbsp;id&nbsp; &nbsp; &nbsp; | integer |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| not null | nextval('jbarray_id_seq'::regclass)&nbsp;symbols | jsonb[] |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |Indexes:&nbsp; &nbsp; "jbarray_pkey" PRIMARY KEY, btree (id)完全独立的 Python 代码：mport jsonimport psycopg2con = psycopg2.connect('dbname=<my database>')some_objects = [{'id': x, 'array': [x, x+1, x+2, {'inside': x+3}]} for x in range(5)]insert_array = [json.dumps(x) for x in some_objects]print(insert_array)c = con.cursor()c.execute("insert into jbarray (symbols) values (%s::jsonb[])", (insert_array,))con.commit()结果：=# select * from jbarray;-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------id&nbsp; &nbsp; &nbsp; | 1symbols | {"{\"id\": 0, \"array\": [0, 1, 2, {\"inside\": 3}]}","{\"id\": 1, \"array\": [1, 2, 3, {\"inside\": 4}]}","{\"id\": 2, \"array\": [2, 3, 4, {\"inside\": 5}]}","{\"id\": 3, \"array\": [3, 4, 5, {\"inside\": 6}]}","{\"id\": 4, \"array\": [4, 5, 6, {\"inside\": 7}]}"}=# select id, unnest(symbols) from jbarray;-[ RECORD 1 ]----------------------------------------id&nbsp; &nbsp; &nbsp;| 1unnest | {"id": 0, "array": [0, 1, 2, {"inside": 3}]}-[ RECORD 2 ]----------------------------------------id&nbsp; &nbsp; &nbsp;| 1unnest | {"id": 1, "array": [1, 2, 3, {"inside": 4}]}-[ RECORD 3 ]----------------------------------------id&nbsp; &nbsp; &nbsp;| 1unnest | {"id": 2, "array": [2, 3, 4, {"inside": 5}]}-[ RECORD 4 ]----------------------------------------id&nbsp; &nbsp; &nbsp;| 1unnest | {"id": 3, "array": [3, 4, 5, {"inside": 6}]}-[ RECORD 5 ]----------------------------------------id&nbsp; &nbsp; &nbsp;| 1unnest | {"id": 4, "array": [4, 5, 6, {"inside": 7}]}如果插入性能对您来说太慢，那么您可以按照此处的说明prepared statement使用with 。我用过那个组合，速度非常快。execute_batch()