Managing transactions with RSQLite?

Hi all,

I'm sorry if the answer is obvious, but my searches haven't been
bearing fruit. I'm trying to wrap a large "insert into" query into a
transaction to optimize its speed. I have something like so:

  dbGetPreparedQuery(conn, "INSERT INTO restriction_read (id, nhits)
VALUES(?, ?)",
                     bind.data=counts.df)

where:
  * `conn` is my sqlite connection
  * `counts.df` is my data.frame with id and nhits colums

`counts.df` can be several 100k to 1 million rows.

I thought this would wrap the inserts into a transaction, but I just
happened to notice that a "mydb.sqlite3-journal" file is being created
and removed lots of times on my file system while R is running this
query (upwards of 30mins), so I'm guessing the queries are being
executed 1 at a time, and not wrapped into a transaction.

Am I correct? If so, what's the appropriate way to handle transactions
(if any) using RSQLite?

Try doing:

?dbBeginTransaction(db)
?## ?insert here
?dbCommit(db)