0

I have created and filled a sqlite database within R using packages DBI and RSQLite. E.g. like that:

con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), "../mydatabase.sqlite")
DBI::dbWriteTable(con_sqlite, "mytable", d_table, overwrite = TRUE)
...

Now the sqlite file got too big and I reduced the tables. However, the size does not decrease and I found out that I have to use command vaccum. Is there a possibility to use this command within R?

panuffel
  • 624
  • 1
  • 8
  • 15

1 Answers1

3

I think this should do the trick:

DBI::dbExecute(con, "VACUUM;")
Peter H.
  • 1,995
  • 8
  • 26
  • Perfect! Thank you. Was not aware of the execute possibility :) – panuffel Feb 03 '23 at 11:53
  • You can send any arbitrary sql command that way, which is nice to have if the dbplyr functions are missing some functionality. – Peter H. Feb 03 '23 at 11:55
  • 1
    `dbExecute` is paired nicely with `dbGetQuery`: they both nicely clean up after themselves, where the first does not expect a return value and the latter does. They both effectively call their equivalent slightly-lower-level functions `dbSendStatement` and `dbSendQuery`, where `dbFetch` it required to retrieve the results, and both `dbSend*` require `dbClearResult` afterward. – r2evans Feb 03 '23 at 13:52