R MySQL – Read Data

Introduction

Reading data from a MySQL database into R allows you to analyze and visualize your data directly within R. The RMySQL package provides functions to connect to MySQL databases and execute SQL queries, making it easy to fetch data into R for further processing.

Installing and Loading the RMySQL Package

First, you need to install and load the RMySQL package. You can install it from CRAN using the install.packages() function.

Installing the Package

# Install the RMySQL package
install.packages("RMySQL")

Loading the Package

# Load the RMySQL package
library(RMySQL)

Establishing a Connection

To establish a connection to a MySQL database, you need the following information:

  • Hostname or IP address of the MySQL server
  • Username
  • Password
  • Database name

Example: Establishing a Connection

Example:

# Establishing a connection to the MySQL database
conn <- dbConnect(RMySQL::MySQL(),
                  dbname = "your_database_name",
                  host = "your_host_name",
                  port = 3306,  # Default MySQL port
                  user = "your_username",
                  password = "your_password")

# Check if the connection is successful
print(conn)

Creating a Table and Inserting Data

For demonstration purposes, let’s create a table and insert some sample data. You can skip this step if you already have a table with data in your MySQL database.

Example: Creating a Table and Inserting Data

Example:

# SQL query to create a table
create_table_query <- "
CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  gender CHAR(1) NOT NULL,
  salary DECIMAL(10, 2) NOT NULL
);
"

# Executing the query to create the table
dbSendQuery(conn, create_table_query)
print("Table 'employees' created successfully")

# SQL query to insert data into the table
insert_data_query <- "
INSERT INTO employees (name, age, gender, salary) VALUES
('Alice', 30, 'F', 50000.00),
('Bob', 25, 'M', 45000.00),
('Charlie', 35, 'M', 55000.00),
('Diana', 28, 'F', 48000.00),
('Eve', 40, 'F', 60000.00);
"

# Executing the query to insert data
dbSendQuery(conn, insert_data_query)
print("Data inserted into 'employees' table successfully")

Reading Data

You can read data from a MySQL table into R using the dbGetQuery() function. This function executes an SQL query and returns the result as a data frame.

Example: Reading Data

Example:

# SQL query to read data from the table
read_data_query <- "SELECT * FROM employees;"

# Executing the query to read data
data <- dbGetQuery(conn, read_data_query)

# Printing the data
print("Data read from 'employees' table:")
print(data)

Example Program Using MySQL Connection to Read Data

Here is an example program that demonstrates the complete process of connecting to a MySQL database, creating a table, inserting data, reading data, and closing the connection.

Example Program

# R Program to Demonstrate Reading Data from MySQL Database

# Install and load the necessary package
install.packages("RMySQL")
library(RMySQL)

# Establishing a connection to the MySQL database
conn <- dbConnect(RMySQL::MySQL(),
                  dbname = "your_database_name",
                  host = "your_host_name",
                  port = 3306,  # Default MySQL port
                  user = "your_username",
                  password = "your_password")

# Check if the connection is successful
print("Connection established:")
print(conn)

# SQL query to create a table
create_table_query <- "
CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  gender CHAR(1) NOT NULL,
  salary DECIMAL(10, 2) NOT NULL
);
"

# Executing the query to create the table
dbSendQuery(conn, create_table_query)
print("Table 'employees' created successfully")

# SQL query to insert data into the table
insert_data_query <- "
INSERT INTO employees (name, age, gender, salary) VALUES
('Alice', 30, 'F', 50000.00),
('Bob', 25, 'M', 45000.00),
('Charlie', 35, 'M', 55000.00),
('Diana', 28, 'F', 48000.00),
('Eve', 40, 'F', 60000.00);
"

# Executing the query to insert data
dbSendQuery(conn, insert_data_query)
print("Data inserted into 'employees' table successfully")

# SQL query to read data from the table
read_data_query <- "SELECT * FROM employees;"

# Executing the query to read data
data <- dbGetQuery(conn, read_data_query)

# Printing the data
print("Data read from 'employees' table:")
print(data)

# Closing the connection
dbDisconnect(conn)
print("Connection closed")

Conclusion

In this chapter, you learned how to read data from a MySQL database into R using the RMySQL package. You also learned how to create a table, insert data, and close the database connection. By mastering these functions, you can efficiently fetch and analyze data stored in MySQL databases within your R programs.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top