CODEWITHSUNDEEP
sqlrdatabasesqlitedataset
kiwi
kiwi

Reputation: 585

How to read all tables from a SQLite database and store as datasets/variables in R?

I have a large SQLite database with many tables. I have established a connection to this database in RStudio using RSQLite and DBI packages. (I have named this database db)

library(RSQLite)
library(DBI)

At the moment I have to read in all the tables and assign them a name manually. For example:

country <- dbReadTable(db, "country")
date <- dbReadTable(db, "date")
#...and so on

You can see this can be a very time-consuming process if you were to have many tables.

So I was wondering if it is possible to create a new function or using existing functions (e.g. lapply() ?) to complete this more efficiently and essentially speed up this process?

Any suggestions are much appreciated :)

Upvotes: 1

Views: 2044

Answers (2)

r2evans
r2evans

Reputation: 160447

Two mindsets:

  1. All tables/data into one named-list:

    alldat <- lapply(setNames(nm = dbListTables(db)), dbReadTable, conn = db)

    The benefit of this is that if the tables have similar meaning, then you can use lapply to apply the same function to each. Another benefit is that all data from one database are stored together.

    See How do I make a list of data frames? for working on a list-of-frames.

  2. If you want them as actual variables in the global (or enclosing) environment, then take the previous alldat, and

    ign <- list2env(alldat, envir = .GlobalEnv)

    The return value from list2env is the environment that we passed to list2env, so it's not incredibly useful in this context (though it is useful other times). The only reason I capture it into ign is to reduce the clutter on the console ... which is minor. list2env works primarily in side-effect, so the return value in this case is not critical.

Upvotes: 5

EJJ
EJJ

Reputation: 1513

You can use dbListTables() to generate a character vector of all your table names in your SQLite database and use lapply() to import them into R efficiently. I would first check you are able to import all the tables in your database into memory.

Below is an reproducible example of this:

library(RSQLite)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(db, "mtcars", mtcars)
dbWriteTable(db, "iris", iris)
db_tbls <- dbListTables(db)

tbl_list <- lapply(db_tbls, dbReadTable, conn = db)
tbl_list <- setNames(tbl_list, db_tbls)

dbDisconnect(db)

> lapply(tbl_list, head)
$iris
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa
6          5.4         3.9          1.7         0.4  setosa

$mtcars
   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
6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

Upvotes: 4

Related Questions