I’m starting to use the package SQLite, and it has a function load! which writes rows from a dataframe into a sqlite database.
I want this function to replace rows instead of inserting them. My current solution is to essentially copy the code, replace the word INSERT with REPLACE, and utilize the new functions:
import SQLite
function load!(itr, db::SQLite.DB, name::AbstractString="sqlitejl_"*Random.randstring(5); kwargs...)
# check if table exists
db_tableinfo = SQLite.tableinfo(db, name)
rows = Tables.rows(itr)
sch = Tables.schema(rows)
return load!(sch, rows, db, name, db_tableinfo; kwargs...)
end
function load!(sch::Tables.Schema, rows, db::SQLite.DB, name::AbstractString, db_tableinfo::Union{NamedTuple, Nothing};
temp::Bool=false, ifnotexists::Bool=false, analyze::Bool=false)
# check for case-insensitive duplicate column names (sqlite doesn't allow)
SQLite.checkdupnames(sch.names)
# check if `rows` column names match the existing table, or create the new one
if db_tableinfo !== nothing
SQLite.checknames(sch, db_tableinfo.name)
else
createtable!(db, name, sch; temp=temp, ifnotexists=ifnotexists)
end
# build insert statement
esc_id = SQLite.esc_id
sqlite3_step = SQLite.sqlite3_step
sqlite3_reset = SQLite.sqlite3_reset
columns = join(esc_id.(string.(sch.names)), ",")
params = chop(repeat("?,", length(sch.names)))
stmt = SQLite._Stmt(db, "REPLACE INTO $(esc_id(string(name))) ($columns) VALUES ($params)")
# start a transaction for inserting rows
SQLite.transaction(db) do
for row in rows
Tables.eachcolumn(sch, row) do val, col, _
SQLite.bind!(stmt, col, val)
end
sqlite3_step(stmt.handle)
sqlite3_reset(stmt.handle)
end
end
SQLite._close!(stmt)
analyze && execute(db, "ANALYZE $nm")
return name
end
This works fine for now, but it feels really clunky; is there a better way to approach this?