BUG? sqldf dataframe can not have periods in their name

94 views
Skip to first unread message

malcol...@gmail.com

unread,
Jun 22, 2019, 12:35:11 PM6/22/19
to sqldf
I've tried every quote/escape syntax I can think of but find that periods in the names of R dataframes cause sqldf to throw an unexpect (to me) error.

Viz:

``` R

> sqldf('select * from cars limit 2')
speed dist
1 4 2
2 4 10
# what we expect

> cars_df<-cars
> sqldf('select * from cars_df limit 2')
speed dist
1 4 2
2 4 10
# so far so go

> cars.df<-cars
> sqldf('select * from cars.df limit 2')
Error in result_create(conn@ptr, statement) : no such table: cars.df
# not sure why it fails

# let's try some escaping:
> sqldf('select * from "cars.df" limit 2')
Error in paste("sqldf:", "table", nam, "already in", dbname, "\n") :
argument "dbname" is missing, with no default
# erhm, no good, how about:
> sqldf('select * from [cars.df] limit 2')
Error in paste("sqldf:", "table", nam, "already in", dbname, "\n") :
argument "dbname" is missing, with no default
# foo!

# how about:
> sqldf('select * from `cars.df` limit 2')
Error in paste("sqldf:", "table", nam, "already in", dbname, "\n") :
argument "dbname" is missing, with no default

```

Is there an approach that will work, or do I have to change my (arguably non-standard) variable naming convention?

Thanks!

P.S. also submitted as github issue [BUG? sqldf dataframe can not have periods in their name](https://github.com/ggrothendieck/sqldf/issues/33)

Gabor Grothendieck

unread,
Jun 22, 2019, 12:39:48 PM6/22/19
to sqldf
Dot is an SQL operator so you must escape names with dots.  Use [cars.df] or "cars.df" .

Gabor Grothendieck

unread,
Jun 22, 2019, 9:37:37 PM6/22/19
to sqldf
Here is an example:

  > library(sqldf)
  Loading required package: gsubfn
  Loading required package: proto
  Loading required package: RSQLite
  > packageVersion("sqldf")
  [1] ‘0.4.11’
  > cars.df <- mtcars
  > sqldf("select * from [cars.df] limit 2")
    mpg cyl disp  hp drat    wt  qsec vs am gear carb
  1  21   6  160 110  3.9 2.620 16.46  0  1    4    4
  2  21   6  160 110  3.9 2.875 17.02  0  1    4    4

If you are not getting that something is wrong.  Try starting from a fresh session.
Also be sure  you are using the SQLite backend.  If you have other database drivers
loaded it will assume you want to use those instead of SQLite so you will need to tell
it you want to use SQLite (see ?sqldf for more info) or else follow the syntax of the
database you are using.  Adding verbose = TRUE to the sqldf call may help. 
Reply all
Reply to author
Forward
0 new messages