我正在try 从R脚本创建新的MySQL数据库,以保存采样函数在其上生成的新表.该函数从用户处获取连接信息,对指定表进行采样,采样后返回表. 下面显示了我的函数的快照,以便更清楚地说明这一 idea . `
SQLRowRandomSampling <- function(username,passwords,databaseName,TableName,
SamplingSize){
#' establish the connection with the selected database
ConnectedDb <- dbConnect(RMariaDB::MariaDB(), user=username,
password=passwords, dbname=databaseName,
host='localhost')
#' checking if the entered table exist in the selected database
x <- tryCatch({
dbframe <- dbReadTable(ConnectedDb,TableName)},
error = function(e){
#' if not stop the function
stop('Table does not exist! Try again please.')
}
)
#' get number of rows in the selected table
actualRowNumber <- nrow(dbframe)
# check if the entered sampling size is valid
if(SamplingSize<1){
stop("Entered sampling size must be a valid number")}
else if (SamplingSize >= actualRowNumber){
stop("You should eneter a sampling size that is less than the rows number
of the selected table")}
# start row random sampling
RandomRowSmpling<- sample_n(dbframe, SamplingSize)
# return the result
return(RandomRowSmpling)}
返回的表RandomRowSmping应该保存在新建的MySQL数据库中,供以后使用.
我知道如何创建新的SQLite数据库,方法如下:
#' create new database named Sampling_Database
ConnectedCreatedDb <- dbConnect(drv = RSQLite::SQLite(),
dbname= 'Sampling_Database')
然而,对于我的项目建议,它应该是MySQL而不是SQLite.你知道如何创建新的MySQL数据库来保存结果表吗?
此外,我知道可以使用以下命令将创建的表添加到原始MySQL数据库中:
dbWriteTable(conn = ConnectedDb, name = "RandomRowSmpling"
, value = RandomRowSmpling)
但是,不幸的是,我不能更改给定的数据库.
谢谢你的帮助!