Access MySQL Data using R

With the increasing prevalence of data in our daily lives, new and better tools are needed to analyze the deluge.  In addition to transforming and analyzing data, R can produce amazing graphics and reports with ease.

R is a programming language and free software environment for statistical computing and graphics. It is one of the best tool to perform analysis over big data. R is used by statisticians and data miners with advanced machine learning training and also by people who are not necessarily trained in advanced data analysis but are tired of using Excel.

In order to analyze data some Analyst and data scientist uses MYSQL as a database. One way to do this, is to extract the data from the database and import it into statistical software. The DBI package in R provides a uniform, client side interface to different database management systems, such as MySQL,

PostgreSQL, and Oracle. For example, the RMySQL package extends the DBI package to provide a MySQL driver and the detailed inner workings for the generic functions to connect, disconnect, and submit and track queries

The R code the user writes to establish a MySQL driver, connect to a MySQL database, and request results is the same code for all

SQL-standard database managers.

We provide a simple example here of how to extract data from a MySQL

Database in an R session.

Step 1:   To connect to a MySQL database simply install the package and load the   library.

# Install RMySQL Package install.packages("RMySQL") # Load library library(RMySQL)

When you include the library of RMySQL. It may generate this error like unloadNamespace(package): namespace ‘DBI’ is imported by ‘twitteR’ so cannot be loaded.  

To solve this error we need to install another package i.e. DBI

Step 2:  Load a driver for a MySQL-type database:

# Load Driver drv = dbDriver("MySQL")

Step 3: Create a connection to the database management server

# create a database connection object. mydb = dbConnect(drv, user='root', password='12345', dbname='mysql', host='localhost')

Listing Tables and Fields:

Once the connection is established, queries can be sent to the database. So let discuss all function one by one

·        dbListTables(mydb)

It returns the names of the tables from the database.

# Display list of all tables from database dbListTables(mydb) [1] "columns_priv" "db" "engine_cost" [4] "event" "func" "general_log" [7] "gtid_executed" "help_category" "help_keyword" [10] "help_relation" "help_topic" "innodb_index_stats" [13] "innodb_table_stats" "ndb_binlog_index" "plugin" [16] "proc" "procs_priv" "proxies_priv" [19] "server_cost" "servers" "slave_master_info" [22] "slave_relay_log_info" "slave_worker_info" "slow_log" [25] "tables_priv" "time_zone" "time_zone_leap_second" [28] "time_zone_name" "time_zone_transition" "time_zone_transition_type" [31] "user"

·        dbListFields(mydb, 'table_name')

It return a list of the fields of a table from database.

# Display fields of a table dbListFields(mydb, 'user') [1] "Host" "User" "Select_priv" [4] "Insert_priv" "Update_priv" "Delete_priv" [7] "Create_priv" "Drop_priv" "Reload_priv" [10] "Shutdown_priv" "Process_priv" "File_priv" [13] "Grant_priv" "References_priv" "Index_priv" [16] "Alter_priv" "Show_db_priv" "Super_priv" [19] "Create_tmp_table_priv" "Lock_tables_priv" "Execute_priv" [22] "Repl_slave_priv" "Repl_client_priv" "Create_view_priv" [25] "Show_view_priv" "Create_routine_priv" "Alter_routine_priv" [28] "Create_user_priv" "Event_priv" "Trigger_priv" [31] "Create_tablespace_priv" "ssl_type" "ssl_cipher" [34] "x509_issuer" "x509_subject" "max_questions" [37] "max_updates" "max_connections" "max_user_connections" [40] "plugin" "authentication_string" "password_expired" [43] "password_last_changed" "password_lifetime" "account_locked"

·        (mydb, 'drop table if exists some_table, some_other_table')

This function is basically used to create and insert data into table from R to mysql database.

# Create table from R into mysql database dbSendQuery(mydb, "CREATE TABLE authors (author_id INT AUTO_INCREMENT PRIMARY KEY,             author_last VARCHAR(50),             author_first VARCHAR(50),             country VARCHAR(50));") # Add Data into Author Table dbSendQuery(mydb, "INSERT INTO authors (author_last, author_first, country)             VALUES('Kumar','Manoj','India');")

·        dbReadTable(mydb, "table_name", row.names = "user_id")

This function is used to read the table from mysql database into R

# Extract data from mysql to R userstable <- dbReadTable(mydb, "authors") userstable

Now we can also check in mysql command line either table that we have created using R into database is created or not.

MYSQL Commands

# View Database show databases; # use Database for example: we have a database named as mysql use mysql; # View tables inside of database show tables; # View data of a table select * from authors;

This way we can easily load data from MySQL database to R and vice versa.