R - Databases - r - learn r - r programming
- The data is Relational database systems are stored in a normalized format.
- To carry out statistical computing we will need very advanced and complex Sql queries.
- But R can connect easily to many relational databases like MySql, Oracle, Sql server etc. and fetch records from them 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.
- In this tutorial we will be using MySql as our reference database for connecting to R.
r programming database relation
RMySQL Package
- R has a built-in package named "RMySQL" which provides native connectivity between with MySql database.
- You can install this package in the R environment using the following command.
Connecting R to MySql
- Once the package is installed we create a connection object in R to connect to the database.
- It takes the username, password, database name and host name as input.
- When we execute the above code, it produces the following result
Querying the Tables
- We can query the database tables in MySql using the function dbSendQuery().
- 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.
- When we execute the above code, it produces the following result
Updating Rows in the Tables
- We can update the rows in a Mysql table by passing the update query to the dbSendQuery() function.
- After executing the above code we can see the table updated in the MySql Environment.
Inserting Data into the Tables
- After executing the above code we can see the row inserted into the table 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.
- After executing the above code we can see the table created in the MySql Environment.
Dropping Tables in MySql
- We can 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.
- After executing the above code we can see the table is dropped in the MySql Environment.