RMySQL

Intall RMySQL

RMySQL is an easy interface between R and MySQL with the database interface definition implemented in package DBI To get hold of if, just do

install.packages("RMySQL")
library(RMySQL)

Install MySQL

If you wish to install MySQL, go to here to find the appropriate version for your machine. The latest version would require you to keep the password after installation otherwise you will need to reset the password as the root. If you install MySQL using from .dmg then it is the best to add MySQL to your path so you don't have to go to the installation directory every time.

export PAHT=$PATH:/user/local/mysql/bin

add the above line to whichever shell profile you use e.g. ~/.bash_profile, and/or ~/.bashrc.

Mini example

Connect to MySQL databases

We can connect to the databases in two ways, using dbConnect:

provide dbConnect with explicit database arguments such as user, password, and database name
provide dbConnect only with a group name in ~/.my.cnf, which connects to the database using the authentication parameters directory. We should favor the latter approach.
```
library(DBI)
library(RMySQL)
con <- dbConnect(RMySQL::MySQL(), dbName="mysql")
```

.my.cnf file

*.cnf files are start-up options files that most MySQL programs use to specify options so that we don't have to enter them explicitly. Each section consists of the sections names in the square brackets, options names and their values. Each time a connection is establish the client and rs-dbi sections are called. You don't have to supply the same option twice if you don't intend to change the value of that argument. If the same option appears twice the second value will be used.

[client]
user = root
password = password
host = localhost

[rs-dbi]
database = s-data

[test]
databse = mysql

The example above has two groups, the default rs-dbi group and the test group. Similar to how we do the connection above we can just

con2 <- dbConnect(RMySQL::MySQL(), group="test")

List tables and fileds

dbListTables(con)

##  [1] "columns_priv"              "db"                       
##  [3] "engine_cost"               "event"                    
##  [5] "func"                      "general_log"              
##  [7] "gtid_executed"             "help_category"            
##  [9] "help_keyword"              "help_relation"            
## [11] "help_topic"                "innodb_index_stats"       
## [13] "innodb_table_stats"        "ndb_binlog_index"         
## [15] "plugin"                    "proc"                     
## [17] "procs_priv"                "proxies_priv"             
## [19] "server_cost"               "servers"                  
## [21] "slave_master_info"         "slave_relay_log_info"     
## [23] "slave_worker_info"         "slow_log"                 
## [25] "tables_priv"               "time_zone"                
## [27] "time_zone_leap_second"     "time_zone_name"           
## [29] "time_zone_transition"      "time_zone_transition_type"
## [31] "user"

dbListFields(con, "user")

##  [1] "Host"                   "User"                  
##  [3] "Select_priv"            "Insert_priv"           
##  [5] "Update_priv"            "Delete_priv"           
##  [7] "Create_priv"            "Drop_priv"             
##  [9] "Reload_priv"            "Shutdown_priv"         
## [11] "Process_priv"           "File_priv"             
## [13] "Grant_priv"             "References_priv"       
## [15] "Index_priv"             "Alter_priv"            
## [17] "Show_db_priv"           "Super_priv"            
## [19] "Create_tmp_table_priv"  "Lock_tables_priv"      
## [21] "Execute_priv"           "Repl_slave_priv"       
## [23] "Repl_client_priv"       "Create_view_priv"      
## [25] "Show_view_priv"         "Create_routine_priv"   
## [27] "Alter_routine_priv"     "Create_user_priv"      
## [29] "Event_priv"             "Trigger_priv"          
## [31] "Create_tablespace_priv" "ssl_type"              
## [33] "ssl_cipher"             "x509_issuer"           
## [35] "x509_subject"           "max_questions"         
## [37] "max_updates"            "max_connections"       
## [39] "max_user_connections"   "plugin"                
## [41] "authentication_string"  "password_expired"      
## [43] "password_last_changed"  "password_lifetime"     
## [45] "account_locked"

Import and export data frames

We first import the users to a data frame and write to write to the database from a data frame

users <- dbReadTable(con, "user")
class(users)

## [1] "data.frame"

dbWriteTable(con, "newUsers", users)

Run SQL statements and see the outputs

Just like how one would run an SQL statement from the terminal:

myUsers <- dbGetQuery(con, "select * from user")

Get meta-info on the connections

There are various methods available for the database connections. We first can give an overview of the current database and connection and then we can get other information on the query request we that we send:

summary(MySQL(), verbose = TRUE)

## 
##   Max connections:   16 
##   Cur connections:   1 
##   Total connections: 1 
##   Default records per fetch: 500 
##   DBI API version:       0.4.1 
##   MySQL client version:  5.5.1 
## 
## Connections:
## 
##   User:   root 
##   Host:   localhost 
##   Dbname: mysql 
##   Connection type: Localhost via UNIX socket 
## 
## Results:
## NULL

summary(con, verbose = TRUE)

## 
##   User:   root 
##   Host:   localhost 
##   Dbname: mysql 
##   Connection type: Localhost via UNIX socket 
##   MySQL server version:   5.7.12 
##   MySQL client version:   5.5.1 
##   MySQL protocol version: 10 
##   MySQL server thread id: 658 
## 
## Results:

rs <- dbSendQuery(con, "SELECT * FROM user WHERE User like 'root'")
dbHasCompleted(rs)

## [1] FALSE

dbGetStatement(rs)

## [1] "SELECT * FROM user WHERE User like 'root'"

dbGetInfo(rs)

## $statement
## [1] "SELECT * FROM user WHERE User like 'root'"
## 
## $isSelect
## [1] 1
## 
## $rowsAffected
## [1] -1
## 
## $rowCount
## [1] 0
## 
## $completed
## [1] 0
## 
## $fieldDescription
## $fieldDescription[[1]]
## NULL

dbClearResult(rs)

## [1] TRUE

Close the connection

Always remember to cleanup by disconnecting the database

dbDisconnect(con)

## [1] TRUE