Insert a variable into a SQL statement

Question

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?

You can use something like sprintf("INSERT INTO weather(city) VALUES(%s)", var1). If var1 is a vector of cities, a vector of queries will be returned (one for each city). — steveb
– steveb, Commented Feb 22, 2016 at 16:04
Thanks @steveb but if I run: example <- dbSendQuery(con, sprintf("INSERT INTO weather(city) VALUES(%s)", x)) I still get the error: Error in .local(conn, statement, ...) : could not run statement: Unknown column 'h' in 'field list'. (this of course with h defined as variable — John Dwyer
– John Dwyer, Commented Feb 22, 2016 at 16:06
It worked for me, I was able to build the string "INSERT INTO weather(city) VALUES(Alkmaar)". In your case, I don't know what x is. — steveb
– steveb, Commented Feb 22, 2016 at 16:11

JackStat · Accepted Answer · 2016-02-22 16:07:46Z

1

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, "')"))
}

answered Feb 22, 2016 at 16:07

JackStat

1,6631 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JackStat Over a year ago

Awesome! I am glad that it worked. I run into this all the time hence the added attention to the single quotes :)

Fuzzy · Accepted Answer · 2016-02-22 16:05:48Z

0

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], ")"))
 }

answered Feb 22, 2016 at 16:05

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

Comments

G. Grothendieck · Accepted Answer · 2016-02-22 16:14:34Z

0

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')")

answered Feb 22, 2016 at 16:14

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Collectives™ on Stack Overflow

Insert a variable into a SQL statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related