我有一个扩展名为.sqlite的SQLite数据库文件,是从Scraperwiki导出的。我应该如何将它导入R中,并将原始数据库表映射为单独的数据框?
data.frame
中:library("RSQLite")
## connect to db
con <- dbConnect(drv=RSQLite::SQLite(), dbname="YOURSQLITEFILE")
## list all tables
tables <- dbListTables(con)
## exclude sqlite_sequence (contains table information)
tables <- tables[tables != "sqlite_sequence"]
lDataFrames <- vector("list", length=length(tables))
## create a data.frame for each table
for (i in seq(along=tables)) {
lDataFrames[[i]] <- dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[i]], "'", sep=""))
}
对于其他遇到这篇文章的人,使用purr库完成从顶部答案开始循环的一个好方法是:
lDataFrames <- map(tables, ~{
dbGetQuery(conn=con, statement=paste("SELECT * FROM '", .x, "'", sep=""))
})
lDataFrames <- vector("list", length=length(tables))
将sgibb和primaj的答案相结合,给表格命名,并添加检索所有表格或特定表格的功能:
getDatabaseTables <- function(dbname="YOURSQLITEFILE", tableName=NULL){
library("RSQLite")
con <- dbConnect(drv=RSQLite::SQLite(), dbname=dbname) # connect to db
tables <- dbListTables(con) # list all table names
if (is.null(tableName)){
# get all tables
lDataFrames <- map(tables, ~{ dbGetQuery(conn=con, statement=paste("SELECT * FROM '", .x, "'", sep="")) })
# name tables
names(lDataFrames) <- tables
return (lDataFrames)
}
else{
# get specific table
return(dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tableName, "'", sep="")))
}
}
# get all tables
lDataFrames <- getDatabaseTables(dbname="YOURSQLITEFILE")
# get specific table
df <- getDatabaseTables(dbname="YOURSQLITEFILE", tableName="YOURTABLE")
con <- dbConnect(drv="SQLite", dbname="YOURSQLITEFILE")
更改为con <- dbConnect(drv=SQLite(), dbname="YOURSQLITEFILE")
。 - four-eyesdata.frames
,而是存储为一个大列表。在 for i in seq 行下面加上这句话就可以了:lDataFrames[[i]]<- data.frame(dbGetQuery(con, "SELECT * FROM '", tables[[i]))
。 - four-eyesclass(dbGetQuery(conn=con, statement=paste("SELECT * FROM '", tables[[1]], "'", sep="")))
显示data.frame
。 - sgibbdbDisconnect()
。 - robertspierre