带动态字符串的R dbGetQuery [英] R dbGetQuery with dynamic string

问题描述

来自这篇文章和

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)

我的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"

但是，当我将sqlcmd传递给dbGetQuery时，它仅返回带有ItemId = 34的数据，而ItemId = 34是Id列表中的第一个元素.

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!

推荐答案

由于我认为R DBI驱动程序尚未实现多个SQL语句支持，因此dbGetQuery仅返回第一条语句.

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

因此，您需要针对多个SQL语句(例如，使用lapply)迭代地运行 sqlcmd ，以返回数据帧列表，然后对单个主数据帧进行rbind调用:

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)

或者，对一个SQL语句使用UNION或UNION ALL.

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:

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

final_df <- dbGetQuery(conn, single_sql)

这篇关于带动态字符串的R dbGetQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！