CODEWITHSUNDEEP
mysqlrrmysql
user9549524
user9549524

Reputation: 57

Connecting to MySQL from R

I am trying to connect to MySQL from R. I have installed "8.0.11 MySQL Community Server - GPL" on my machine. In R studio, I have installed RMySQL Library.

When I give the command:

con = dbConnect(RMySQL::MySQL(),user="root", password = "password", dbname="test")

I keep getting the error:

Error in .local(drv, ...) : Failed to connect to database: Error: Unknown database 'test'

I am not sure why it keep giving this error. Any suggestions?

Upvotes: 4

Views: 19115

Answers (3)

Harley
Harley

Reputation: 1534

RMariaDB seems to be the way to go these days. Tested with MySQL.

sudo apt-get update
sudo apt-get install libmariadbclient-dev

R code:

# install.packages("RMariaDB")
library(DBI)

# Connect to the MySQL database
con <- dbConnect(RMariaDB::MariaDB(), 
             dbname = "test", 
             host = "localhost", 
             port = 3306,
             user = "root",
             password = "password")

# Get table names
tables <- dbListTables(con)

# Display structure of tables
str(tables)

# Always cleanup by disconnecting the database
dbDisconnect(con)

You might run into authentication issues:

Error: Failed to connect: Plugin caching_sha2_password could not be loaded:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'TopSecret##1234';

Upvotes: 2

A. Suliman
A. Suliman

Reputation: 13135

The obvious reason may be "I hope" beacuse you didn't include the host ip. Also I prefer use pool package. Then your connection call may be 

library(DBI)
library(RMySQL)
library(pool)

pool <- dbPool(
                  drv = RMySQL::MySQL(),
                  dbname = "db_name",
                  host = "127.0.0.1",
                  username = 'user_name',
                  password = 'password',
                  port = 3306
             )

 onStop(function() {
           poolClose(pool)
         })

Another thing it's better to define user with appropriate privileges on test DB and use this user in the connection call insteade of root as DB connection security best practice.

Upvotes: 1

Andrii
Andrii

Reputation: 3043

Here is a code I use for access to MySQL from R

# 1. Library
library(RMySQL)

# 2. Settings
db_user <- 'your_name'
db_password <- 'your_password'
db_name <- 'database_name'
db_table <- 'your_data_table'
db_host <- '127.0.0.1' # for local access
db_port <- 3306

# 3. Read data from db
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                 dbname = db_name, host = db_host, port = db_port)
s <- paste0("select * from ", db_table)
rs <- dbSendQuery(mydb, s)
df <-  fetch(rs, n = -1)
on.exit(dbDisconnect(mydb))

Please, check how it works on your side.

PS. Looks like you miss 'db_table' parameter.

Upvotes: 10

Related Questions