学习如何在R中使用SQLite，这是一种非常轻量级的关系数据库管理系统(RDBMS)。

创建数据库和表

第一步是创建数据库。使用dbConnect()函数为mtcars数据集创建一个适当的数据库。

# Load the RSQLite Librarylibrary(RSQLite)# Load the mtcars as an R data frame put the row names as a column.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 directoryconn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")

一旦创建了数据库，就可以继续使用dbWriteTable()函数在数据库中创建一个表。这个函数可以接受多个参数:

conn:连接到你的SQLite数据库
name:您想要用于表的名称
value:插入的数据

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

# Write the mtcars dataset into a table names mtcars_datadbWriteTable(conn, "cars_data", mtcars)# List all the tables available in the databasedbListTables(conn)##'cars_data'

如果你有多个数据，可以通过在dbWriteTable()中设置可选参数append = TRUE，在已有的表中添加更多的数据。例如，可以通过添加两个不同的数据来创建一个新的表，其中包含一些汽车和制造商。

# Create toy data framescar <- 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 listdfList <- list(df1,df2)# Write a table by appending the data frames inside the listfor(k in 1:length(dfList)){ dbWriteTable(conn,"Cars_and_Makes", dfList[[k]], append = TRUE)}# List all the TablesdbListTables(conn)##"Cars_and_Makes" "cars_data"

确保所有数据都在新表中

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

carmakeCamaroChevroletCaliforniaFerrariMustangFordExplorerFordCorollaToyotaLancerMitsubishiSportageKiaXEJaguar

执行SQL查询

可以使用dbGetQuery()执行有效的SQL查询，该函数有以下参数：

conn:连接SQLite数据库
query:执行的SQL查询

NOTE:通过RSQLIte，可以执行任何查询，从简单的SELECT语句到JOINS(除了RIGHT OUTER JOINS和FULL OUTER JOINS，这是在SQLite中不支持的)。

# Gather the first 5 rows in the cars_data tabledbGetQuery(conn, "SELECT * FROM cars_data LIMIT 5")

car_nameshpcylMazda RX41106Mazda RX4 Wag1106Merc 2801236Merc 280C1236Merc 450SE1808Merc 450SL1808Merc 450SLC1808Maserati Bora3358

要将查询的结果存储，以便在R中继续执行进一步的操作，只需将查询的结果赋值给一个变量即可。

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

加入变量查询

从R操作SQLite数据库的最大优点之一是能够使用参数化查询。也就是说，能够使用R工作空间中可用的变量查询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 cylindersmpg <- 18cyl <- 6Result <- dbGetQuery(conn, 'SELECT car_names, mpg, cyl FROM cars_data WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))Result

car_namesmpgcylMazda RX421.06Mazda RX4 Wag21.06Hornet 4 Drive21.46Hornet Sportabout18.78Valiant18.16Merc 28019.26Pontiac Firebird19.28Ferrari Dino19.76

不返回表格数据的查询

可能希望执行不一定返回表格数据的SQL查询。这些操作的例子包括插入、更新或删除表记录。为此，我们可以使用函数dbExecute()，它以一个SQLite数据库连接和一个SQL查询作为参数。

# Visualize the table before deletiondbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")# Delete the column belonging to the Mazda RX4.dbExecute(conn, "DELETE FROM cars_data WHERE car_names = 'Mazda RX4'")# Visualize the new table after deletiondbGetQuery(conn, "SELECT * FROM cars_data LIMIT 10")

关闭

在R中完成了SQLite数据库的操作，调用函数dbDisconnect()是很重要的。这确保释放了数据库连接一直在使用的资源。

# Close the database connection to CarsDBdbDisconnect(conn)

转载自《生信技能树》，如有侵权，请联系删除。