CODEWITHSUNDEEP
r
sasir
sasir

Reputation: 177

Append rows to MYSQL table from R

I have table 'df1' in mysql , now i am trying to append next set of rows (df2) to the df1,

I am trying to append the df2 to df1 as below

connection <- dbConnect(MySQL(), user = 'root', password = 'pass',
                        host = 'localhost', dbname = 'data')

dbWriteTable(connection,"df1",df2,row.names=T,append=T)

ERROR:could not run statement: Table 'df1' already exists

please suggest Any modifications in the above code

Thanks in advance

Upvotes: 2

Views: 1995

Answers (1)

Stuart Ball
Stuart Ball

Reputation: 41

The following works fine for me:

library(RMySQL)
library(stringi)
con <- dbConnect(MySQL(), user = 'test', password = '****',
                 host = 'localhost', dbname = 'test')
dbf1 <- data.frame(x=round(runif(500, 1, 1000),0),
                   y=rnorm(500, mean=100, sd=21),
                   z=stri_rand_strings(500, 5),
                   stringsAsFactors = FALSE)
dbWriteTable(con, "test1", dbf1)
dbDisconnect(con)

and then:

library(RMySQL)
library(stringi)
con <- dbConnect(MySQL(), user = 'test', password = '****',
                 host = 'localhost', dbname = 'test')
dbf2 <- data.frame(x=round(runif(300, 10000, 11000),0),
                   y=rnorm(300, mean=100, sd=21),
                   z=stri_rand_strings(300, 5),
                   stringsAsFactors = FALSE)
dbWriteTable(con, "test1", dbf2, append = TRUE)
dbGetQuery(con, "SELECT count(x) FROM test1")
dbDisconnect(con)

The query returns:

  count(x)
1      800

showing that the second set of 300 rows has been appended as expected.

You need to give a working example of the error that we can run. The immediate thing that springs to mind is that using T and F as abbreviations for TRUE and FALSE is bad practice! Whilst TRUE and FALSE are reserved words in R and cannot be changed, T and F are ordinary variables and can be modified. So, without seeing your whole script, there is no guarantee that row.names=T,append=T actually means that append=TRUE!

Upvotes: 1

Related Questions