How to export a data frame in R to a table in MySQL

Question

I tried sqlSave() in RODBC, but it runs super slowly. Is there an alternative way of doing this?

Answer 1

You could look at the package RMySQL. I am using it and it offers quite some convenience loading and reading data from a MySQL database. That being said it is limited in the queries you can use (e.g. HAVING is not possible IIRC). I can't say it's super-quick or my data is that big, but it's several 2-digits MB of text and it's ok. Depends on what you expect. However it's convenient:

con <- dbConnect(MySQL(), user="user", password="pass", 
    dbname="mydb", host="localhost",client.flag=CLIENT_MULTI_STATEMENTS)

dbListTables(con)
yourtable <- dbReadTable(con,"sometable")
# write it back
dbWriteTable(con,"yourTableinMySQL",yourtable,overwrite=T)
# watch out with the overwrite argument it does what it says :)
dbDisconnect(con)

yourtable will be a data.frame. Sometimes it bugs me that the modes are not set like i'd expect, but I have a custom made function for that. Just need to improve it then I'll post it here.

http://cran.r-project.org/web/packages/RMySQL/