Use values in df column to create a sql query

Question

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")

I think the whisker or brew packages will be helpful here. — ctbrown
– ctbrown, Commented Oct 29, 2014 at 20:53
Don't know if you have realized, but you have collapse spelled incorrectly (you have collaspe), and this affects the final result. You don't need the noquote function (it only affects the way objects are printed) and you can pass the as.query.final object directly in the call of sqlQuery (or similar functions) to execute the query. — nicola
– nicola, Commented Oct 29, 2014 at 21:51
Yes thanks for catching the spelling. That was just a typo and not the issue. Also, I am using R to query Salesforce (using rforcecom and soql) so I really need to control the creation of the Select statement, so the sqlQuery from as.query.final does not work. The only thing I can do is use the cat method to screen and copy and paste the result and then remove the trailing comma before the from statement. Alas in my laziness I was hoping to be able to fully automate it but I can not. thanks — muraenok
– muraenok, Commented Oct 29, 2014 at 23:16

Hack-R · Accepted Answer · 2014-10-29 20:58:35Z

2

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)

answered Oct 29, 2014 at 20:58

Hack-R

23.5k15 gold badges82 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

muraenok Over a year ago

I see that your method prints it to the screen and works, but I do not get it to store the value of the cat into vector call as.query.final. When I print that vector it is NULL. Did I miss something

Hack-R Over a year ago

Oh, okay hmmm I tested it with sqlQuery but maybe you're using it differently or something? If you can paste the error/problem you're describing then I will help you fix it

Community · Accepted Answer · 2017-05-23 10:33:19Z

0

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 .

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Oct 29, 2014 at 23:42

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

3 Comments

muraenok Over a year ago

This works great. Thanks this is just what I was looking to do. It even drops the last comma automatically. Many thanks... I am using the query in a non standard sub query language so I needed to control precisely what is the output.

muraenok Over a year ago

I am trying to find the documentation for fn$identity. so I can figure out how to use it better. I did not see it on the CRAN pdf? Did I miss something? The possibilities this opens for my code, I would like to know more. I am looking through the package and wow! thanks...

G. Grothendieck Over a year ago

Try: library(gsubfn); ?fn Also vignette(gsubfn). The home page gsubfn.googlecode.com has some info too.

Collectives™ on Stack Overflow

Use values in df column to create a sql query

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related