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 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