r使用rsqlite，dbi和dbplyr，可以在rsqlite中有多个表格，以便加入

Question

我很难理解这三个软件包的行为： rsqlite ， dbi 和 dbplyr 。

我正在使用 mtcars dataframe，并且正在使用 tibble 软件包加载它，将CAR名称添加为行之一。

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练习加入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，并且我真的想在开始之前学习。对我来说，最好的学习方法是做这些事情。在网上观看示例而没有自己做，这并不为我留下。

因此，在这里，我试图使用这些R软件包创建SQL版本的内容。

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

来自安德鲁·库奇（Andrew Couch）的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内存中的一个表。第二个是存储在数据库中的远程表的本地访问点。