0

I tried to select specific data from the data frame in a batch manner of R language(3.6) but failed.

When I use 

sqldf("select * from Interact where miRNA = 'hsa-miR-510-5p' and
       Seqname ='chr3:195780289-195787118-' ")

it turned out ok.

While I tried

sqldf("select * from Interact where miRNA = Interpairs[1,1] and 
       Seqname =Interpairs[1,2]"),

or 

sqldf('select * from Interact where miRNA = Interpairs$microRNA[1] and 
      Seqname = Interpairs$circRNA[1]')

it turned out wrong.

Error in result_create(conn@ptr, statement) : near "[1]": syntax error

I wonder whether anyone can help figure this out?

Improve this question
4
  • Its because your Interpairs is your R variable. Use paste or sprintf to pass those variables in SQL statment. See here: stackoverflow.com/questions/17435086/… Commented May 23, 2019 at 2:16
  • 1
    sqldf magically bridges the gap in the presence/name of frames (as pseudo-tables), but does not bridge the gap for other variables. You might also consider glue::glue_sql. Commented May 23, 2019 at 2:27
  • I don't recommend using glue or other additional packages for that. sqldf already contains facilities for this imported from the gsubfn package and exported back out so that you don't need to explicitly load it.. See my answer for the preferred approach. Commented May 23, 2019 at 4:07
  • Thanks for your help. I puzzeled it out with the method recommended by MKa and Grothendieck. Here is the code: fn$sqldf("select * from Interact where miRNA= '`Interplym$microRNA[1]`' and Seqname = '`Interplym$circRNA[1]`' ") Commented May 23, 2019 at 6:27

1 Answer 1

Reset to default
1

The string passed to sqldf must be SQL. What you can do is insert the relevant content by prefacing sqldf with fn$ as shown and then code within backquotes will be executed by R and its output substituted into the sql string at that point prior to passing the string to sqldf.

library(sqldf)
DF <- data.frame(a = 'x', b = 'y', stringsAsFactors = FALSE) # test data

fn$sqldf("select * from DF where a = '`DF$a[1]`' and b = '`DF$b[1]`' ")
##   a b
## 1 x y

fn$sqldf("select * from DF where a = '`DF[1,1]`' and b = '`DF[1,2]`' ")
##   a b
## 1 x y

Also $variable will substitute the variable's contents provided the variable name does not contain certain special characters:

a <- DF$a[1]
b <- DF$b[1]
fn$sqldf("select * from DF where a = '$a' and b = '$b' ")
##   a b
## 1 x y

If you want to see the string actually passed to sqldf then add the verbose = TRUE argument to any of the above sqldf calls.

More info

There are several examples of this on the sqldf github home page: https://github.com/ggrothendieck/sqldf

Also info on fn$ can be found via ?fn . Note that this is a general facility that works with just about any function and not just sqldf. For example,

w <- "world"
fn$cat("Hello, $w\n")
## Hello, world
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, Grothendieck. But I am still confused about the different result:
R fn$sqldf("select * from Interact where miRNA= '`Interpairs$microRNA[1]`' and Seqname = '`Interpairs$circRNA[1]`' ") \n > [1] miRNA Free Energy Seqname <0 row> (or 0-length of row.names) \n R fn$sqldf("select * from Interact where miRNA= 'hsa-miR-510-5p' and Seqname = 'chr3:195780289-195787118-' ") miRNA Free Energy Seqname 1 hsa-miR-510-5p -21.73 chr3:195780289-195787118- 2 hsa-miR-510-5p -15.08 chr3:195780289-195787118-
Is it possible that the ranked order will ascribe the variation?
Regarding the results reported in your comment, it is up to you to provide reproducible input when you report errors or unexpected results if you want help so that we can reproduce it.. Did you check that the string is what you thought it was as per the instructions in the answer? Are you sure that your values are character and not factor? other?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.