Writing the data frame to MySql DB table

Question

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

Answer 1

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

Answer 2

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