How to Write Entire Dataframe into MySQL Table in R

In this article, we are going to learn how to write the entire data frame into a MySQL table in R programming language.

To perform this task we need some basic requirements which are listed below:

• Install MySQL
• Install R Studio
• Basic knowledge of SQL queries.

Using SQL queries create a database “gfg_database”  and in this data base create a sample table with two columns ‘subject’ and ‘marks’ in which we are going to store data using R programming language.

Table used in the code:

Structure of Table

What is RMySQL?

In order to deal with MySQL tables in the R environment, the package RMySQL package should be installed into the environment. The package acts as a database interface and MySQL driver for R. Below command is used to install the RMySQL package in R. 

install.packages("RMySQL")

dbConnect() Method

A local connection can be set up from the Rstudio to the MYSQL server. The server should be started before starting the script. The dbConnect() method is used to create a connection between the continuing R session and an MySQL database. It takes as arguments such as hostname, password, etc to establish the connection.

Syntax: dbConnect(drv, …)

Arguments : 

• drv – existing DBI Connection object (for instance ‘dbConnect(RMySQL::MySQL())‘ )
• … – these includes ‘user’, ‘password’, ‘dbname’, ‘host’, ‘port’, etc.

Creating data frame

A data frame can be created using the data.frame() method where in we specify the contents of the table which we wish to write to the MySQL table.  The SQL query is then generated by using the table name in which we wish to put the data. The data frame values are used as values to put in the MySQL table. 

Send data to a MySQL table

A SQL query with the dynamic data is created by the paste0() method which takes the string values from the columns and inputs them into the data frame. The query is then sent to the database using the dbSendquery() method. 

The final method dbSendQuery() method is then used to execute the MySQL query on the R environment. This method is responsible for the invocation of the specified SQL query on the database engine. 

dbSendQuery(connection , sql-query)

In the following code snippet, initially, a connection is established using the dbConnect() method, which takes as input the username and the password respectively. A dummy data frame containing the values is then created using the data.frame() method which has two columns namely subject and marks respectively. 

R

# installing the required libraries
library("RMySQL")
# setting up a db connection
connection <- dbConnect(MySQL(), user = 'root',
        password = '12345', host = 'localhost',
                        dbname = 'gfg_database')

# creating a data frame
data_frame <- data.frame(subject = c("Maths",
            "Science","CS"), marks = c(22,38,76))

# creating the query
query <- "insert into sample_table(subject,marks) VALUES"

# inserting values in sql query
query <- paste0(query, paste(sprintf("('%s', '%s')",
   data_frame$subject, data_frame$marks), collapse = ","))

# sending the query to database
dbSendQuery(connection,query)

Output: After executing the above code if we display the data of the table using SQL query we will get the below output.

In the above code firstly install “RMySQL” package and then set up a connection between ‘R’ and ‘MySQL’ after that create a data frame using which we are going to store the data in MySQL table. In MySQL data is inserted using a SQL query so create a query and process that data using paste0() method and finally send that data to the MySQL database table using dbSendQuery() method.