I'm trying to rewrite some code using the sqldf library in R, which should allow me to run SQL queries on data frames, but I am having an issue in that whenever I try to run a query, R seems like it tries to query the actual real MySQL db con that I use and look for a table by the name of a the data frame that I am trying to search by.
When I run this:
sqldf("SELECT COUNT(*) from work.class_scores")
I get:
Error in mysqlNewConnection(drv, ...) : RS-DBI driver: (Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) )
When I try to specify the location using two different ways (the first form the googlecode page, the second which should be right based on the docs)
> sqldf("SELECT COUNT(*) from work.class_scores", sqldf.driver = "SQLite")
Error in sqldf("SELECT COUNT(*) from work.class_scores", sqldf.driver = "SQLite") :
unused argument(s) (sqldf.driver = "SQLite")
> sqldf("SELECT COUNT(*) from work.class_scores", drv = "SQLite")
Loading required package: tcltk
Loading Tcl/Tk interface ... Error : .onLoad failed in loadNamespace() for 'tcltk', details:
call: dyn.load(file, DLLpath = DLLpath, ...)
error: unable to load shared library '/Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so':
dlopen(/Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so, 10): Library not loaded: /usr/local/lib/libtcl8.5.dylib
Referenced from: /Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so
Reason: image not found
Error: require(tcltk) is not TRUE
So, I'm thinking it might be a a problem with this package tcltk, which I have never heard of, so I try and take care of that and find some issues:
> install.packages("tcltk")
Warning in install.packages :
argument 'lib' is missing: using '/Users/michaeldiscenza/Library/R/2.11/library'
Warning in install.packages :
package ‘tcltk’ is not available
> install.packages("tcltk2", lib="/Applications/RStudio.app/Contents/Resources/R/library")
trying URL 'http://lib.stat.cmu.edu/R/CRAN/bin/macosx/leopard/contrib/2.11/tcltk2_1.1-5.tgz'
Content type 'application/x-gzip' length 940835 bytes (918 Kb)
opened URL
==================================================
downloaded 918 Kb
The downloaded packages are in
/var/folders/Y1/Y1gdz9tKFiSnWsGP9+BDcU+++TI/-Tmp-//RtmpL07KTL/downloaded_packages
> library("tcltk")
Loading Tcl/Tk interface ... Error : .onLoad failed in loadNamespace() for 'tcltk', details:
call: dyn.load(file, DLLpath = DLLpath, ...)
error: unable to load shared library '/Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so':
dlopen(/Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so, 10): Library not loaded: /usr/local/lib/libtcl8.5.dylib
Referenced from: /Library/Frameworks/R.framework/Resources/library/tcltk/libs/x86_64/tcltk.so
Reason: image not found
Error: package/namespace load failed for 'tcltk'
Error in !dbPreExists : invalid argument type
Here, I just really don't know what the issue is, do I need to move something around?
Another approach that I tried was before running the query on the data frame object, setting my database connection so R would look there rather than trying to connect to the actual local MySQL database. But that didn't work. Back to the problem with the socket (even though I can query the local DB itself without any issues.
> con <- sqldf()
Error in mysqlNewConnection(drv, ...) :
RS-DBI driver: (Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
)
Eventually, I want to query to get the count of records where the value for C is larger than 2 for example, and I feel comfortable doing that. The only problem is I don't know if there is another way to specify that what I am querying is a data frame and not an actual db. Am I missing something really silly and easy here?
Thanks!