When using RPostgreSQL
I find that I cannot use sqldf
in the same way. For example if I load the library and read in data into a data frame using the following code:
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost", user="postgres", password="xxx", dbname="yyy", port="5436")
rs <- dbSendQuery(con, "select * from table");
df<- fetch(rs, n = -1); dbClearResult(rs)
dbDisconnect(con)
I know have the contents of this table in the dataframe df
. However if I want to run a SQL command using sqldf
I would previously do something like this:
sqldf("SELECT * FROM df WHERE X > 10")
This no longer works as I get the error message:
Error in postgresqlNewConnection(drv, ...) :
RS-DBI driver: (could not connect postgres@localhost on dbname "test"
)
Error in !dbPreExists : invalid argument type
I assume this is operator error on my part, but I can't figure how what arguments to supply to sqldf
so that it just focuses on the data frame and does not try to connect to anything else.
Using sqldf with RPostgreSQL
sqldf
will automatically work with the test
database in PostgreSQL if it sees that RPostgreSQL is loaded. So you can create a test
database in PostgreSQL and then use sqldf with that
or, you can specify the name of a different database.
See: sqldf FAQ 12
Using sqldf with RSQLite
If you want to use sqldf with RSQLite rather than with RPostgreSQL you can use sqldf
's drv
argument to force it use a non-default driver. e.g.
sqldf("select foo from bar...",drv="SQLite")
or, you can set the driver globally using the "sqldf.driver"
option. From within R:
options(sqldf.driver = "SQLite")
or, another possibility if you wish to use RSQLite is to detach
RPostgreSQL before you use sqldf and load it again afterwards.
See ?sqldf
for details.