vlambda博客
学习文章列表

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




下面是因INFORnotes 分享


学习如何在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")


car make
Camaro Chevrolet
California Ferrari
Mustang Ford
Explorer Ford
Corolla Toyota
Lancer Mitsubishi
Sportage Kia
XE Jaguar


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


car_names hp cyl
Mazda RX4 110 6
Mazda RX4 Wag 110 6
Merc 280 123 6
Merc 280C 123 6
Merc 450SE 180 8
Merc 450SL 180 8
Merc 450SLC 180 8
Maserati Bora 335 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


car_names mpg cyl
Mazda RX4 21.0 6
Mazda RX4 Wag 21.0 6
Hornet 4 Drive 21.4 6
Hornet Sportabout 18.7 8
Valiant 18.1 6
Merc 280 19.2 6
Pontiac Firebird 19.2 8
Ferrari Dino 19.7 6


不返回表格数据的查询

可能希望执行不一定返回表格数据的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,多一点数据认知,让他们的科研上一个台阶:

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