[R] Accessing MySQL Database in R
saschaview at gmail.com
Sat Jan 22 08:58:11 CET 2011
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",
On 11-01-20 08:30, Spencer Graves wrote:
> The following worked for me recently:
> MySQL. <- MySQL()
> MySQLcon <- dbConnect(MySQL., user='thisuser', password='thispassword',
> 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
> "r-sig-db at stat.math.ethz.ch". [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.
> On 1/20/2011 5:55 AM, PtitBleu wrote:
>> I used to use RMySQL but as there is no more package for windows, I
>> to move to RODBC.
>> I installed ODBC driver for MySQL (downloaded on the MySQL website) and
>> the RODBC package.
>> I finally discovered that it was not needed to "register" your database
>> ODBC before using it.
>> These commands below work for me.
>> ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1
>> resultdb<-sqlQuery(ch,"SELECT * from my_table")
>> 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
>> 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
>> 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
>> 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
> R-help at r-project.org mailing list
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
Sascha Vieweg, saschaview at gmail.com
More information about the R-help