RSQLite is the easiest way to use a database from R because the package itself contains SQLite; no external software is needed. This vignette will walk you through the basics of using a SQLite database.
RSQLite is a DBI-compatible interface which means you primarily use functions defined in the DBI package, so you should always start by loading DBI, not RSQLite:
To create a new SQLite database, you simply supply the filename to
If you just need a temporary database, use either
(for an on-disk database) or
"file::memory:" (for a in-memory database). This database
will be automatically deleted when you disconnect from it.
You can easily copy an R data frame into a SQLite database with
Issue a query with
dbGetQuery(mydb, 'SELECT * FROM mtcars LIMIT 5') #> mpg cyl disp hp drat wt qsec vs am gear carb #> 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 #> 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 #> 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 #> 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 #> 5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
Not all R variable names are valid SQL variable names, so you may
need to escape them with
If you need to insert the value from a user into a query, don’t use
paste()! That makes it easy for a malicious attacker to
insert SQL that might damage your database or reveal sensitive
information. Instead, use a parameterised query:
This is a little more typing, but much much safer.
If you run a query and the results don’t fit in memory, you can use
dbClearResults() to retrieve the results in batches. By
dbFetch() will retrieve all available rows: use
n to set the maximum number of rows to return.
You can use the same approach to run the same parameterised query
with different parameters. Call
dbBind() to set the
You can also pass multiple parameters in one call to
DBI has new functions
dbExecute(), which are the counterparts of
dbGetQuery() for SQL
statements that do not return a tabular result, such as inserting
records into a table, updating a table, or setting engine parameters. It
is good practice, although currently not enforced, to use the new
functions when you don’t expect a result.