Accessing MySQL Database in R

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Accessing MySQL Database in R

schlafly
I have a local installation of MySQL on my computer.

I enter the following to access MySQL from the command line:
/Applications/MAMP/Library/bin/mysql -h localhost -u root -p
I am then prompted for a password, and I use: root
This connects me to MySQL in the command line.

I now want to access MySQL databases in R. I enter the following:
mysql <- dbDriver("MySQL")
conn <- dbConnect(mysql,user='root',host='localhost', password='root')

I get the following error message: Error in mysqlNewConnection(drv, ...) :
RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'root'@'localhost' (using password: YES)

Does anyone know why these aren't equivalent?
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

djmuseR
Hi:

Because R does not have a direct interface to MySQL?

You need to load a communication package - the two most common ones are
RODBC and RMySQL. The former requires that you register your MySQL database
table(s) with ODBC before using the RODBC package on them, whereas the
latter works with specific version combinations of MySQL and R. The RODBC
package has a very informative vignette; for information re the RMySQL
package, see
http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

HTH,
Dennis

On Mon, Jan 17, 2011 at 1:30 PM, schlafly <[hidden email]> wrote:

>
> I have a local installation of MySQL on my computer.
>
> I enter the following to access MySQL from the command line:
> /Applications/MAMP/Library/bin/mysql -h localhost -u root -p
> I am then prompted for a password, and I use: root
> This connects me to MySQL in the command line.
>
> I now want to access MySQL databases in R. I enter the following:
> mysql <- dbDriver("MySQL")
> conn <- dbConnect(mysql,user='root',host='localhost', password='root')
>
> I get the following error message: Error in mysqlNewConnection(drv, ...) :
> RS-DBI driver: (Failed to connect to database: Error: Access denied for
> user
> 'root'@'localhost' (using password: YES)
>
> Does anyone know why these aren't equivalent?
> --
> View this message in context:
> http://r.789695.n4.nabble.com/Accessing-MySQL-Database-in-R-tp3221264p3221264.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

Joel
In reply to this post by schlafly
You could try useing '127.0.0.1' instead of 'localhost' and see if that works.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

Uwe Ligges-3
Folks,

1. can you pelase cite the messages you are repsonding to?
2. Can you please include the original poster of the question who is not
necessarily subscribed to the list?

Thanks,
Uwe Ligges


On 18.01.2011 09:52, Joel wrote:
>
> You could try useing '127.0.0.1' instead of 'localhost' and see if that
> works.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

PtitBleu
In reply to this post by djmuseR
Hello,

I used to use RMySQL but as there is no more package for windows, I decided to move to RODBC.
I installed ODBC driver for MySQL (downloaded on the MySQL website) and then the RODBC package.

I finally discovered that it was not needed to "register" your database with ODBC before using it.
These commands below work for me.

library(RODBC)
ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1 Driver;DATABASE=my_database;UID=root;PWD=my_password;case=tolower")
resultdb<-sqlQuery(ch,"SELECT * from my_table")
odbcClose(ch)

Try to modify them for your case.
I hope it will work for you.
Good luck,
Ptit Bleu.


Re: Accessing MySQL Database in R
Jan 18, 2011; 12:10am — by djmuseR [User is online] djmuseR
Hi:

Because R does not have a direct interface to MySQL?

You need to load a communication package - the two most common ones are
RODBC and RMySQL. The former requires that you register your MySQL database
table(s) with ODBC before using the RODBC package on them, whereas the
latter works with specific version combinations of MySQL and R. The RODBC
package has a very informative vignette; for information re the RMySQL
package, see
http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

HTH,
Dennis

On Mon, Jan 17, 2011 at 1:30 PM, schlafly <[hidden email]> wrote:

>
> I have a local installation of MySQL on my computer.
>
> I enter the following to access MySQL from the command line:
> /Applications/MAMP/Library/bin/mysql -h localhost -u root -p
> I am then prompted for a password, and I use: root
> This connects me to MySQL in the command line.
>
> I now want to access MySQL databases in R. I enter the following:
> mysql <- dbDriver("MySQL")
> conn <- dbConnect(mysql,user='root',host='localhost', password='root')
>
> I get the following error message: Error in mysqlNewConnection(drv, ...) :
> RS-DBI driver: (Failed to connect to database: Error: Access denied for
> user
> 'root'@'localhost' (using password: YES)
>
> Does anyone know why these aren't equivalent?
> --
> View this message in context:
> http://r.789695.n4.nabble.com/Accessing-MySQL-Database-in-R-tp3221264p3221264.html
> Sent from the R help mailing list archive at Nabble.com.
>
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

Spencer Graves-2
       The following worked for me recently:


library(RMySQL)
MySQL. <- MySQL()
MySQLcon <- dbConnect(MySQL., user='thisuser', password='thispassword',
                       dbname='desiredDB')


       I have the following suggestions and questions for you:


             1.  Have you tried supplying "dbname" rather than "host"?


             2.  Please provide "sessionInfo()".  Many packages have a
function named "dbConnect", and I don't know which one you are using.


             3.  I don't know if "MySQL()" is equivalent to
dbDriver("MySQL"), which you used.  It might be;  I don't know.


             4.  The standard "install.packages('RMySQL')" may not work,
because this package needs to be built to configure itself properly to
your local operating system and versions of MySQL and R installed.  
Installation instructions are available at
"http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL".  If you have not
already followed those instructions, please do so.  There is a good
chance that will fix your problem, I think.


             5.  If this is not adequate, I suggest you post this
question to "[hidden email]".  [I suggest you subscribe
first.  This list has low volume and you can unsubscribe later if you
prefer.  And please also provide "sessionInfo()".]


             6.  Or use RODBC as suggested by Ptit Bleu.  It comes
highly recommended (including by Brian Ripley).  However, I had
difficulties getting positive results from both RMySQL and RODBC.  I
tried both, with each receiving similar quantities of expletives.  
Finally, I got RMySQL to do what I wanted and suspended my schoolboy
exercises with RODBC.


       Hope this helps.
       Spencer


On 1/20/2011 5:55 AM, PtitBleu wrote:

> Hello,
>
> I used to use RMySQL but as there is no more package for windows, I decided
> to move to RODBC.
> I installed ODBC driver for MySQL (downloaded on the MySQL website) and then
> the RODBC package.
>
> I finally discovered that it was not needed to "register" your database with
> ODBC before using it.
> These commands below work for me.
>
> library(RODBC)
> ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1
> Driver;DATABASE=my_database;UID=root;PWD=my_password;case=tolower")
> resultdb<-sqlQuery(ch,"SELECT * from my_table")
> odbcClose(ch)
>
> Try to modify them for your case.
> I hope it will work for you.
> Good luck,
> Ptit Bleu.
>
>
> Re: Accessing MySQL Database in R
> Jan 18, 2011; 12:10am — by djmuseR [User is online] djmuseR
> Hi:
>
> Because R does not have a direct interface to MySQL?
>
> You need to load a communication package - the two most common ones are
> RODBC and RMySQL. The former requires that you register your MySQL database
> table(s) with ODBC before using the RODBC package on them, whereas the
> latter works with specific version combinations of MySQL and R. The RODBC
> package has a very informative vignette; for information re the RMySQL
> package, see
> http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL
>
> HTH,
> Dennis
>
> On Mon, Jan 17, 2011 at 1:30 PM, schlafly<[hidden email]>  wrote:
>
>> I have a local installation of MySQL on my computer.
>>
>> I enter the following to access MySQL from the command line:
>> /Applications/MAMP/Library/bin/mysql -h localhost -u root -p
>> I am then prompted for a password, and I use: root
>> This connects me to MySQL in the command line.
>>
>> I now want to access MySQL databases in R. I enter the following:
>> mysql<- dbDriver("MySQL")
>> conn<- dbConnect(mysql,user='root',host='localhost', password='root')
>>
>> I get the following error message: Error in mysqlNewConnection(drv, ...) :
>> RS-DBI driver: (Failed to connect to database: Error: Access denied for
>> user
>> 'root'@'localhost' (using password: YES)
>>
>> Does anyone know why these aren't equivalent?
>> --
>> View this message in context:
>> http://r.789695.n4.nabble.com/Accessing-MySQL-Database-in-R-tp3221264p3221264.html
>> Sent from the R help mailing list archive at Nabble.com

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: Accessing MySQL Database in R

Sascha Vieweg
I think this is not an R issue, but one of MAMP. On my server's
sql service, I can connect using password, however, on my local
MAMP, I need the socket:

dbCon <- dbConnect(dbdr, user="root", password="root",
dbname="mydb",
unix.socket="/Applications/MAMP/tmp/mysql/mysql.sock")

HTH, *S*


On 11-01-20 08:30, Spencer Graves wrote:

>       The following worked for me recently:
>
>
> library(RMySQL)
> MySQL. <- MySQL()
> MySQLcon <- dbConnect(MySQL., user='thisuser', password='thispassword',
>                       dbname='desiredDB')
>
>
>      I have the following suggestions and questions for you:
>
>
>            1.  Have you tried supplying "dbname" rather than "host"?
>
>
>            2.  Please provide "sessionInfo()".  Many packages have a
> function named "dbConnect", and I don't know which one you are using.
>
>
>            3.  I don't know if "MySQL()" is equivalent to dbDriver("MySQL"),
> which you used.  It might be;  I don't know.
>
>
>            4.  The standard "install.packages('RMySQL')" may not work,
> because this package needs to be built to configure itself properly to your
> local operating system and versions of MySQL and R installed.  Installation
> instructions are available at
> "http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL".  If you have not already
> followed those instructions, please do so.  There is a good chance that will
> fix your problem, I think.
>
>
>            5.  If this is not adequate, I suggest you post this question to
> "[hidden email]".  [I suggest you subscribe first.  This list has
> low volume and you can unsubscribe later if you prefer.  And please also
> provide "sessionInfo()".]
>
>
>            6.  Or use RODBC as suggested by Ptit Bleu.  It comes highly
> recommended (including by Brian Ripley).  However, I had difficulties getting
> positive results from both RMySQL and RODBC.  I tried both, with each
> receiving similar quantities of expletives.  Finally, I got RMySQL to do what
> I wanted and suspended my schoolboy exercises with RODBC.
>
>
>       Hope this helps.
>       Spencer
>
>
> On 1/20/2011 5:55 AM, PtitBleu wrote:
>>  Hello,
>>
>>  I used to use RMySQL but as there is no more package for windows, I
>>  decided
>>  to move to RODBC.
>>  I installed ODBC driver for MySQL (downloaded on the MySQL website) and
>>  then
>>  the RODBC package.
>>
>>  I finally discovered that it was not needed to "register" your database
>>  with
>>  ODBC before using it.
>>  These commands below work for me.
>>
>>  library(RODBC)
>>  ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1
>>  Driver;DATABASE=my_database;UID=root;PWD=my_password;case=tolower")
>>  resultdb<-sqlQuery(ch,"SELECT * from my_table")
>>  odbcClose(ch)
>>
>>  Try to modify them for your case.
>>  I hope it will work for you.
>>  Good luck,
>>  Ptit Bleu.
>>
>>
>>  Re: Accessing MySQL Database in R
>>  Jan 18, 2011; 12:10am — by djmuseR [User is online] djmuseR
>>  Hi:
>>
>>  Because R does not have a direct interface to MySQL?
>>
>>  You need to load a communication package - the two most common ones are
>>  RODBC and RMySQL. The former requires that you register your MySQL
>>  database
>>  table(s) with ODBC before using the RODBC package on them, whereas the
>>  latter works with specific version combinations of MySQL and R. The RODBC
>>  package has a very informative vignette; for information re the RMySQL
>>  package, see
>>  http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL
>>
>>  HTH,
>>  Dennis
>>
>>  On Mon, Jan 17, 2011 at 1:30 PM, schlafly<[hidden email]>  wrote:
>>
>> >  I have a local installation of MySQL on my computer.
>> >
>> >  I enter the following to access MySQL from the command line:
>> >  /Applications/MAMP/Library/bin/mysql -h localhost -u root -p
>> >  I am then prompted for a password, and I use: root
>> >  This connects me to MySQL in the command line.
>> >
>> >  I now want to access MySQL databases in R. I enter the following:
>> >  mysql<- dbDriver("MySQL")
>> >  conn<- dbConnect(mysql,user='root',host='localhost', password='root')
>> >
>> >  I get the following error message: Error in mysqlNewConnection(drv, ...)
>> >  :
>> >  RS-DBI driver: (Failed to connect to database: Error: Access denied for
>> >  user
>> >  'root'@'localhost' (using password: YES)
>> >
>> >  Does anyone know why these aren't equivalent?
>> >  --
>> >  View this message in context:
>> >  http://r.789695.n4.nabble.com/Accessing-MySQL-Database-in-R-tp3221264p3221264.html
>> >  Sent from the R help mailing list archive at Nabble.com
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>

--
Sascha Vieweg, [hidden email]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.