CODEWITHSUNDEEP
mysqlsqlr
nohelsss
nohelsss

Reputation: 3

How to query on a for loop in R

I have a sql database that has two columns, one with person IDs and one with their info. I have a data frame of patient IDs that I wish to query from the sql database, I want to query them by using a for loop and I want the data that is obtained from the for loop to be transferred into a table, but have been unsuccessful.

I tried different variations of a for loop, but they all lead to errors of string vectors or they only put the most recent information in the table, omitting any other information of any ID before. 

for (i in CancerMet2){
  x <- dbGetQuery(mydb, paste('SELECT * FROM kv WHERE Person = ', i, ';'))
  info = fromJSON(x$info)
  print(i)
}

Then I get this error:

Error in result_create(conn@ptr, statement) : Expecting a single string value: [type=character; extent=90907].

The expected results should be a table with the people's ID's and corresponding info of the selected people IDs I have in a data frame.

Upvotes: 0

Views: 301

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider DBI's sqlInterpolate to safely and efficiently bind parameter to query and avoid any need to concatenate or punctuate:

# PREPARED STATEMENT (NO DATA)
sql <- "SELECT * FROM kv WHERE Person = ?pid"

for (i in CancerMet2){ 
  query <- sqlInterpolate(conn, sql, pid = i)
  info <- dbGetQuery(conn, query)

  print(info)
}

To save results in a named list of data frames:

df_list <- setNames(lapply(CancerMet2, function(i) {
                             query <- sqlInterpolate(conn, sql, pid = i))
                             dbGetQuery(conn, query) 
                           }), 
                    paste0("Person_", CancerMet2))

df_list$Person_1
df_list$Person_2
df_list$Person_3
...

Upvotes: 1

Related Questions