Using R To Connect To A MySQL Database

in programming •  6 years ago  (edited)

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.

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Hi, thanks for the post! I have included the post in my daily Science and technology digest, and you'll receive a 10% share of that post's rewards.

Thank you @remlaps-lite.

If you need to connect to the database, you can use odbc connection