CODEWITHSUNDEEP
mysqlsqlr
John Dwyer
John Dwyer

Reputation: 189

Insert a variable into a SQL statement

Im having a dataframe that looks like this:

city <- c("Alkmaar", "Den Haag")
df <- data.frame(city)

What I would like to do now is write values into a mysql database. Therefore I do the following:

 library(DBI)
 con <- dbConnect(RMySQL::MySQL(),
                dbname = "match_predictions",
                host = "109.237.221.34",
                port = 3306,
                user = "**",
                password = "***")


 for(i in 1:nrow(df)){
  var1 <- city[i]
 dbGetQuery(con, "INSERT INTO weather(city) VALUES(var1)")
 }

But this trows an error:

  Error in .local(conn, statement, ...) : 
  could not run statement: Unknown column 'var1' in 'field list'

Any thoughts on how i can insert a variable into the database?

Upvotes: 0

Views: 2359

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269421

Try replacing the dbGetQuery line in your code with the dbGetQuery line shown here:

library(gsubfn)

fn$dbGetQuery(con, "INSERT INTO weather(city) VALUES('$var1')")

Upvotes: 0

JackStat
JackStat

Reputation: 1653

Just use paste0 to write the query for each city. I added in the single quotes and then you just need to make sure that you escape any single quotes in the city names if that occurs. 

for(i in 1:nrow(df)){
  var1 <- city[i]
  # excape single quotes
  var2 <- gsub("\\'", "\\'\\'", var1)
  dbGetQuery(con, paste0("INSERT INTO weather(city) VALUES('", var2, "')"))
}

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

try this:

library(DBI)
 con <- dbConnect(RMySQL::MySQL(),
                dbname = "match_predictions",
                host = "109.237.221.34",
                port = 3306,
                user = "**",
                password = "***")


 for(i in 1:nrow(df)){
  var1 <- city[i]
 dbGetQuery(con, cat("INSERT INTO weather(city) VALUES(", var1 <- city[i], ")"))
 }

Upvotes: 0

Related Questions