CODEWITHSUNDEEP
rsqldf
muraenok
muraenok

Reputation: 95

Use values in df column to create a sql query

I would like to take the values from a data frame and paste them into a text string that can be used as a sql query. In SAS I would do it 

proc sql noprint; Select Names into :names separated by ", " from df; quit;

this would create a variable &name storing all the names. Like: Id, Name, Account. I would like to do this same type of thing in R, but do not know how. I can create a vector with names separated by comma and each one is surrounded by quotes, I can take away the quotes using noquote function and have them in a vector, but I can not get the elements in another paste statement to add the "SELECT" and FROM. I can not get it to all paste. Is there a way to pull the values on Column and create a text string that can be used as a SQL query inside R? Here is what I have tried in R:

name = c("Id", "IsDeleted", "Name", "Credit__Loan__c")
label = c("Record Id", "Deleted", "ID", "Loan")
df = data.frame(name, label)
names(df) <- c("name", "label")

as.query.fields = noquote(paste(df$name, collaspe=", "))
as.query.final <- paste("SELECT " , noquote(paste(df$name, collaspe=", ")), " id FROM Credit_Amortization_Schedule__c")

Upvotes: 2

Views: 1255

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269371

The gsubfn package supports string interpolation:

library(gsubfn)

Names <- toString( sprintf("%s '%s'", df$name, df$label) )
fn$identity("select $Names from myTable")

giving:

[1] "select Id 'Record Id', IsDeleted 'Deleted', Name 'ID', Credit__Loan__c 'Loan' from myTable"

Here some additional examples: SO example 1 and SO example 2 .

Upvotes: 0

Hack-R
Hack-R

Reputation: 23216

data(iris)
colnames(iris)
a <- noquote(paste(colnames(iris), collaspe=", "))
as.query.final <- cat("SELECT " , a, " id FROM Credit_Amortization_Schedule__c")

The result is:

SELECT  Sepal.Length ,  Sepal.Width ,  Petal.Length ,  Petal.Width ,  Species ,   id FROM Credit_Amortization_Schedule__c

which you can then use with SQL like this:

require(RODBC)
result <- sqlQuery(db, as.query.final)

where db is your database connection

Or, since I see your sqldf tag now, if you want to use sqldf it's just:

sqldf(as.query.final)

Upvotes: 2

Related Questions