R Databases - R Programming language

What are R Databases?

Usually all Relational database systems are stored in a normalized format. To perform statistical computing we need very advanced and complex Sql queries. But R programming language supports many relational databases like MySql, Oracle, Sql server etc to fetch records from databases as a data frame. Once the data is available in the R environment, it becomes a normal R data set and can be manipulated or analyzed using all the powerful packages and functions.
R Databases tutorial explained with using MySql as our reference database for connecting to R.

RMySQL Package

R has a built-in package named "RMySQL" which provides native connectivity between with MySql database. To perform this you can install this package in the R environment using the following command.

Connecting R to MySql

Once the installation is done then create a connection object in R to connect to the database. Details like username, password, database name and host name as input are enough to connect R to Mysql.
When we execute the above code, it produces the following result –

Querying the Tables

dbSendQuery() function is useful to query the database tables in MySql. The query gets executed in MySql and the result set is returned using the R fetch() function. Finally it is stored as a data frame in R.
When we execute the above code, it produces the following result –

Query with Filter Clause

We can pass any valid select query to get the result.
Above code will produce below output:

Updating Rows in the Tables

Updating Rows in the table can be done by passing the update query to the dbSendQuery() function.
Once the code is executed ,we can observe the table updated in the MySql Environment.

Inserting Data into the Tables

Once the code is executed ,we can observe the row inserted in the MySql Environment.
.

Creating Tables in MySql

We can create tables in the MySql using the function dbWriteTable(). It overwrites the table if it already exists and takes a data frame as input.
Once the code is executed ,we can observe the table updated in the MySql Environment.

Dropping Tables in MySql

If you want to drop the tables in MySql database passing the drop table statement into the dbSendQuery() in the same way we used it for querying data from tables.
Once the code is executed ,we can observe the table dropped in the MySql Environment.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

R Programming language Topics