python - Assign numpy array to duckdb array column in where clause - Stack Overflow

admin管理员组

文章数量:1406046

How do I update a duckdb array column with the value from a numpy array (returned from a python function) in a line where a where-clause matches?

In the example I want to assign the value of new_array in the second line in the table example, that is the line in which WHERE id = 2 matches.

import duckdb
import numpy as np

con = duckdb.connect()
con.sql("CREATE TABLE example (id INTEGER, vec FLOAT[3]);")
con.sql("INSERT INTO example VALUES (1, array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT));")
con.sql("INSERT INTO example VALUES (2, NULL);")

con.sql("SELECT * FROM example;").fetchall()

new_array = np.array([4,5,6], dtype=np.float32)
new_array

How do I update a duckdb array column with the value from a numpy array (returned from a python function) in a line where a where-clause matches?

In the example I want to assign the value of new_array in the second line in the table example, that is the line in which WHERE id = 2 matches.

import duckdb
import numpy as np

con = duckdb.connect()
con.sql("CREATE TABLE example (id INTEGER, vec FLOAT[3]);")
con.sql("INSERT INTO example VALUES (1, array_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT));")
con.sql("INSERT INTO example VALUES (2, NULL);")

con.sql("SELECT * FROM example;").fetchall()

new_array = np.array([4,5,6], dtype=np.float32)
new_array

• python
• arrays
• duckdb

Share Improve this question Follow asked Mar 6 at 16:07 snautsnaut 2,5342323 silver badges4545 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Ok, I found this out quicker than I expected. One can select from the array. This gives a column. One can then use the aggregation function list to aggregate the column into a list and update the array value with a usual SQL udpate statement:

con.sql("SELECT * from new_array;")
con.sql("UPDATE example SET vec = (SELECT list(column0) FROM new_array) WHERE id = 2;")
con.sql("SELECT * FROM example;").fetchall()

本文标签： pythonAssign numpy array to duckdb array column in where clauseStack Overflow