How to fix dbWriteTable error "unable to find an inherited method for function 'dbWriterTable' for signature...?"

Question

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.

Answer 1

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)