0

I am trying to run sqldf in R in Rstudio. However, it produced an error asking for a password even when a password was given (shown in box 1).

I made sure that my password was OK by typing mysql -u scottyiu -p into the terminal and then my password. It is successful and I can get into mysql. I am stuck and will really appreciate some help.

Box 1:

> sqldf("select pwgtp1 from acs where AGEP < 50", password="My password", host = "localhost")
Error in mysqlNewConnection(drv, ...) : 
  RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'scottyiu'@'localhost' (using password: NO))
Error in !dbPreExists : invalid argument type

Box 2:

mysql -u scottyiu -p
mysql> CREATE USER 'scottyiu'@'localhost' IDENTIFIED BY 'my password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'scottyiu'@'localhost';
mysql> FLUSH PRIVILEGES;
Scott Yiu
  • 31
  • 1
  • 3

1 Answers1

-1

I figured out what was going on, below is my original code:

library(RMySQL)
library(sqldf)
fileURL <- "https://d396qusza40orc.cloudfront.net/getdata%2Fdata%2Fss06pid.csv"
download.file(fileURL,destfile="assignment2_q2.csv",method="curl")
acs <- read.csv("assignment2_q2.csv")
sqldf("select * from acs")

It turns out RMySQL and sqldf cannot be both called. If I comment out the RMySQL library, everything works!

Scott Yiu
  • 31
  • 1
  • 3
  • RMySQL and sqldf do work together but you have to set up MySQL as explained in `?sqldf` Another thing you can do is to force sqldf to use sqlite using the drv= argument or options as explained in ?sqldf. – G. Grothendieck Aug 16 '16 at 13:39