R MySQL Database

MySQL is the most popular and widely used Open Source Relational Database Management System. It can run on many different platforms without failure, even in low powered PC. It is very lightweight, secure, password protected database management system.

R is capable of communicating with most Database Management Systems (DBMS). R provides a RMySQL package to connect to MySQL. So, let's first install the package.

install.packages('RMySQL') 
library('RMySQL')

Connect to MySQL

R provides dbConnect() function to connect to MySQL database. The syntax of dbConnect() function is as follows -

dbConnect(MySQL(), user='username', password='password', dbname='databasename', host='hostname')

Suppose there is a MySQL database name 'company'. To connect to this database on the local machine, run the following command -

con = dbConnect(MySQL(), user='root', password='', dbname='company', host='localhost')

Here, con is the connection object which we will use further in database operations.





Get Database Info

We can get more information about the database connection by using dbGetInfo() function.

dbGetInfo(con)

Get Database Tables

The dbListTables() function is used to list all tables under the database.

dbListTables(con)

Create a table

R provides dbWriteTable() function to create a new table. The dbWriteTable() syntax is as follows -

dbWriteTable(con, table, values, row.names, overwrite, append)

Here, the con is the database connection object, table is the table name, value is the data to insert, overwrite and append are the boolean values, both are by default false. The overwrite specifies whether to overwrite the existing table or not and the append specifies whether to append the data in the existing table or not. If we set row.names to TRUE, an extra field adds to the table and the name of the field is decided by R.

Suppose we want to create a new table name 'employee' in the existing database name 'company'.

# create a dataframe
dframe <- data.frame(id=c(1:4), first_name=c('Mary','Soy','Alexa','Roxy'),
+ last_name=c('Trump','Oak','jorz','Gaga'), age=c(30,27,36,32),
+ department=c('HR','IT','IT','Acct'))
> dbWriteTable(con,'employee',dframe, row.names=FALSE)
[1] TRUE




Select Query

R provides dbSendQuery() function to fetch table data. The following command returns all the data from employee table.

> query <- dbSendQuery(con, 'SELECT * FROM employee')
> result <- dbFetch(query)
> print(result)
  id first_name last_name age department
1  1       Mary     Trump  30         HR
2  2        Soy       Oak  27         IT
3  3      Alexa      jorz  36         IT
4  4       Roxy      Gaga  32       Acct







Read more articles


General Knowledge



Learn Popular Language