Search code examples
rrmysql

Error When Insert data frame using RMySQL on R

Using R, I tried to insert a data frame. My script looks like below:

con <-  dbConnect(RMySQL::MySQL(), username = "xxxxxx", password = "xxxxxx",host = "127.0.0.1", dbname = "xxxxx")
dbWriteTable(conn=con,name='table',value=as.data.frame(df), append = TRUE, row.names = F)  
dbDisconnect(con)

WHen the script hit below line: 

dbWriteTable(conn=con,name='table',value=as.data.frame(df), append = TRUE, row.names = F)

I got an error like below:

Error in .local(conn, statement, ...) : could not run statement: Invalid utf8 character string: 'M'

I am not sure why this error occurred. This is a part of script that has been run well on another machine.

Please advise

Solution

  • You should create proper connection, then only can insert data frame to your DB. for creating connection username & password, host name & data base name should correct. same code only but i removed some parameter

    try this:

    mydb = dbConnect(MySQL(), user='root', password='password', dbname='my_database', host='localhost')

    i just insert Iris data in my_database

    data(iris)  
dbWriteTable(mydb, name='db', value=iris)

    i inserted iris data frame in the name of db in my_database