CODEWITHSUNDEEP
mysqlsqlr
Grace
Grace

Reputation: 177

How do I get SQL database into R from local host?

I just created my first SQL database using MAMP. (It is simple- just a list of pets.) I would like to load it into R. Here is what I wrote:

install.packages("dbConnect")
library(dbConnect)
mypets=dbConnect(MySQL(),user="root",
             host="localhost1234/DatabaseGrace")

This error is returned:

Error in mysqlNewConnection(drv, ...) : 
  RS-DBI driver: (Failed to connect to database: Error: Unknown MySQL server host 'localhost1234/DatabaseGrace' (2))

Any idea what this means or how I can solve it?

Upvotes: 5

Views: 9282

Answers (4)

falev
falev

Reputation: 1

this works for me with XAMPP on MAC:

library(RMySQL)
con=dbConnect(MySQL(),
    host='localhost',       
    unix.sock='/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock',
    user='root',        
    password='...'
    )

df=dbGetQuery(con,'SHOW DATABASES')
print(df)

Upvotes: 0

Grace
Grace

Reputation: 177

Thank you @duffymo and @Lorenz.

Summary:

  • The database is stored on my computer, so host did need to be localhost as you suggested.

Here is what ended up working.

install.packages("RMySQL")

install.packages("dbConnect")
library(dbConnect)
dbGrace=dbConnect(MySQL(),user="root",
                 host="localhost",
                 dbname="DatabaseGrace",
                 password="root",
                 unix.sock="/Applications/MAMP/tmp/mysql/mysql.sock")

Thanks, all!

Upvotes: 8

duffymo
duffymo

Reputation: 309008

I think the host name should be "localhost", without the 1234.

If you intended that to mean the port, I'd check to make sure that MySQL is indeed listening on that port. The default value is 3306; the admin would have to make a conscious decision to change that.

The usual notation is "localhost:3306" in Java. If you must include the port number, try separating it from the host name with a colon.

Can you connect to MySQL using the admin shell?

Have you GRANTed permission to localhost to connect to database named DatabaseGrace? If not, MySQL won't allow you to connect.

http://dev.mysql.com/doc/refman/5.1/en/grant.html

localhost suggests to me that the database is running on your local machine. Is that true? If not, localhost isn't the right host name. It might also mean that you have a firewall between your machine and the database which prevents you from connecting.

I get a lot of mileage out of cutting & pasting any errors I get into Google to see if anyone else has ever experienced my problem. Here's the first hit.

Upvotes: 4

Lorenz Meyer
Lorenz Meyer

Reputation: 19945

The host is the computer name. So put simply localhost. Select the correct database in a second step.

Upvotes: 1

Related Questions