Insert in RMySQL from data frame

Question

Im trying to add data to MySQL table by using RMySQL. I only need to add one row at a time and it's not working. What I'm trying to do is this.

dbGetQuery(con,"INSERT INTO names VALUES(data[1,1], data[1,2])")

so what I'm doing is that I have values in data frame that is named as "data" and I need to put them into mysql table. before that I will check them if they are already in the table or not and if they are not then I will add them, but that way it isn't working. The data is read from .csv file by read.csv .

Solution 1

You can use paste to construct that actual query.

dat <- matrix(1:4, 2, 2)
query <- paste("INSERT INTO names VALUES(",data[1,1], ",", data[1,2], ")")
query
#[1] "INSERT INTO names VALUES( 1 , 3 )"
dbGetQuery(con, query)

# If there are a lot of columns this could be tedious...
# So we could also use paste to add all the values at once.
query <- paste("INSERT INTO names VALUES(", paste(data[1,], collapse = ", "), ")")
query
#[1] "INSERT INTO names VALUES( 1, 3 )"

Solution 2

You could try with:

dbWriteTable(names, data[1,],append=True)

as the DBI package details

Comments

[TheLaama]

Thankyou. That helped me :)