Search code examples
rdbidbplyrrsqlite

R trouble finding the table in sql database from a function in DBI package, using with RSQLite and dbplyr

I have been studying a YouTube video from Andrew Couch about RSQLite, DBI, and dbplyr packages. Here is the link.

I'm running into an error, however, and I'm not sure what is happening. The YouTube video is a good video but I don't think AC is running into the same issue I am having so I don't think it's diagnosed there.

I first load the libraries:

library(dbplyr)
library(RSQLite)
library(DBI)

I then rename mtcars as sql_mtcars and I create a connection to the SQLite database and copy my dataframe to that database.

sql_mtcars <- mtcars
con <- RSQLite::dbConnect(SQLite(), ":memory:")
dplyr::copy_to(con, sql_mtcars)

I turn it into a table (I guess because I have to?)

sql_mtcars_db <- tbl(con, "sql_mtcars")

I practice writing a query and then testing the SQL version of the query.

sql_mtcars_db %>%
  dplyr::select(car, mpg, wt) %>%
  dplyr::show_query()

But here is where I get stuck. Now I'm trying to write the SQL code directly under the dbGetQuery function from DBI.

DBI::dbGetQuery(con, '
SELECT sql_mtcars_db.mpg 
FROM sql_mtcars_db
  ')

I get prompted with this error message:

Error: no such table: sql_mtcars_db

But that's confusing to me because when I run this line of code to call the table:

sql_mtcars_db

I can see that the table is there in the SQLite database.

enter image description here

Solution

  • The table name is sq_mtcars- i.e. the select statement is for the particular table and the connection is already established with the database

    DBI::dbGetQuery(con, '
SELECT sql_mtcars.mpg 
FROM sql_mtcars
  ')

    -output

      mpg
1  21.0
2  21.0
3  22.8
4  21.4
5  18.7
6  18.1
7  14.3
8  24.4
9  22.8
10 19.2
11 17.8
...

    If there are doubts, list the tables with

    dbListTables(con)
[1] "sql_mtcars"   "sqlite_stat1" "sqlite_stat4"