14

How to write the data frame in R into MySQL?

dateTime            host    authId     sessionId      status                    action 
2012-08-22 14:58:23 foo.com 221501398 2c10b368ae23ba3        questions#instant_answers
2012-08-22 14:58:23 foo.com 221501398 22c10b368ae23                      questions#new
2012-08-22 14:58:23 foo.com 221501398 01a36f64bd3f80c                     sessions#new

I want to write the dataframe all at once to the MySQL DB table. I have used RMySql package to connect and establish the connection.

Thanks

Mihai Iorga
  • 39,330
  • 16
  • 106
  • 107
pandhale
  • 199
  • 1
  • 3
  • 8

3 Answers3

30

Use the dbWriteTable function. It looks like this : 

dbWriteTable(connection, value = data.frame, name = "MyTable", append = TRUE )

The function is well documented.

P.S Also look at: RMySQL dbWriteTable with field.types

Community
  • 1
  • 1
MadSeb
  • 7,958
  • 21
  • 80
  • 121
  • 1
    It didn't worked to me. The correct syntax is dbWriteTable(connection, "MyTable", data.frame, append = TRUE). The docs: http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf – Miquel Feb 25 '14 at 15:15
  • Adding `row.names=FALSE` will help seamless transfer – Apricot Nov 19 '20 at 10:34
7

See help(dbSendQuery) for generic update ... statements in SQL, and help(dbWriteTable) to write an entire data frame to a new table.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
1

It worked for me using the command below. Note that this will append the rows in yourtable to the database named yourTableInMySQL.

library(RMySQL)
dbWriteTable(con, "yourTableinMySQL", yourtable, append = TRUE)
bdforbes
  • 1,486
  • 1
  • 13
  • 31
vinit kumar singh
  • 21
  • 1
  • I've spent hours without success trying to run DBI's function of the same name. However, RmySQL::dbWiteTable worked immediately. – eod Dec 14 '22 at 07:24