Inserting into sqlite DB en masse?

General Usage
1

I’m currently entering rows one-at-a time using Stmt,bind!,execute! and the process is quite slow, even though there are only 3 columns in the table, and I have a lot of rows to insert.

I’ve been looking through the docs and I haven’t found a way to generate a more efficient formulation within Julia (*). it looks as though the queries have to be performed one-at-a-time. is there a way to prepare a block of inserts ?

Thanks.

(*) the most efficient formulation i’ve found so far is to generate a csv file and use csv import in sqlite.

2

I don’t have practice with SQLite.jl, but looking at the documentation seems like an alternative which could be faster would be inserting it all in a temporary table and then copying it within SQLite.

SQLite.Load!(source, db, "table_temp", temp=true)
SQLite.Query(db, "INSERT INTO table SELECT * FROM table_temp")

You could also use SQLite’s UPSERT if you want to insert or update if it exists. In this case source could be a dataframe or an array of named tuples (anything that implements Tables.jl)

3

The documentation for that call was sitting right under the Query call I must have skipped right over it when I was looking through.

I haven’t tried it but that’s certain to be much faster than one row at it me, and certainly much more convenient.

Thank you !

4

Actually, I just tested and you can load! onto a table that already exists, so you don’t need the second step.