Call R Dataframe in SQL

Question

is there any way to call an R dataframe in SQL? For example, I would like to run something like:

SELECT user_id, other_variables FROM table1 WHERE user_id IN ('R DATAFRAME')

where the R dataframe is a simple list of some user id's that can be found in table1.

I was just wondering if something like this is possible, either written in R or SQL and if so, how do this? I know that I could just upload the R dataframe to the database but I do not have permissions to create my own tables in the database. Any help would be greatly appreciated, thank you.

Answer 1

Except with spark environment or with the R package sqldf you cannot.

If it's only a list of ids you can use unlist()

sqlQuery (ch,paste0(" 
  select * from my_table 
  where id in (",
  paste(unlist(my_df_Fktable$id), 
  collapse=','),
  ")"
))

Answer 2

Here is an answer to nearly use dataframe, line by line.

library(dplyr)
library(RSQLite)
library(DBI)
library(glue)
# Only for data example
con <- dbConnect(RSQLite::SQLite(), ":memory:")
T_all<- data.frame(a=1:3,b=c("a","b","c"))
T_where <- data.frame(a=1:3,b=c("a","b","d"))
DBI::dbWriteTable(con, "T_all", T_all)
DBI::dbWriteTable(con, "T_where", T_where)

# The function of the answer
glue_sql.multi.fct <-function (sql_multi_vars, args,connexion_bdd=DBI::ANSI()) {
  args<-unlist(list(sql_multi_vars,args,.con =connexion_bdd), recursive = FALSE ) 
  unname(names(args)[1])# for 1st arg of glue_sql()
  do.call(glue_sql,args )
}

# All datas where I find 
dfall<-DBI::dbGetQuery(con,"select * from T_All")
print("dfall: All datas where I find ")
print(dfall)

dfw<-DBI::dbGetQuery(con,"select * from T_Where")

sql1="select * from T_all where a = {a} and b={b}"


sql_binded<-glue_sql.multi.fct(sql1,list(a=1,b="a"))
print("sql_binded")
print(sql_binded)
dfall_filtered1<-DBI::dbGetQuery(con,sql_binded)
print("dfall_filtered1: datas dfall filtered by a list (normal use of glue_sql()")
print(dfall_filtered1)

# loop for the datas dfall filtered by dfw
dfall_filtered2<-  data.frame()   
# the loop is mandatory for SQL Server, it doesn't work like example of multi line of DBI::dbGetQuery documentation.
for (sqlcurrent in glue_sql.multi.fct(sql1,dfw))  {
  dfall_filtered2<-rbind(dfall_filtered2, DBI::dbGetQuery(con,sqlcurrent))
  print(sqlcurrent)
}

# results of  the datas dfall filtered by dfw
print("dfall_filtered2: results of  the datas dfall filtered by dfw")
print(dfall_filtered2)

dbDisconnect(con)

Ouput

[1] "dfall: All datas where I find "
  a b
1 1 a
2 2 b
3 3 c
[1] "sql_binded"
<SQL> select * from T_all where a = 1 and b='a'
[1] "dfall_filtered1: datas dfall filtered by a list (normal use of glue_sql()"
  a b
1 1 a
[1] "select * from T_all where a = 1 and b='a'"
[1] "select * from T_all where a = 2 and b='b'"
[1] "select * from T_all where a = 3 and b='d'"
[1] "dfall_filtered2: results of  the datas dfall filtered by dfw"
  a b
1 1 a
2 2 b