正如我在上一篇教程《 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")

mpg	cyl	DISP	hp	drat	wt	qsec	vs	am	gear	carb	car_names
21.0	6	160	110	3.90	2.620	16.46	0	1	4	4	Mazda RX4
21.0	6	160	110	3.90	2.875	17.02	0	1	4	4	Mazda RX4 Wag
22.8	4	108	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258	110	3.08	3.215	19.44	1	0	3	1	Hornet 4 Drive
18.7	8	360	175	3.15	3.440	17.02	0	0	3	2	Hornet Sportabout
18.1	6	225	105	2.76	3.460	20.22	1	0	3	1	Valiant

创建数据库并且数据格式正确后, 就可以继续使用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")

汽车	使
卡玛洛	雪佛兰
加利福尼亚州	法拉利
野马	福特汽车
探险者	福特汽车
花冠	丰田汽车
发射	三菱
运动型	起亚
XE	美洲虎

执行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")

mpg	圆柱体	DISP	hp	德拉特	wt	QseC的	vs	am	齿轮	碳水化合物	car_names
21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4	马自达RX4
21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	马自达RX4 Wag
22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	大黄蜂4驱动器
18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	大黄蜂Sportabout
18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	英勇
14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	除尘器360
24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	Merc 240D
22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	Merc 230
19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	Merc 280

# 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")

car_names	hp	圆柱体
大黄蜂Sportabout	175	8
除尘器360	245	8
Merc 450SE	180	8
Merc 450SL	180	8
Merc 450SLC	180	8
凯迪拉克弗利特伍德	205	8
林肯大陆	215	8
克莱斯勒帝国	230	8
道奇挑战者	150	8
AMC标枪	150	8
卡玛洛z28	245	8
庞蒂亚克火鸟	175	8
福特豹L	264	8
玛莎拉蒂博拉	335	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)")

car_names	hp	圆柱体
马自达RX4	110	6
马自达RX4 Wag	110	6
Merc 280	123	6
Merc 280C	123	6
Merc 450SE	180	8
Merc 450SL	180	8
Merc 450SLC	180	8
玛莎拉蒂博拉	335	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")

圆柱体	average_hp	average_mpg
4	82.63636	26.66364
6	122.28571	19.74286
8	209.21429	15.10000

【R中的SQLite用法权威介绍】要将查询结果存储为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)

圆柱体	average_hp
4	82.63636
6	122.28571
8	209.21429

‘ 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

car_names	mpg	圆柱体
马自达RX4	21.0	6
马自达RX4 Wag	21.0	6
大黄蜂4驱动器	21.4	6
大黄蜂Sportabout	18.7	8
英勇	18.1	6
Merc 280	19.2	6
庞蒂亚克火鸟	19.2	8
法拉利迪诺	19.7	6

如你所见, 发送普通查询和参数化查询之间的唯一区别在于查询中的占位符值(> =？)和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_dataWHEREmpg> = ?ANDhp> = ? ANDwt> = ?"

car_names	mpg	hp	wt
大黄蜂Sportabout	18.7	175	3.440
Merc 450SE	16.4	180	4.070
Merc 450SL	17.3	180	3.730
庞蒂亚克火鸟	19.2	175	3.845
法拉利迪诺	19.7	175	2.770

上面的函数可能是基本的, 但是它说明了如何编写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")

mpg	圆柱体	DISP	hp	德拉特	wt	QseC的	vs	am	齿轮	碳水化合物	car_names
21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4	马自达RX4
21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	马自达RX4 Wag
22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	大黄蜂4驱动器
18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	大黄蜂Sportabout
18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	英勇
14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	除尘器360
24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	Merc 240D
22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	Merc 230
19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	Merc 280

1

mpg	圆柱体	DISP	hp	德拉特	wt	QseC的	vs	am	齿轮	碳水化合物	car_names
21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	马自达RX4 Wag
22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	大黄蜂4驱动器
18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	大黄蜂Sportabout
18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	英勇
14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	除尘器360
24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	Merc 240D
22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	Merc 230
19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	Merc 280
17.8	6	167.6	123	3.92	3.440	18.90	1	0	4	4	Merc 280C

现在, 让我们尝试将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

mpg	圆柱体	DISP	hp	德拉特	wt	QseC的	vs	am	齿轮	碳水化合物	car_names
21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	马自达RX4 Wag
22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	Datsun 710
21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	大黄蜂4驱动器
18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	大黄蜂Sportabout
18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	英勇
14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	除尘器360
24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	Merc 240D
22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	Merc 230
19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	Merc 280
17.8	6	167.6	123	3.92	3.440	18.90	1	0	4	4	Merc 280C
16.4	8	275.8	180	3.07	4.070	17.40	0	0	3	3	Merc 450SE
17.3	8	275.8	180	3.07	3.730	17.60	0	0	3	3	Merc 450SL
15.2	8	275.8	180	3.07	3.780	18.00	0	0	3	3	Merc 450SLC
10.4	8	472.0	205	2.93	5.250	17.98	0	0	3	4	凯迪拉克弗利特伍德
10.4	8	460.0	215	3.00	5.424	17.82	0	0	3	4	林肯大陆
14.7	8	440.0	230	3.23	5.345	17.42	0	0	3	4	克莱斯勒帝国
32.4	4	78.7	66	4.08	2.200	19.47	1	1	4	1	菲亚特128
30.4	4	75.7	52	4.93	1.615	18.52	1	1	4	2	本田思域
33.9	4	71.1	65	4.22	1.835	19.90	1	1	4	1	丰田卡罗拉
21.5	4	120.1	97	3.70	2.465	20.01	1	0	3	1	丰田电晕
15.5	8	318.0	150	2.76	3.520	16.87	0	0	3	2	道奇挑战者
15.2	8	304.0	150	3.15	3.435	17.30	0	0	3	2	AMC标枪
13.3	8	350.0	245	3.73	3.840	15.41	0	0	3	4	卡玛洛z28
19.2	8	400.0	175	3.08	3.845	17.05	0	0	3	2	庞蒂亚克火鸟
27.3	4	79.0	66	4.08	1.935	18.90	1	1	4	1	菲亚特X1-9
26.0	4	120.3	91	4.43	2.140	16.70	0	1	5	2	保时捷914-2
30.4	4	95.1	113	3.77	1.513	16.90	1	1	5	2	莲花欧罗巴
15.8	8	351.0	264	4.22	3.170	14.50	0	1	5	4	福特豹L
19.7	6	145.0	175	3.62	2.770	15.50	0	1	5	6	法拉利迪诺
15.0	8	301.0	335	3.54	3.570	14.60	0	1	5	8	玛莎拉蒂博拉
21.4	4	121.0	109	4.11	2.780	18.60	1	1	4	2	沃尔沃142E
21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4	马自达RX4

如你所见, 最后一行代码将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向导。保持学习; 天空才是极限！

推荐阅读

• 

  营销商学院是干什么的 营销商学院有哪些
• 

  梦幻模拟战手游重装枪兵图鉴 枪兵重装枪兵介绍
• 

  招商银行借记卡是什么 工商银行借记卡是什么
• 

  37岁的c罗 C罗37岁身材，c罗36岁的身体
• 

  财达委托交易单独下单，财达证券业务办理
• 

  如何在切换服务器后恢复数据？ 切换服务器怎么恢复
• 

  摄像头|夜拍也要看出彩华晟高清摄像头再升级
• 

  让你的打印机重生 爱普生XP15010维护箱清零软件使用教程
• 

  圣女果吃了会变白吗
• 

  qq音乐|QQ音乐接入鸿蒙系统，支持跨设备听歌
• 

  面试题如何运用新媒体，如何利用新媒体开展工作
• 

  15公分等于几寸 十厘米乘十五厘米是多少寸
• 

  桑葚酒有什么功效？桑葚酒的功效与作用
• 

  别克gl8排挡杆怎么拆
• 

  青瓜汁的做法步骤图解
• 

  球衣一件值千金！球员的球衣并非每场全新，送人还得自掏腰包
• 

  新捷达1.4低配全部办齐要多少钱？
• 

  原神申鹤E技能好用吗 原神申鹤E技能伤害解析
• 

  mysql怎么添加用户名 用mysql数据库添加用户
• 

  cf换购有多把火麒麟换购了会全部回收吗？

• 电子表格中的数据透视表
• SQL中的清理数据
• 如何在电子表格中进行加，减，除和乘运算
• 电子表格中的条件格式
• PostgreSQL中的实例化视图
• Python中的参数解析
• win7如何显示隐藏文件? 本文教您显示办法
• 怎样给电脑中添加字体 本文教您添加的办法
• 系统之家win7纯净版最新推荐