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

Community · Accepted Answer · 2017-05-23 11:46:14Z

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

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Aug 29, 2012 at 23:05

MadSeb

8,33424 gold badges83 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Miquel Over a year ago

It didn't worked to me. The correct syntax is dbWriteTable(connection, "MyTable", data.frame, append = TRUE). The docs: cran.r-project.org/web/packages/RMySQL/RMySQL.pdf

Apricot Over a year ago

Adding row.names=FALSE will help seamless transfer

Dirk is no longer here · Accepted Answer · 2012-08-29 21:42:48Z

7

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

answered Aug 29, 2012 at 21:42

Dirk is no longer here

370k60 gold badges668 silver badges742 bronze badges

Comments

bdforbes · Accepted Answer · 2018-02-05 09:23:52Z

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)

edited Feb 5, 2018 at 9:23

bdforbes

1,5541 gold badge14 silver badges34 bronze badges

answered Feb 23, 2017 at 14:53

vinit kumar singh

211 bronze badge

1 Comment

eod Over a year ago

I've spent hours without success trying to run DBI's function of the same name. However, RmySQL::dbWiteTable worked immediately.

Collectives™ on Stack Overflow

Writing the data frame to MySql DB table

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related