Issue
I'm trying to update a lot of row in my database using unnest()
.
Here's my db:
create table my_table
(
id serial,
feat bytea,
primary key (id)
);
create index ix_my_table_id
on my_table (id);
Then I update the table with:
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,),
)
But I got the following error:
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'
However, it's working if I do a classic update. Any idea ?
Python: 3.8.10
psycopg: 3.0.13
Solution
Since your data is a 2x100 matrix, is better to partition it in vectors (1d array) for each parameter as follows:
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]),)
Answered By - Pepe N O
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.