Database Connectivity with R Programming

The database is a collection of organized information so that it can be accessed with ease. It can be accessed or stored at the computer system. It can be managed through a Database management system (DBMS), which is a software that is used to manage data. Database refers to related data which is in a structured form.

It supports the storage and manipulation of data. Basically a database is having 5 sublanguages:

  • Data Definition Language(DDL)
  • Data Query Language(DQL)
  • Data Manipulation Language(DML)
  • Data Control Language(DCL)
  • Transaction Control Language(TCL)

To connect Database with R Programming we will be going to connect R script with MySQL Database.

To install MYSql refer to its official site dev.mysql.com

To begin with the connection process, follow the steps given below:



Step 1: Create a database in MySQL with the following command:

create database databasename;

As you can see in this image we have used the commands to access the database and moreover performed the DML operations in the database.

Step 2: To connect the database with R we can use R Studio. To download R Studio visit rstudio.com
R-Studio-Download

Step 3: Use the following command to install the MySQL library in RStudio:

install.packages("RMySQL")

Now execute the following commands as RScript:

filter_none

edit
close

play_arrow

link
brightness_4
code

#To check whether the library is installed or not
library(RMySQL)
   
# Create a connection Object to MySQL database.
mysqlconnection = dbConnect(MySQL(), user = 'root'
                                 password = 'root',
    dbname = 'onlinetutorials', host = 'localhost')
typeof(mys)
   
# List the tables available in this database.
dbListTables(mysqlconnection)
   
# Query the "actor" tables to get all the rows.
a = dbSendQuery(mysqlconnection, 
                "create table students(id int, name varchar(10))")
a = dbSendQuery(mysqlconnection, 
                "insert into students values(101, 'amit')")
a = dbSendQuery(mysqlconnection, 
                "insert into students values(102, 'aman')")
result = dbSendQuery(mysqlconnection, 
                     "select * from students")
   
# Store the result in a R data frame object.
# n = 5 is used to fetch first 5 rows.
data.frame = fetch(result)
print(data.frame)

chevron_right


Output:

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.