vlambda博客
学习文章列表

学习如何在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，多一点数据认知，让他们的科研上一个台阶：

• ，快速了解一些生物信息学应用图表
• ，你的生物信息学第一课