在R中使用SQLite进行简单数据库管理

2022的实习生和学徒培养开始啦，所以大家又可以看到了几百个全新的生物信息学知识点整理分享的简书，公众号，语雀账号了。挑选其中一些持久的，优秀的小伙伴的笔记转载到我们《生信技能树》公众号平台：

• 
  

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

创建数据库和表

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

# Load the RSQLite Library
library(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 directory
conn <- dbConnect(RSQLite::SQLite(), "CarsDB.db")

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

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

之后，可以使用函数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'

如果你有多个数据，可以通过在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)
##"Cars_and_Makes" "cars_data"

确保所有数据都在新表中

dbGetQuery(conn, "SELECT * FROM Cars_and_Makes")

执行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 table
dbGetQuery(conn, "SELECT * FROM cars_data LIMIT 5")

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

要将查询的结果存储，以便在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 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

不返回表格数据的查询

可能希望执行不一定返回表格数据的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.
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")

关闭

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

# Close the database connection to CarsDB
dbDisconnect(conn)

文末友情宣传

强烈建议你推荐给身边的博士后以及年轻生物学PI，多一点数据认知，让他们的科研上一个台阶：

• 数据挖掘（GEO,TCGA,单细胞）2022年4月场，快速了解一些生物信息学应用图表
• 生信入门课-2022年4月场，你的生物信息学第一课