R中的SQLite用法权威介绍

正如我在上一篇教程《 SQLite入门指南》的最后所提到的那样, 将SQLite数据库与R和Python一起使用时功能最强大。到目前为止, 在srcmini中, 你已经了解了如何使用Python处理SQLite数据库(请参阅Sayak Paul撰写的Python中的SQLite教程, 以了解如何通过Python中的sqlite3软件包来操作SQLite数据库)。但是, 在本教程中, 我们将重点介绍如何使用RSQLite包在R中使用SQLite数据库。

我们将介绍如何执行基本任务的基础知识, 例如将查询发送到SQLite数据库或使用RSQLite创建表。此外, 我将介绍如何使用不返回表格结果的参数化查询和操作(例如INSERT或DELETE)。

创建数据库和表

你可能已经猜到的第一步是创建一个数据库。 RSQLite可以创建短暂的内存中瞬态SQLite数据库, 就像打开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数据库的连接
• 名称：你要用于表格的名称
• 值：你要插入的数据。它应该是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. “汽车与制造者”
2. ‘cars_data’

现在让我们确保所有数据都在新表中：

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

执行SQL查询

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

• conn：与SQLite数据库的连接
• 查询：要作为字符串执行的SQL查询

为了进一步展示使用RSQLite执行SQL查询的功能, 让我们来看一下cars_data表上的更多查询示例：

注意：切记, 通过RSQLIte, 你可以执行对SQLite数据库有效的任何查询, 从简单的SELECT语句到JOINS(除了RIGHT OUTER JOINS和FULL OUTER JOINS, 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, 作为数据帧在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脚本中结合使用SQLite数据库是一个非常有用的工具。在参数化查询中可以看到R和SQLite之间交集的强大功能的一个示例, 如果你需要查询数据库以基于R Shiny应用程序内的用户输入来显示信息, 则可以使用该参数。另一个示例用例或参数化查询可以是虚拟助手或聊天机器人。如果你想了解更多有关此的知识, 建议你阅读srcmini的”使用Python构建聊天机器人”课程。

通过附加数据框来编写SQLite表也非常强大。正如我在《 SQLite初学者指南》教程中指出的那样, 此功能使我可以通过在收集他们时将感兴趣的多个Twitter用户的关注者追加到表中来完成社交网络分析项目。为此, 我使用了SQLite数据库, 以免因断电或Windows更新而从头启动而节省时间, 这可能会强行关闭计算机。如果发生了类似的事情, 我要做的就是重新开始收集用户的关注者, 该关注者紧随我在数据库中拥有的最后一个关注者。准确地说, 由于Twitter API的速率限制, 聚集我所有感兴趣的Twitter用户关注者的计算时间花了将近4周的时间。因此, 由于断电或Windows更新而从零开始将是非常不可取的。

与往常一样, 我鼓励你继续学习SQLite数据库以及如何通过R与它们交互。我强烈建议你更深入地检查以前的教程, 其中涵盖了它们在Python和命令行中的用法, 这可以进一步帮助你成为完整的SQLite向导。保持学习;天空才是极限！