R mySQL Sqlite SQL Generator
R Insert SQL Generator
I need a generic way to create SQL statements within R. I prefer to develop a convention to follow that is re-usable. I hate hand-writing one off SQL SELECT and Insert statements, every time I need to push or pull data from the database into R.
#build an SQL insert query based on the data frame that gets passed in
getInsertSqlQuery <- function(tableName, dataFrame){
columns <- paste(colnames(dataFrame), sep="", collapse=", ")
values <- paste(":", colnames(dataFrame), sep="", collapse=", ")
sql <- paste("INSERT INTO ", tableName, " (", columns, ") VALUES (", values, ")", sep="")
sql
}
R Insert SQL Prepared Statement
Now that I have a working SQL generator, I need a convenient way to insert the data into the Database using RMySQL’s and SQLite’s interfaces.
#generic RSQLite & RMysql SQL Insert
insertData <- function (tableName, dataFrame){
drv <- dbDriver("SQLite")
#works with RMySQL too
con <- dbConnect(drv, dbname="sqlite/blogger.db")
sql <- getInsertSqlQuery(tableName, dataFrame)
dbBeginTransaction(con)
dbGetPreparedQuery(con, sql, dataFrame)
dbCommit(con)
}
#From your application call
insertData(tableName="comments",dataFrame=data.frame("dollars"=c(3.44),"year"=c('2012'),units=c(5))
If you need to manipulate your data do that prior to calling insertData.
Advertisement
