Using R To Connect To A MySQL Database

Hi there. I have been playing around with R and using it to connect to a MySQL database. My past statistics training as a student involved mostly reading in data files of the .csv format and not so much from a database.

Through some quick research along with trial and error here is what I found.

Pixabay Image

 

Using The RMySQL R Library

---

The most important R package for connecting to a MySQL database in R is RMySQL. If you do not have this package installed yet in your R or RStudio program use the command install.packages('RMySQL') to install the package.

After installation, the load in the RMySQL package with library(RMySQL) in the R / RStudio console. Once that is done successfully, you can use the commands and functions from the package.

 

Pixabay Image

 

Connecting To A MySQL Database In R

---

The main function for connecting to a MySQL database from R's RMySQL package is dbConnect(). You will need the user name, password, a host URL and the name of the database you would like to access.

 

# Connect to MYSQL database (user, pass and details omitted):
# Reference: https://www.r-bloggers.com/mysql-and-r/

con = dbConnect(MySQL(), 
                user = ' ',  
                password = '', 
                dbname =  ' ', 
                host = ' ')

 

You can list the tables in the specified database with the use of dbListTables(con).

 

Extract Data With A SQL Query

---

Once the connection is established, you can use a query to obtain whatever information you would like from the database. You can go simple with something like SELECT * FROM table_name which would select all the columns and all the rows from table_name from the database (from dbname).

Once you have a query ready, use dbSendQuery() with the connection and query as arguments.

 

Example

query <- "SELECT * FROM customer;"

customer_query <- dbSendQuery(con, query)

Pixabay Image

 

Convert The Query Result Into A R Dataframe

---

The customer_query is not a R dataframe yet. It needs to be converted into one with the use of the fetch() command. Using n= -1 will take everything for conversion into a dataframe.

 

customer_data <- fetch(insurance_query, n = -1)

 

Writing To A .csv File & Closing The Connection

---

An optional step (and a good idea) is to save the dataframe into a .csv file for offline use or portability.

write.csv(file = fileName,  x=Fail)

 

Closing the connection is quite simple. Use dbDisconnect(con).

Pixabay Image

 

---

Thank you for reading.