How to closing active connections using RMySQL

When we are using RMySQL, it is important to ensure that active connections to the database are properly closed after all tasks are completed. Closing connections is important for a number of reasons. First, it helps reduce system resources allotted for connections, prevent resource exhaustion, and improve overall system performance. Second, closing the connection ensures the integrity of the data stored in the database. Closing the connection commits or rolls back any pending transactions or changes, preventing data inconsistency or loss. By following the practice of closing connections after complеting databasе opеrations, users can maintain system stability and efficiency while ensuring data accuracy and reliability.

Requirements

Please make sure that you have R and R Studio installed with the following R libraries: DBI and RMySQL on your system. If you are unable to install these libraries then you can simply install them by executing the following command in the R Studio console:

install.packages("DBI")
install.packages("RMySQL")

*DBI package is required by RMySQL to establish a connection with the database and execute queries and retrieve information from the database within the R environment.

How to close active connections using RMySQL

Here is a step-by-step guide to closing active connections using RMySQL:

Step 1: Importing Required Library

This is our first step. In this step, we need to import the RMySQL package to connect to the MySQL database, then close the active connection to RMySQL:

# Importing the library
library(DBI)
library(RMySQL)

DBI package is required by RMySQL to establish a connection with the database and execute queries and retrieve information from the database within the R environment.

Step 2: Establish a Database Connection

After successfully importing the library, we now need to establish a connection with the database. With the help of the dbConnect() function, we can simply establish a connection with the database. This function requires the necessary details such as the host, username, password, and database name.

Syntax:

dbConnect(RMySQL::MySQL(), dbname = "database_name", host = "localhost", port = 3306, 
                user = "username", password = "password")

# creating a database connection
connection <- dbConnect(RMySQL::MySQL(), 
                 dbname = "Rlanguage", 
                 host = "localhost", 
                 port = 3306, 
                 user = "username", 
                 password = "password")

We use a database called RLanguage on localhost. As you can see, we have provided the port number, username, and password required to access the database.

Step 3: Select a table or Perform any Operation which you want

The following R code retrieves data from a database using a SQL query. This query sеlеcts all columns from the Studеnts tablе. Use the dbGеtQuеry function to retrieve the result and store it in the result variable.

query <- "SELECT * FROM students";
result <- dbGetQuery(connection,query);

output:

Step 4: Close the Database Connection

This is the final step. After all work has been completed successfully, the connection to the database should be closed. To do this, use the dbDisconnect() function, which closes the active connection to the database. Only one connection variable is required for this function to disconnect from the database.

dbDisconnect(connection)

Output:

Advantages and Disadvantages of closing active connections

Here are some advantages and disadvantages of closing active connections:

ishukatiyar16

Follow

Improve

Article Tags :

• R Language