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
Step 3: Use the following command to install the MySQL library in RStudio:
install.packages("RMySQL")
Now execute the following commands as RScript:
#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) |
Output:
Contact Us