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.

Subdeacon

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. – Anthropomorphous

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. – Subdeacon

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 – Subdeacon

try assigning france$date to an object and then insert that. – Anthropomorphous

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. – Subdeacon

Also try specifying MySQL::dbWriteTable so that there is no issue in the namespace. – Anthropomorphous

Answer 1

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

Bellda

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 – Subdeacon

Answer 2

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)

Cowgirl