Supporting appending `Vector` in DuckDB

Specific Domains Data
3

Just a public service announcement, thanks to @era127’s [Julia] Add support for appending duckdb List types by era127 · Pull Request #16512 · duckdb/duckdb · GitHub which is merged, if you update the DuckDB package, the code I originally had trouble with in the previous discourse topic now runs as expected (see below):

using DuckDB, DataFrames

function load_table!(db::T, df::DataFrame, name) where {T<:DuckDB.DB}
    sch = DataFrame(DBInterface.execute(db, "DESCRIBE $(name);"))
    sch_cols = sch[!, :column_name]
    @assert Set(names(df)) == Set(sch_cols)
    appender = DuckDB.Appender(db, name)
    for i in eachrow(df)
        for j in sch_cols
            # database columns are ordered
            DuckDB.append(appender, i[j])
        end
        DuckDB.end_row(appender)
    end
    DuckDB.flush(appender)
    DuckDB.close(appender)
end

db_mem = DBInterface.connect(DuckDB.DB, ":memory:")
DBInterface.execute(db_mem, "CREATE TABLE tab1 (id INTEGER PRIMARY KEY, vals INTEGER[]);")
tab1_df = DataFrame(id=1:10, vals=fill([1,2,3],10))
load_table!(db_mem, tab1_df, "tab1") # errors
DBInterface.execute(db_mem, "SELECT * FROM tab1;") |> DataFrame
4 Likes