3

From This post and This post, I got a way to write an rsqlite dynamic command. However, it doesn't work for me. My data looks like:

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")
Df <- dbGetQuery(conn, sqlcmd)

My sqlcmd gives me a list of strings as 

"select col1, col2 from DB where STOREID =34"
"select col1, col2 from DB where STOREID =22"
"select col1, col2 from DB where STOREID =86"

However, when I pass sqlcmd to dbGetQuery, it only returns data with ItemId = 34, which is the first element in the Id list.

I'm wondering if anyone has any ideas on why does this happen? Any help would be appreciated!

Mr369
  • 384
  • 4
  • 17
  • `dbGetQuery` only returns first statement (multiple resultset is not implemented yet). So, you need to iteratively run it for multiple SQL statements. Otherwise use a `UNION` for one SQL statement. – Parfait Jun 08 '18 at 20:43
  • Regardless of your preferred answer: (1) *please* do not use `paste("select...",Id2)`, you are open to [sql injection](https://xkcd.com/327/), better to use `sqlInterpolate`, `dbQuoteString`, or `dbBind`; (2) it is usually (much) faster/cheaper to do `select id,col1,col2 ... where id in (11,12,13)` and use `split()` or similar in R than to do three round-trip queries, esp with larger data; (3) if you are thinking of using `union`, then you might as well stick with `...where id in (11,12,13)` in a single query without `union` ... if order matters, then be explicit about the preferred order. – r2evans Jun 09 '18 at 05:54
  • http://db.rstudio.com/best-practices/run-queries-safely/ – r2evans Jun 09 '18 at 05:55

4 Answers4

3

Since I believe R DBI drivers have not yet implemented multiple SQL statements support, dbGetQuery only returns first statement.

Hence, you need to iteratively run your sqlcmd for multiple SQL statements such as with lapply to return a list of dataframes, followed by an rbind call for single master dataframe: 

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

# LIST OF DATAFRAMES
df_list <- lapply(sqlcmd , function(x) dbGetQuery(conn, x)) 

# FINAL DATAFRAME
final_df <- do.call(rbind, df_list)

Alternatively, use a UNION or UNION ALL for one SQL statement. 

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

single_sql <- paste(sqlcmd, collapse = " UNION ")
final_df <- dbGetQuery(conn, single_sql)

Or still use OR:

single_sql <- paste("select col1, col2 from DB where ItemId =", 
                    paste(Id, collapse=" OR ItemId = "))

final_df <- dbGetQuery(conn, single_sql)
Parfait
  • 104,375
  • 17
  • 94
  • 125
2

This also works with parametrized queries:

library(RSQLite)
conn <- dbConnect(SQLite())
dbWriteTable(conn, "DB", data.frame(col1 = 1, col2 = 2, ItemId = 3))

Id <- c(34, 22, 86)
sqlcmd <- "select col1, col2 from DB where ItemId = ?"
Df <- dbGetQuery(conn, sqlcmd, params = list(Id))

Created on 2018-06-11 by the reprex package (v0.2.0).

Recent versions of DBI and RSQLite will return a data frame that contains the results from these queries concatenated.

krlmlr
  • 25,056
  • 14
  • 120
  • 217
  • Hey Krlmlr, really appreciate for your help! When I'm trying this approach, R returns an error massage as ERROR: pg_atoi: error in "c(34, 22, 86)": can't parse "c(34, 22, 86)". Do you have any idea about why does this happen? – Mr369 Jun 11 '18 at 13:44
  • @Mr369: I've expanded the example to make it self-contained, works for me. Can you run the code as is? – krlmlr Jun 11 '18 at 18:56
1

I would normally do this like this: 

Id1 <- c(34, 22, 86)

Id2 <- paste(Id1, collapse = ", ")
sqlcmd <- paste("select col1, col2 from DB where ItemId in (", Id2, ")", sep="")
Df <- dbGetQuery(conn, sqlcmd)

However if you want to return a list of data frames for each id and run the query three times you could do:

sqlcmd <- paste("select col1, col2 from DB where ItemId in (", Id1, ")", sep="")    
dataList <- lapply(sqlcmd, function(x) dbGetQuery(conn, x))
Ian Wesley
  • 3,565
  • 15
  • 34
0

Just noting that using variables in a SQL query is generally discouraged, as it can make you susceptible to an injection attack. https://db.rstudio.com/best-practices/run-queries-safely/

Kyle Protho
  • 21
  • 1