Database Connectivity with R Programming

Import Data from a File in detail

A database is a structured collection of organized data that allows easy access, storage, and management. It can be handled using a Database Management System (DBMS), which is specialized software for managing databases efficiently. A database contains related and structured data that can be stored and retrieved when needed.

A database primarily supports data storage, retrieval, and manipulation through various sublanguages:

1. Data Definition Language (DDL)
2. Data Query Language (DQL)
3. Data Manipulation Language (DML)
4. Data Control Language (DCL)
5. Transaction Control Language (TCL)

Step 1: Install MySQL

To begin, download and install MySQL from its official website:

Once installed, create a new database in MySQL using the following command:

CREATE DATABASE studentDB;

Step 2: Install R Studio

To write and execute R scripts, install RStudio from:

CREATE DATABASE studentDB;

Step 3: Install MySQL Library in R

In RStudio, install the MySQL package with the command:

install.packages("RMySQL")

Now, execute the following R script to connect MySQL with R:

# Load the RMySQL library
library(RMySQL)

# Establish a connection to MySQL database
mysql_connection = dbConnect(MySQL(),
                             user = 'root',
                             password = 'root',
                             dbname = 'studentDB',
                             host = 'localhost')

# List available tables in the database
dbListTables(mysql_connection)

# Creating a table in MySQL database
dbSendQuery(mysql_connection, "CREATE TABLE students (id INT, name VARCHAR(20));")

# Inserting records into the table
dbSendQuery(mysql_connection, "INSERT INTO students VALUES (201, 'Rahul');")
dbSendQuery(mysql_connection, "INSERT INTO students VALUES (202, 'Neha');")
dbSendQuery(mysql_connection, "INSERT INTO students VALUES (203, 'Ankit');")

# Retrieving records from the table
query_result = dbSendQuery(mysql_connection, "SELECT * FROM students")

# Storing result in an R data frame
data_frame = fetch(query_result)

# Displaying the data frame
print(data_frame)

Output:

id   name
1 201  Rahul
2 202  Neha
3 203  Ankit