How to connect to mysql using R

This recipe helps you connect to mysql using R

Recipe Objective

How to connect to mysql using R.

As we know, data files such as data.frames or csv () can easily be imported and read in R using the read.csv () function. But R cannot handle such data files which have a large amount of data present in it. Hence, a connection with the relational database is necessary. Relational databases are types of databases that can store data in the form of tables and different operations like storing, creating, retrieving the data, etc can be performed. SQL -Structured Query Languages is a language that performs all these operations in the relational databases. There are many relational database management systems (RDBMS) like MySQL, Oracle, SQLite, etc .available that use SQL as their standard database language. R can connect to all these relational databases to fetch records from them. The RMySQL packages, helps make a connection between the R environment and the MySQL server. After the connection is made, the data set can be manipulated and analyzed using various functions. This recipe demonstrates an example of how to make connections to MySQL using R.

Step 1 - Install necessary packages

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

Step 2 - Connect MySQL to R

Create a connection Object to MySQL database. Then connect to any sample database, here we are connecting to a database named "university". dbconnect(MySQL(), dbname, host, port, user, password) where, dbname - name of the database which we want to use, in this case : 'university' host - the host name used while creating the connection to server in MySQL. user - the username name defined while making the connection to server. password - the password set while making the connection to the server.

mysqlconnection = dbConnect(RMySQL::MySQL(), dbname='university', host='localhost', port=3306, user='newuser', password='Demo@123456')

Step 3 - List the tables

dbListTables(mysqlconnection) # displays the tables available in this database.

"Tables in the databsse are : "
'course' 'department' 'department_total_salary' 'faculty' 'fall_2017' 'fall_2017_watson' 'instructor' 'physics_instructor' 'section' 'student' 'teaches'

Step 4 - Write some SQL queries

result = dbSendQuery(mysqlconnection, "select * from course") # write query to acces the records from a particular table. data.frame = fetch(result, n = 5) print(data.frame) # Store the result in a R data frame object. n = 5 is used to fetch first 5 rows.

"Output of code is  : "
  course_id                      title  dept_name credits
1   BIO-101          Intro. to Biology    Biology       4
2   BIO-399      Computational Biology    Biology       3
3    CS-101 Intro. to Computer Science comp. sci.       4
4    CS-347   Database System Concepts comp. sci.       4
5   FIN-201         Investment Banking    Finance       3

{"mode":"full","isActive":false}

What Users are saying..

profile image

Ray han

Tech Leader | Stanford / Yale University
linkedin profile url

I think that they are fantastic. I attended Yale and Stanford and have worked at Honeywell,Oracle, and Arthur Andersen(Accenture) in the US. I have taken Big Data and Hadoop,NoSQL, Spark, Hadoop... Read More

Relevant Projects

Customer Churn Prediction Analysis using Ensemble Techniques
In this machine learning churn project, we implement a churn prediction model in python using ensemble techniques.

Create Your First Chatbot with RASA NLU Model and Python
Learn the basic aspects of chatbot development and open source conversational AI RASA to create a simple AI powered chatbot on your own.

Build a Hybrid Recommender System in Python using LightFM
In this Recommender System project, you will build a hybrid recommender system in Python using LightFM .

Classification Projects on Machine Learning for Beginners - 1
Classification ML Project for Beginners - A Hands-On Approach to Implementing Different Types of Classification Algorithms in Machine Learning for Predictive Modelling

Build a Credit Default Risk Prediction Model with LightGBM
In this Machine Learning Project, you will build a classification model for default prediction with LightGBM.

End-to-End Snowflake Healthcare Analytics Project on AWS-1
In this Snowflake Healthcare Analytics Project, you will leverage Snowflake on AWS to predict patient length of stay (LOS) in hospitals. The prediction of LOS can help in efficient resource allocation, lower the risk of staff/visitor infections, and improve overall hospital functioning.

FEAST Feature Store Example for Scaling Machine Learning
FEAST Feature Store Example- Learn to use FEAST Feature Store to manage, store, and discover features for customer churn prediction machine learning project.

Learn How to Build a Logistic Regression Model in PyTorch
In this Machine Learning Project, you will learn how to build a simple logistic regression model in PyTorch for customer churn prediction.

Machine Learning project for Retail Price Optimization
In this machine learning pricing project, we implement a retail price optimization algorithm using regression trees. This is one of the first steps to building a dynamic pricing model.

Build OCR from Scratch Python using YOLO and Tesseract
In this deep learning project, you will learn how to build your custom OCR (optical character recognition) from scratch by using Google Tesseract and YOLO to read the text from any images.