What is Database in R?

 In the relational database management system, the data is stored in a normalized format. Therefore, to complete statistical computing, we need very advanced and complex SQL queries. The large and huge data which is present in the form of tables require SQL queries to extract the data from it.

R can easily connect with many of the relational databases like MySql, SQL Server, Oracle, etc. When we extract the information from these databases, by default, the information is extracted in the form of data frame. Once, the data comes from the database to the R environment; it will become a normal R dataset. The data analyst can easily analyze or manipulate the data with the help of all the powerful packages and functions.

RMySQL Package

RMySQL package is one of the most important built-in package of R. This package provides native connectivity between the R and MySql database. In R, to work with MySql database, we first have to install the RMySQL package with the help of the familiar command, which is as follows:

1. install.packages("RMySQL")  

When we run the above command in the R environment, it will start downloading the package RMySQL.

Output

We have created a database employee in which there is a table employee_info, which has the following record.

We will use the data which we have mentioned above in our upcoming topics.

Create a connection between R and MySql

To work with MySql database, it is required to create a connection object between R and the database. For creating a connection, R provides dbConnect() function. This function takes the username, password, database name, and host name as input parameters. Let's see an example to understand how the dbConnect() function is used to connect with the database.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # Listing the tables available in this database.  
10.  dbListTables(mysql_connect)  

Output

R MySQL Commands

In R, we can perform all the SQL commands like insert, delete, update, etc. For performing the query on the database, R provides the dbSendQuery() function. The query is executed in MySQL, and the result set is returned using the R fetch () function. Finally, it is stored in R as a data frame. Let's see the example of each and every SQL command to understand how dbSendQuery() and fetch() functions are used.

Create Table

R provides an additional function to create a table into the database i.e., dbWriteTable(). This function creates a table in the database; if it does not exist else, it will overwrite the table. This function takes the data frame as an input.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. #Creating data frame to create a table   
10. emp.data<- data.frame(    
11. name = c("Raman","Rafia","Himanshu","jasmine","Yash"),    
12. salary = c(623.3,915.2,611.0,729.0,843.25),     
13. start_date = as.Date(c("2012-01-01", "2013-09-23", "2014-11-15", "2014-05-11","2015-03-27")),  
14. dept = c("Operations","IT","HR","IT","Finance"),    
15. stringsAsFactors = FALSE    
16. )    
17.   
18. # All the rows of emp.data are taken inot MySql.  
19. dbWriteTable(mysql_connect, "emp", emp.data[, ], overwrite = TRUE)  

Output

Select

We can simply select the record from the table with the help of the fetch() and dbSendQuery() function. Let's see an example to understand how to select query works with these two functions.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # selecting the record from employee_info table.  
10. record = dbSendQuery(mysql_connect, "select * from employee_info")  
11.   
12. # Storing the result in a R data frame object. n = 6 is used to fetch first 6 rows.  
13. data_frame = fetch(record, n = 6)  
14. print(data_frame)  

Output

Select with where clause

We can select the specific record from the table with the help of the fetch() and dbSendQuery() function. Let's see an example to understand how to select query works with where clause and these two functions.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # selecting the specific record from employee_info table.  
10. record = dbSendQuery(mysql_connect, "select * from employee_info where dept='IT'")  
11.   
12. # Fetching all the records(with n = -1) and storing it as a data frame.  
13. data_frame = fetch(record, n = -1)  
14. print(data_frame)  

Output

Insert command

We can insert the data into tables with the help of the familiar method dbSendQuery() function.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # Inserting record into employee_info table.  
10. dbSendQuery(mysql_connect, "insert into employee_info values(9,'Preeti',1025,'8/25/2013','Operations')")  

Output

Update command

Updating a record in the table is much easier. For this purpose, we have to pass the update query to the dbSendQuery() function.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # Updating the record in employee_info table.  
10. dbSendQuery(mysql_connect, "update employee_info set dept='IT' where id=9")  

Output

Delete command

Below is an example in which we delete a specific row from the table by passing the delete query in the dbSendQuery() function.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # Deleting the specific record from employee_info table.  
10. dbSendQuery(mysql_connect, "delete from employee_info where id=8")  

Output

Drop command

Below is an example in which we drop a table from the database by passing the appropriate drop query in the dbSendQuery() function.

Example

1. #Loading RMySQL package into R  
2. library("RMySQL")  
3.   
4. # Creating a connection Object to MySQL database.  
5. # Conneting with database named "employee" which we have created befoe with the helpof XAMPP server.  
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',  
7.    host = 'localhost')  
8.   
9. # Dropping the specific table from the employee database.  
10. dbSendQuery(mysql_connect, "drop table if exists emp")  

Output

What is XML File in R?

Like HTML, XML is also a markup language which stands for Extensible Markup Language. It is developed by World Wide Web Consortium(W3C) to define the syntax for encoding documents which both humans and machine can read. This file contains markup tags. There is a difference between HTML and XML. In HTML, the markup tag describes the structure of the page, and in xml, it describes the meaning of the data contained in the file. In R, we can read the xml files by installing "XML" package into the R environment. This package will be installed with the help of the familiar command i.e., install.packages.

1. install.packages("XML")  

Creating XML File

We will create an xml file with the help of the given data. We will save the following data with the .xml file extension to create an xml file. XML tags describe the meaning of data, so that data contained in such tags can easily tell or explain about the data.

Example: xml_data.xml

1. <records>  
2. <employee_info>  
3. <id>1</id>  
4. <name>Shubham</name>  
5. <salary>623</salary>  
6. <date>1/1/2012</date>  
7. <dept>IT</dept>  
8. </employee_info>  
9.       
10. <employee_info>  
11. <id>2</id>  
12. <name>Nishka</name>  
13. <salary>552</salary>  
14. <date>1/1/2012</date>  
15. <dept>IT</dept>  
16. </employee_info>  
17.   
18. <employee_info>  
19. <id>1</id>  
20. <name>Gunjan</name>  
21. <salary>669</salary>  
22. <date>1/1/2012</date>  
23. <dept>IT</dept>  
24. </employee_info>  
25.   
26. <employee_info>  
27. <id>1</id>  
28. <name>Sumit</name>  
29. <salary>825</salary>  
30. <date>1/1/2012</date>  
31. <dept>IT</dept>  
32. </employee_info>  
33.   
34. <employee_info>  
35. <id>1</id>  
36. <name>Arpita</name>  
37. <salary>762</salary>  
38. <date>1/1/2012</date>  
39. <dept>IT</dept>  
40. </employee_info>  
41.   
42. <employee_info>  
43. <id>1</id>  
44. <name>Vaishali</name>  
45. <salary>882</salary>  
46. <date>1/1/2012</date>  
47. <dept>IT</dept>  
48. </employee_info>  
49.   
50. <employee_info>  
51. <id>1</id>  
52. <name>Anisha</name>  
53. <salary>783</salary>  
54. <date>1/1/2012</date>  
55. <dept>IT</dept>  
56. </employee_info>  
57.   
58. <employee_info>  
59. <id>1</id>  
60. <name>Ginni</name>  
61. <salary>964</salary>  
62. <date>1/1/2012</date>  
63. <dept>IT</dept>  
64. </employee_info>  
65.       
66. </records>  

Reading XML File

In R, we can easily read an xml file with the help of xmlParse() function. This function is stored as a list in R. To use this function, we first need to load the xml package with the help of the library() function. Apart from the xml package, we also need to load one additional package named methods.

Let's see an example to understand the working of xmlParse() function in which we read our xml_data.xml file.

Example: Reading xml data in the form of a list.

1. # Loading the package required to read XML files.  
2. library("XML")  
3.   
4. # Also loading the other required package.  
5. library("methods")  
6.   
7. # Giving the input file name to the function.  
8. result <- xmlParse(file = "xml_data.xml")  
9.   
10. xml_data <- xmlToList(result)  
11. print(xml_data)  

Output

Example: Getting number of nodes present in xml file.

1. # Loading the package required to read XML files.  
2. library("XML")  
3.   
4. # Also loading the other required package.  
5. library("methods")  
6.   
7. # Giving the input file name to the function.  
8. result <- xmlParse(file = "xml_data.xml")  
9.   
10. #Converting the data into list  
11. xml_data <- xmlToList(result)  
12.   
13. #Printing the data  
14. print(xml_data)  
15.   
16. # Exracting the root node form the xml file.  
17. root_node <- xmlRoot(result)  
18.   
19. # Finding the number of nodes in the root.  
20. root_size <- xmlSize(root_node)  
21.   
22. # Printing the result.  
23. print(root_size)  

Output

Example: Getting details of the first node in xml.

1. # Loading the package required to read XML files.  
2. library("XML")  
3.   
4. # Also loading the other required package.  
5. library("methods")  
6.   
7. # Giving the input file name to the function.  
8. result <- xmlParse(file = "xml_data.xml")  
9.   
10. # Exracting the root node form the xml file.  
11. root_node <- xmlRoot(result)  
12.   
13. # Printing the result.  
14. print(root_node[1])  

Output

Example: Getting details of different elements of a node.

1. # Loading the package required to read XML files.  
2. library("XML")  
3.   
4. # Also loading the other required package.  
5. library("methods")  
6.   
7. # Giving the input file name to the function.  
8. result <- xmlParse(file = "xml_data.xml")  
9.   
10. # Exracting the root node form the xml file.  
11. root_node <- xmlRoot(result)  
12.   
13. # Getting the first element of the first node.  
14. print(root_node[[1]][[1]])  
15.   
16. # Getting the fourth element of the first node.  
17. print(root_node[[1]][[4]])  
18.   
19. # Getting the third element of the third node.  
20. print(root_node[[3]][[3]])  

Output

How to convert xml data into a data frame

It's not easy to handle data effectively in large files. For this purpose, we read the data in the xml file as a data frame. Then this data frame is processed by the data analyst. R provide xmlToDataFrame() function to extract the information in the form of Data Frame.

Let's see an example to understand how this function is used and processed:

Example

1. # Loading the package required to read XML files.  
2. library("XML")  
3.   
4. # Also loading the other required package.  
5. library("methods")  
6.   
7. # Giving the input file name to the function xmlToDataFrame.  
8. data_frame <- xmlToDataFrame("xml_data.xml")  
9.   
10. #Printing the result  
11. print(data_frame)  

Output