[R] 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 
> "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.
>       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
>
> ______________________________________________
> R-help at r-project.org 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, saschaview at gmail.com