Insert an R data.frame into a mysql table in the mysql REPLACE fashion

Question

(Sorry if this is very basic, I'm just not able to figure out)

I have a table t sitting in a mysql db, which looks like this: (Name is the primary key)

Current Table in DB

Name   Balance
 Bob      100
 Ted      150
 Carl     130

I also have a data.frame in R reflecting changed balances

data.frame in R

Name   Balance
 Bob      90
 Ted      170
 Bill     50

I want to perform the equivalent of a mysql replace, so that my table gets updated to reflect the new balances

Desired Table in DB

Name   Balance
 Bob      90
 Ted      170
 Carl     130
 Bill     50

I'm currently using the RMySQL package- I tried doing this with dbWriteTable, but am not able to get this to work.

Eg1 ("Insert")

dbWriteTable(dbConnection, 'tableName', df, row.names=F, append=T)

Produces

Name   Balance
 Bob      100
 Ted      150
 Carl     130
 Bill     50

Eg2 ("Overwrite")

dbWriteTable(dbConnection, 'tableName', df, row.names=F, append=F, overwrite=T)

Produces

Name   Balance
 Bob      90
 Ted      170
 Bill     50

How do I do a replace?

Heparin

You should share what you've tried and explain how it didn't work. I'm not sure why you'd use dbWriteTable, though. Would you simply write a specific replace query and send it like any other select, delete, or insert query? – Levenson

Added more explanation of my attempts as suggested. What would that replace query look like? Note- both my data frame and mySQL tables are quite big, therefore it probably needs to be a "batch" kind of a replace – Heparin

Answer 1

if it is very basic then I suggest using basic SQL queries. I always use the packacke RODBC to connect to MySQL databases. Here is my example. 'conn' is the connection to your database:

library("RODBC")

df <- data.frame(Name = c('Bob', 'Ted', 'Bill'), Balance = c(90, 170, 50))

for (i in 1:nrow(df)) {
  query <- paste0("REPLACE tablename values ('", paste(df[i, ], collapse = "', '"), "')")
  sqlQuery(conn, query)
}

You can modify the query any way you like. The code above requires the table in MySQL to have the same columns as the dataframe. When you are working with larger dataframes I suggest using 'LOAD DATA (LOCAL) INFILE'.

Dredi