CODEWITHSUNDEEP
rsyntaxsqldf
Lym Lin
Lym Lin

Reputation: 3

why the syntax of sqldf is wrong

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?

Upvotes: 0

Views: 335

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269421

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

Upvotes: 1

Related Questions