I'd like to batch insert many items with the same prepared statement in sqlite3, using go-sqlite3.
I'm looking for something like the mongodb construct for inserting an array of the same type of records, with automatic transaction/batch handling.
I imagine something like the following:
type Rec struct {
A string
B string
}
db, err := sql.Open("sqlite3", "my.db")
checkErr(err)
stmt, err := db.Prepare("INSERT INTO Recs(a, b) values(?,?)")
checkErr(err)
R1 := Rec{"A":1,"B":2}
R2 := Rec{"A":3,"B":4} // in practice, some loop generates these...
Recs := []{R1,R2}
_, err = stmt.ExecMany(Recs)
Does this kind of syntax exist for sqlite?
I'm currently doing the following:
var Recs []Rec
batch := 0
for i:=0;i<100000; i++ {
R := Rec{"A":i,"B":i+1}
Recs = append(Recs, R)
if (batch % 100) {
tx, err := db.Begin()
stmt, err := db.Prepare("INSERT INTO Recs(a, b) values(?,?)")
for _, r := range Recs {
_, err = stmt.Exec(r.A, r.B)
checkErr(err)
}
err = tx.Commit()
checkErr(err)
Recs = Recs[0:0]
batch = 1
} else {
batch = batch + 1
}
}
but it is super slow, and I keep errorring out with errors like:
panic: database is locked.