用unnest更新bytea列

Question

我正在尝试使用unnest()更新数据库中的许多行。这是我的分贝：

create table my_table
(
    id         serial,
    feat       bytea,
    primary key (id)
);

create index ix_my_table_id
    on my_table (id);

然后，我用以下内容更新表：

import numpy as np

data = [(np.random.random(20).tobytes(), i) for i in range(100)]
cursor.execute(
    """
    UPDATE my_table
    SET feat = s.feat
    FROM unnest(%s) s(feat bytea, id integer)
    WHERE id = s.id;
    """, (data,),
)

但我得到了以下错误：

  File ".../python3.8/site-packages/psycopg/cursor.py", line 551, in execute
    self._conn.wait(
  File ".../python3.8/site-packages/psycopg/connection.py", line 776, in wait
    return waiting.wait(gen, self.pgconn.socket, timeout=timeout)
  File ".../python3.8/site-packages/psycopg/waiting.py", line 219, in wait_epoll
    s = next(gen)
  File ".../python3.8/site-packages/psycopg/cursor.py", line 196, in _execute_gen
    pgq = self._convert_query(query, params)
  File ".../python3.8/site-packages/psycopg/cursor.py", line 379, in _convert_query
    pgq.convert(query, params)
  File ".../python3.8/site-packages/psycopg/_queries.py", line 84, in convert
    self.dump(vars)
  File ".../python3.8/site-packages/psycopg/_queries.py", line 95, in dump
    self.params = self._tx.dump_sequence(params, self._want_formats)
  File ".../python3.8/site-packages/psycopg/_transform.py", line 152, in dump_sequence
    out[i] = dumper.dump(param)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 167, in dump
    dump_list(obj)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 154, in dump_list
    ad = self._dump_item(item)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 173, in _dump_item
    return self.sub_dumper.dump(item)
  File ".../python3.8/site-packages/psycopg/types/composite.py", line 66, in dump
    return self._dump_sequence(obj, b"(", b")", b",")
  File ".../python3.8/site-packages/psycopg/types/composite.py", line 47, in _dump_sequence
    ad = b'"' + self._re_esc.sub(rb"\1\1", ad) + b'"'
  File ".../3.8.10/lib/python3.8/re.py", line 332, in filter
    return sre_parse.expand_template(template, match)
  File ".../3.8.10/lib/python3.8/sre_parse.py", line 1064, in expand_template
    return empty.join(literals)
AttributeError: 'memoryview' object has no attribute 'join'

然而，如果我做一个经典的更新，它是有效的。知道吗？

Python: 3.8.10
psycopg: 3.0.13

Answer 1

由于您的数据是一个2x100矩阵，所以最好将其划分为向量(一维数组)，如下所示：

cursor.execute(
        """
        UPDATE my_table mt
        SET feat = s.feat
        FROM (select unnest(%s) id, unnest(%s) feat) s 
        WHERE mt.id = s.id;
    """, ([row[1] for row in data],[row[0] for row in data]),)