Working with Databases in R Programming

In R programming, a number of datasets are passed to the functions to visualize them using statistical computing. So, rather than creating datasets again and again in console, we can pass those normalized datasets from relational databases. R can be connected to many relational databases such as Oracle, MySQL, SQL Server, etc and fetches the result as data frame. Once the result set is fetched into data frame, it becomes very easy to visualize and manipulate them. In this article, we’ll discuss MySQl as reference to connect with R, creating, dropping, inserting, updating and querying the table using R.

Connecting MySQL with R

R requires RMySQL package to create a connection object which takes username, password, hostname and database name while calling the function. dbConnect() function is used to create the connection object in R.

Syntax:
dbConnect(drv, user, password, dbname, host)

Parameter values:
drv represents Database Driver
user represents username
password represents password value assigned to Database server
dbname represents name of the database
host represents host name

Example:



filter_none

edit
close

play_arrow

link
brightness_4
code

# Install package
install.packages("RMySQL")
  
# Loading library
library("RMySQL")
  
# Create connection
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                      dbname = 'GFG', host = 'localhost')
  
# Show tables in database
dbListTables(mysqlconn)

chevron_right


Tables present in given database:

Output:

Loading required package: DBI
[1] "articles"

Create Tables in MySQL Using R

Tables in MySQL can be created using function dbWriteTable() in R. This function overwrites the table if table already exists.

Syntax:
dbWriteTable(conn, name, value)

Parameter value:
conn represents connection object
name represents name of the table in MySQL
value represents dataframe that has to be presented as MySQL table

Example:

filter_none

edit
close

play_arrow

link
brightness_4
code

# Create connection object
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                            dbname = 'GFG', host = 'localhost')
  
# Create new table mtcars
dbWriteTable(mysqlconn, "mtcars", mtcars[1:10, ], overwrite = TRUE)

chevron_right


Output:

[1] TRUE

Database table content:

Drop Tables in MySQL Using R

To perform other operations than creating table, dbSendQuery() function is used to execute a query.



Syntax:
dbSendQuery(conn, statement)

Parameter values:
conn represents connection object
statement represents query to be executed in MySQL

Example:

filter_none

edit
close

play_arrow

link
brightness_4
code

# Create connection object
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                             dbname = 'GFG', host = 'localhost')
  
# Drop table mtcars from database
dbSendQuery(mysqlconn, 'DROP TABLE mtcars')

chevron_right


Output:

<MySQLResult:745348760, 3, 5>

Database content:

Insert into Table in MySQL Using R

Example:

filter_none

edit
close

play_arrow

link
brightness_4
code

# Create connection object
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                            dbname = 'GFG', host = 'localhost')
  
# Inserting into articles table
dbSendQuery(mysqlconn, "insert into articles(sno, type
values(1, 'R language')"
)

chevron_right


Output:

<MySQLResult:745348760, 3, 6>

Database content:

Updating a Table in MySQL Using R

Example:



filter_none

edit
close

play_arrow

link
brightness_4
code

# Create connection object
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                      dbname = 'GFG', host = 'localhost')
  
# Update query
dbSendQuery(mysqlconn, "UPDATE articles SET sno = 10 where type = 'R language'")

chevron_right


Output:

<MySQLResult:-1, 3, 7>

Database content:

Querying a Table in MySQL Using R

Example:

filter_none

edit
close

play_arrow

link
brightness_4
code

# Create connection object
mysqlconn = dbConnect(MySQL(), user = 'root', password = 'welcome',
                      dbname = 'GFG', host = 'localhost')
  
# Select all rows from articles table
res = dbSendQuery(mysqlconn, "SELECT *FROM articles")
  
# Fetch first 3 rows in data frame
df = fetch(res, n = 3)
print(df)

chevron_right


Output:

  sno       type
1   1 Data Struc
2   2       Algo
3   3       Java

Database content:




My Personal Notes arrow_drop_up

Blockchain Enthusiast

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.