将MySQL转储导入R（不需要MySQL服务器）

一尘不染

将MySQL转储导入R（不需要MySQL服务器）

sql

像包RMySQL，并sqldf允许一个与本地或远程数据库服务器连接。我创建一个便携式项目，该项目涉及的情况下导入SQL数据（或设备）不总是可以访问正在运行的服务器，但
你总是可以访问数据库的最新.SQL转储。

目标似乎很简单：在不涉及MySQL服务器的情况下将.sql转储导入R。
更具体地说，我想创建一个列表列表，其中的元素与.sql转储中定义的任何数据库相对应（可能有多个），而这些元素又由这些数据库中的表组成。

为了重现这一点，让我们在此处获取样本sportsdb
SQL文件-如果将其解压缩，则称为sportsdb_sample_mysql_20080303.sql。

有人会认为sqldf可以做到这一点：

read.csv.sql('sportsdb_sample_mysql_20080303.sql', sql="SELECT * FROM addresses") Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such table: addresses

即使在转储中肯定有一个表地址也是如此。sqldf列表上的该帖子提到了相同的错误，但没有解决方案。

然后sql.reader，软件包中有一个函数ProjectTemplate，看起来很有希望。随便看看，该功能的源代码可以在这里找到，它假设一个正在运行的数据库服务器并依赖于RMySQL…而不是我所需要的。

所以…我们似乎用光了所有的选项。蜂巢的任何帮助表示赞赏！

（再次重申，我不是
找依靠进入到SQL服务器的解决方案;与很容易dbReadTable从RMySQL，包我非常想绕过服务器，并从.SQL转储文件直接获取数据。）

阅读 134

2021-05-05

共1个答案

一尘不染

根据要从表中提取的内容，这是如何处理数据的方法

numLines <- R.utils::countLines("sportsdb_sample_mysql_20080303.sql")
# [1] 81266

linesInDB <- readLines("sportsdb_sample_mysql_20080303.sql",n=60)

然后，您可以执行一些正则表达式来获取表名（在CREATE TABLE之后），列名（在第一括号之间）和VALUES（在CREATE
TABLE之后和第二括号之间的行）

编辑：响应OP的回答，如果我正确解释python脚本，它也将逐行读取它，过滤INSERT
INTO行，解析为csv，然后写入文件。这与我最初的建议非常相似。我的版本在R中。如果文件太大，最好使用一些其他R包以大块形式读取文件

options(stringsAsFactors=F)
library(utils)
library(stringi)
library(plyr)

mysqldumpfile <- "sportsdb_sample_mysql_20080303.sql"

allLines <- readLines(mysqldumpfile)
insertLines <- allLines[which(stri_detect_fixed(allLines, "INSERT INTO"))]
allwords <- data.frame(stri_extract_all_words(insertLines, " "))
d_ply(allwords, .(X3), function(x) {
    #x <- split(allwords, allwords$X3)[["baseball_offensive_stats"]]
    print(x[1,3])

    #find where the header/data columns start and end
    valuesCol <- which(x[1,]=="VALUES")
    lastCols <- which(apply(x, 2, function(y) all(is.na(y))))
    datLastCol <- head(c(lastCols, ncol(x)+1), 1) - 1

    #format and prepare for write to file
    df <- data.frame(x[,(valuesCol+1):datLastCol])
    df <- setNames(df, x[1,4:(valuesCol-1)])
    #type convert before writing to file otherwise its all strings
    df[] <- apply(df, 2, type.convert)
    #write to file
    write.csv(df, paste0(x[1,3],".csv"), row.names=F)
})

2021-05-05