CODEWITHSUNDEEP
mysqlrdataframecreate-table
cone001
cone001

Reputation: 923

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

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

Upvotes: 5

Views: 9763

Answers (2)

Soumyanath Chatterjee
Soumyanath Chatterjee

Reputation: 51

You need to have mysql client codes installed before you try installing RMySQL. Without knowing what OS and version you use it is really not possible to give any better answer.

Upvotes: 0

Matt Bannert
Matt Bannert

Reputation: 28264

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/

Upvotes: 11

Related Questions