6

I want to insert data in MySQL from a dataframe in R. I managed to connect without problems from R to MySQL using dbConnect, however when I try to insert my data using dbWriteTable I keep getting the error

unable to find an inherited method for function 'dbWriterTable' for signature '"integer", "character", "data.frame"'.

Now, I've tried the proposed solution mentioned here How to resolve this error--dbWriteTable() but this solution doesn't work for me. My original code was 

dbWriteTable(conn, "Date", france$Date)

because my table in MySQL is called Date and my dataframe in R is called france and has a column Datecontaining dates (the type of this column is date too). After the proposed solution, my code becomes

dbWriteTable(conn, "Date", data.frame(dat=france$Date), row.names=FALSE, append=TRUE)

but I'm getting the same error. I tried adding field.types=list("date") as mentioned in the following solution RMySQL dbWriteTable with field.types but I get the same error.

Finally, I've tried to use dbSendQuery together with paste() to insert manually my data as suggested here How to insert integer values with query in MySQL in R? but I get the same error again!

This is driving me crazy. Any help will be appreciated.

Community
  • 1
  • 1
antonioACR1
  • 1,303
  • 2
  • 15
  • 28
  • Try this:`dbWriteTable(con, "table_name", r_df, field.types = NULL, row.names = FALSE, overwrite = FALSE, append = TRUE, allow.keywords = FALSE)`. Also you have variables named as Date which could potentially give u an error. – Chirayu Chamoli Nov 08 '16 at 06:58
  • By "r_df" do you mean france$Date? My dataframe is called "france" and the column is called "Date". If so, then I'm still getting the same error with your suggested code. – antonioACR1 Nov 08 '16 at 07:14
  • I'm having the same problem with other examples. For example, I defined df <- data.frame(c(1,2),c(3,4)) and I created a table in MySQL as follows: CREATE TABLE mytable (ID int); then I try dbWriteTable(conn,"mytable",df$a) but I get the same error. Also when I try dbWriteTable(conn, "mytable", df$a, field.types = NULL, row.names = FALSE, overwrite = FALSE, append = TRUE, allow.keywords = FALSE) is the same problem – antonioACR1 Nov 08 '16 at 07:25
  • 1
    try assigning france$date to an object and then insert that. – Chirayu Chamoli Nov 08 '16 at 07:29
  • Nothing changed. I defined `df<-france$Date` and then tried `dbWriteTable(conn, "Date",df)` and `dbWriteTable(conn, "Date", df, field.types = NULL, row.names = FALSE, overwrite = FALSE, append = TRUE, allow.keywords = FALSE)`. I'm getting the same error in both cases. – antonioACR1 Nov 08 '16 at 07:41
  • Also try specifying MySQL::dbWriteTable so that there is no issue in the namespace. – Chirayu Chamoli Nov 08 '16 at 07:46

2 Answers2

8

I got the same error because the object I was providing to dbWriteTable() wasn't a data.frame.

To fix it, I simply convert the object to a data.frame first

dat <- as.data.frame(dat)
dbWriteTable(con, "mydbtable", dat)
stevec
  • 41,291
  • 27
  • 223
  • 311
3

Did you try connecting to your database? I was having the same error and when I checked connection, it was broken. Make sure you check the connection to db and run the dbWriteTable command next. It should work

linthum
  • 333
  • 1
  • 8
  • 22
  • 2
    I remember I restarted MySQL and the problem was gone. So I guess your answer works. Interesting to see a reply 4 years later lol – antonioACR1 Mar 09 '20 at 13:21