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.
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.
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)
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).
Thank you for reading.