使用RSQLite、DBI和dbplyr在RSQLite中不能有多个表以进行连接

Question

我很难理解这三个包的行为：RSQLite、DBI和dbplyr。

我正在使用mtcars数据文件，并将其与tibble包一起加载，以添加车名作为行之一。

library(tibble)

mtcars <- tibble::as_tibble(mtcars, rownames = 'car')  

我还创建了自己的表格，其中包含了所有这些汽车的生产地点的信息：

car <- c("Mazda RX4", "Mazda RX4 Wag", "Datsun 710", "Hornet 4 Drive", "Hornet Sportabout", "Valiant", "Duster 360", "Merc 240D", "Merc 230", "Merc 280", "Merc 280C", "Merc 450SE", "Merc 450SL", "Merc 450SLC", "Cadillac Fleetwood", "Lincoln Continental", "Chrysler Imperial", "Fiat 128", "Honda Civic", "Toyota Corolla", "Toyota Corona", "Dodge Challenger", "AMC Javelin", "Camaro Z28", "Pontiac Firebird", "Fiat X1-9", "Porsche 914-2", "Lotus Europa", "Ford Pantera L", "Ferrari Dino", "Maserati Bora", "Volvo 142E")

origin <- c("Japan", "Japan", "Japan", "United States", "United States", "United States", "United States", "Germany", "Germany", "Germany", "Germany", "Germany", "Germany", "Germany", "United States", "United States", "United States", "Italy", "Japan", "Japan", "Japan", "United States", "United States", "United States", "United States", "Italy", "Germany", "British", "United States", "Italy", "Italy", "Sweden")

cars_origin_tbl <- tibble(car, origin)

通过创建这两个表，我可以使用R练习联接，下面是编写semi_join的两种不同方法的示例

mtcars %>% dplyr::semi_join(cars_origin_tbl, by = 'car') -> mtcars_semi_join

# another method using value matching

mtcars_semi_join <- mtcars %>%
  filter(car %in% cars_origin_tbl$car)

但是，我真的希望能够更轻松地使用SQL语法进行联接。我有一个即将到来的工作，将涉及更多的SQL，我真的很想学习之前，我开始。对我来说，最好的学习方法就是做这些事情。在网上看例子，但我自己不做，只是不适合我。

因此，在这里，我试图创建一个SQL版本的东西使用这些R包。

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

这个来自Andrew的YouTube视频非常有用。这是链接。

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

sql_mtcars_db <- tbl(con, "sql_mtcars")

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

这个show_query的东西很有趣。它显示了dplyr代码的SQL版本。

​

​

这里的另一篇文章得到了答复并向我展示了如何使用dbListTables查找列出的表。

dbListTables(con)

​

​

这个dbGetQuery将运行代码并获得预期的输出。

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

​

​

这就是我困惑的地方。我正在尝试将cars_origin_tbl放到RSQLite中，然后练习连接两个不同的表。

cars_origin_tbl <- cars_origin_tbl
con <- dbConnect(SQLite(), ":memory:")
copy_to(con, cars_origin_tbl)

cars_origin_tbl_db <- tbl(con, "cars_origin_tbl")

dbListTables(con)

运行dbListTables()之后，我的第一个sql_mtcars就被遗忘并消失了。

​

​

现在，这个dbGetQuery运行良好，因为我只是调用更新的表。

DBI::dbGetQuery(con, '
SELECT cars_origin_tbl.car
FROM cars_origin_tbl
  ')

但是另一个dbGetQuery不能工作，因为我指的是两个表，我认为这两个表都在RSQLite中。我收到一条消息说没有sql_mtcars。

DBI::dbGetQuery(con, '
SELECT sql_mtcars.mpg, sql_mtcars.wt
FROM sql_mtcars
LEFT JOIN cars_origin_tbl 
ON sql_mtcars.car = cars_origin_tbl.car;
  ')

Error: no such table: sql_mtcars

我基本上是想把两个表连接到这个连接中，这样我就可以练习加入它们了，但是我不知道如何在没有一个消失的情况下把两个表都放进去。

con <- RSQLite::dbConnect(SQLite(), ":memory:")
con2 <- RSQLite::dbConnect(SQLite(), ":memory:")
dplyr::copy_to(c(con, con2), cars_origin_tbl)

Error in UseMethod("copy_to") : 
  no applicable method for 'copy_to' applied to an object of class "list"

Answer 1

解决方案是对两个表使用相同的连接。以下工作：

# create data base connection
con <- RSQLite::dbConnect(SQLite(), ":memory:")

# copy tables to data base
dplyr::copy_to(con, sql_mtcars)
dplyr::copy_to(con, cars_origin_tbl)

# check contents of data base
dbListTables(con)

# create local pointer to remote table
sql_mtcars_db <- tbl(con, "sql_mtcars")
cars_origin_tbl_db <- tbl(con, "cars_origin_tbl")

您现在可以半连接这两个表：

mtcars_semi_join = sql_mtcars_db %>%
  semi_join(cars_origin_tbl_db, by = 'car')

show_query(mtcars_semi_join)

生成以下SQL：

SELECT * FROM `sql_mtcars` AS `LHS`
WHERE EXISTS (
  SELECT 1 FROM `cars_origin_tbl` AS `RHS`
  WHERE (`LHS`.`car` = `RHS`.`car`)

顺便提一下，sql_mtcars和sql_mtcars_db是不同的R对象。第一个是局部R内存中的表。第二个是存储在数据库中的远程表的本地访问点。