在 R 中使用 SQLite

正如我在上一篇教程 SQLite 入门指南 的末尾所提到的，SQLite 数据库在配合 R 和 Python 使用时威力最大。到目前为止，在 DataCamp 上，您已经了解了如何通过 Python 操作 SQLite 数据库（参见 Sayak Paul 的《在 Python 中使用 SQLite》教程，学习如何通过 Python 的 sqlite3 包操作 SQLite 数据库）。不过，在本教程中，我们将重点介绍如何在 R 中使用 RSQLite 包来使用 SQLite 数据库。

我们将从基础出发，讲解如何执行关键任务，例如向 SQLite 数据库发送查询或使用 RSQLite 创建表。此外，我还会介绍如何使用参数化查询，以及 INSERT 或 DELETE 等不返回表格结果的操作。

创建数据库和数据表

第一步，如您所料，是创建一个数据库。RSQLite 可以像打开 SQLite 命令行时那样，创建临时的内存型短暂数据库。不过，这通常并非您的目标，因此我们使用 dbConnect() 函数为 mtcars 数据集创建一个正式的数据库。该函数接受以下参数：

• drv：数据库驱动
• path：SQLite 数据库的路径。如果您要创建一个新数据库，只需像我下面那样给它起一个名称即可。如果您想使用临时的内存数据库，则可以省略 path 参数或输入 ":memory:"）。

# Load the RSQLite Library
library(RSQLite)
# Load the mtcars as an R data frame put the row names as a column, and print the header.
data("mtcars")
mtcars$car_names <- rownames(mtcars)
rownames(mtcars) <- c()
head(mtcars)
# Create a connection to our new database, CarsDB.db
# you can check that the .db file has been created on your working directory
conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")

创建好数据库并整理好数据后，您可以使用 dbWriteTable() 函数在数据库中创建一张数据表。该函数可以接受多个参数，但这里我们先关注以下几点：

• conn：到您的 SQLite 数据库的连接
• name：您想为数据表使用的名称
• value：您要插入的数据。它应当是一个 R 数据框 或可被强制转换为 R 数据框的对象。

之后，您可以使用 dbListTables() 函数，将 SQLite 数据库连接作为参数传入，以检查数据表是否已成功创建。

# Write the mtcars dataset into a table names mtcars_data
dbWriteTable(conn, "cars_data", mtcars)
# List all the tables available in the database
dbListTables(conn)

'cars_data'

在使用 RSQLite 创建数据表时，一个非常有用的特性是：如果您手头有多个数据框，可以在循环中将更多数据追加到现有表中，只需在 dbWriteTable() 中设置可选参数 append = TRUE。例如，我们来通过追加两个不同的数据框，创建一张包含部分车型及其制造商的示例表：

# Create toy data frames
car <- c('Camaro', 'California', 'Mustang', 'Explorer')
make <- c('Chevrolet','Ferrari','Ford','Ford')
df1 <- data.frame(car,make)
car <- c('Corolla', 'Lancer', 'Sportage', 'XE')
make <- c('Toyota','Mitsubishi','Kia','Jaguar')
df2 <- data.frame(car,make)
# Add them to a list
dfList <- list(df1,df2)
# Write a table by appending the data frames inside the list
for(k in 1:length(dfList)){
    dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)
}
# List all the Tables
dbListTables(conn)

1. 'Cars_and_Makes'
2. 'cars_data'

现在我们来确认新表中的所有数据：

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

执行 SQL 查询

如上所示，可以通过 RSQLite 使用 dbGetQuery() 函数执行有效的 SQL 查询，该函数具有以下参数：

• conn：到 SQLite 数据库的连接
• query：您想执行的 SQL 查询字符串

为了进一步展示使用 RSQLite 执行 SQL 查询的能力，我们在 cars_data 表上再看几个示例查询：

注意：需要牢记，通过 RSQLite，您可以执行任何对 SQLite 数据库有效的查询，从简单的 SELECT 语句到 JOIN（但不包括 RIGHT OUTER JOIN 和 FULL OUTER JOIN，因为 SQLite 不支持）。

# Gather the first 10 rows in the cars_data table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")

# Get the car names and horsepower of the cars with 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE cyl = 8")

# Get the car names and horsepower starting with M that have 6 or 8 cylinders
dbGetQuery(conn,"SELECT car_names, hp, cyl FROM cars_data
                 WHERE car_names LIKE 'M%' AND cyl IN (6,8)")

# Get the average horsepower and mpg by number of cylinder groups
dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp', AVG(mpg) AS 'average_mpg' FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")

要将查询结果作为数据框存储，以便后续在 R 中进一步操作，只需把查询结果赋给一个变量即可。

avg_HpCyl <- dbGetQuery(conn,"SELECT cyl, AVG(hp) AS 'average_hp'FROM cars_data
                 GROUP BY cyl
                 ORDER BY average_hp")
avg_HpCyl
class(avg_HpCyl)

'data.frame'

在查询中插入变量（参数化查询）

在 R 中操作 SQLite 数据库的一大优势是可以使用参数化查询。也就是说，您可以使用 R 工作空间中的变量来查询 SQLite 数据库。下面举一个在 SQLite 查询中使用变量的例子：

# Lets assume that there is some user input that asks us to look only into cars that have over 18 miles per gallon (mpg)
# and more than 6 cylinders
mpg <-  18
cyl <- 6
Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result

如您所见，发送普通查询与参数化查询的唯一区别在于查询中的占位符（>= ?）以及 dbGetQuery() 的 params 参数。该参数接受一个列表或向量，包含要赋给占位符的值（本例中我们用一个包含 mpg 和 cyl 变量的向量）。

那么，当您想执行不同的查询时会怎样？在上面的示例中，如您所见，我几乎是手工拼接了查询。它只允许输入 mpg 和 cyl 值，并且查询将只检索那些 mpg 和 cyl 大于等于给定值的车型。不过，有时您可能希望更灵活一些。如果用户还想查看马力和重量大于等于某个值的车型怎么办？使用上面的查询，您需要回去重写它，但也可以写一个函数来免去这一步。我们来看一个示例：

# Assemble an example function that takes the SQLite database connection, a base query,
# and the parameters you want to use in the WHERE clause as a list
assembleQuery <- function(conn, base, search_parameters){
    parameter_names <- names(search_parameters)
    partial_queries <- ""
    # Iterate over all the parameters to assemble the query
    for(k in 1:length(parameter_names)){
        filter_k <- paste(parameter_names[k], " >= ? ")
        # If there is more than 1 parameter, add an AND statement before the parameter name and placeholder
        if(k > 1){
            filter_k <- paste("AND ", parameter_names[k], " >= ?")
        }
        partial_queries <- paste(partial_queries, filter_k)
    }
    # Paste all together into a single query using a WHERE statement
    final_paste <- paste(base, " WHERE", partial_queries)
    # Print the assembled query to show how it looks like
    print(final_paste)
    # Run the final query. I unlist the values from the search_parameters list into a vector since it is needed
    # when using various anonymous placeholders (i.e. >= ?)
    values <- unlist(search_parameters, use.names = FALSE)
    result <- dbGetQuery(conn, final_paste, params = values)
    # return the executed query
    return(result)
}

base <- "SELECT car_names, mpg, hp, wt FROM cars_data"
search_parameters <- list("mpg" = 16, "hp" = 150, "wt" = 2.1)
result <- assembleQuery(conn, base, search_parameters)
result

[1] "SELECT car_names, mpg, hp, wt FROM cars_data  WHERE  mpg  >= ?  AND  hp  >= ? AND  wt  >= ?"

上述函数虽然基础，但说明了如何编写 R 代码来生成可在 SQLite 数据库上执行的 SQL 查询。如果您对这一用例感兴趣，建议继续探索。提升空间很多，比如去掉“用户提供参数时总是希望筛选出大于等于该值的记录”的假设等。

不返回表格结果的语句

有时，您可能需要执行不一定返回表格数据的 SQL 查询。例如插入、更新或删除表记录。对此我们可以使用 dbExecute() 函数，它接受 SQLite 数据库连接和一条 SQL 查询作为参数。下面是几个示例：

# Visualize the table before deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")
# Delete the column belonging to the Mazda RX4. You will see a 1 as the output.
dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")
# Visualize the new table after deletion
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")

1

现在我们把 Mazda RX4 的数据再插回去试试：

# Insert the data for the Mazda RX4. This will also ouput a 1
dbExecute(conn, "INSERT INTO cars_data VALUES (21.0,6,160.0,110,3.90,2.620,16.46,0,1,4,4,'Mazda RX4')")
# See that we re-introduced the Mazda RX4 succesfully at the end
dbGetQuery(conn, "SELECT * FROM cars_data")

1

如您所见，最后一行代码将 Mazda RX4 添加到了表的末尾。在 R 中操作完 SQLite 数据库后，务必调用 dbDisconnect() 函数。这可以确保释放数据库连接所占用的资源，始终是良好实践。

# Close the database connection to CarsDB
dbDisconnect(conn)

结论

在本教程中，我们介绍了使用 RSQLite 在 R 中操作 SQLite 数据库所需的基本函数。如果使用得当，SQLite 数据库在与 R 脚本结合时将非常有用。R 与 SQLite 结合的强大之处之一是参数化查询：当您需要根据用户输入在 R Shiny 应用中展示信息而查询数据库时，就可以使用它。另一个与参数化查询相关的应用场景是虚拟助手或聊天机器人。如果您想进一步了解，建议您学习 DataCamp 的 《使用 Python 构建聊天机器人》课程。

通过追加数据框来写入 SQLite 表也同样强大。正如我在 SQLite 入门指南 教程中提到的，这一能力让我在进行社交网络分析项目时，能够在抓取过程中将多个感兴趣的 Twitter 用户的粉丝不断追加到同一张表中。我之所以使用 SQLite 数据库，是为了节省时间，避免因断电或 Windows 更新导致计算机被强制关闭而不得不从头开始。如果真的发生这类情况，我所要做的只是从数据库中最后一个用户的下一个用户重新开始抓取。具体来说，由于 Twitter API 的速率限制，抓取所有目标 Twitter 用户的粉丝总计耗时近 4 周。因此，如果因为断电或系统更新而不得不从零开始，将是非常不理想的。

一如既往，我鼓励您继续学习 SQLite 数据库及其在 R 中的交互方式。强烈建议您更深入地学习，并回看之前关于 Python 和命令行使用的教程，这将帮助您成为 SQLite 高手。继续学习吧，前路无限！