Data is actually database-dependent systems that are stored in a normalized format. So; To perform statistical calculations, we will need a very advanced and complex Sql search. 

Database in R programming language, But R can easily connect to many relational databases such as MySql, Oracle, Sql Server and.. When data is available in R environment; We will have an R dataset and it can be used with all powerful packages and functions; Analyzed or somehow managed.

In this tutorial we will use MuSql as our reference database to connect to R.

RMySQL package

R has an internal package called “RMySQL” that provides a native connection between the MySql database. You can use the following command; Install this package in R environment.

install.packages (“RMySQL”)

R connection to MySql

Once the package is installed, we create an object in R to connect to the database. This object takes the username, password, database name and hostname as input.

# Create a connection Object to MySQL database.

# We will connect to the sampel database named “sakila” that comes with MySql installation.

mysqlconnection = dbConnect (MySQL (), user = ‘root’, password = ”, dbname = ‘sakila’,

host = ‘localhost’)

# List the tables available in this database.

dbListTables (mysqlconnection)

When we run the above code; The following result is obtained:

[1] “actor” “actor_info”

[3] “address” “category”

[5] “city” “country”

[7] “customer” “customer_list”

[9] “film” “film_actor”

[11] “film_category” “film_list”

[13] “film_text” “inventory”

[15] “language” “nicer_but_slower_film_list”

[17] “payment” “rental”

[19] “sales_by_film_category” “sales_by_store”

[21] “staff” “staff_list”

[23] “store”

Search the table

Using the dbSendQuery () function, we can search database tables in MySql. The query is executed in MySql and the resulting set is returned using the fetch () function in R. Eventually; This file is stored in R as a data frame.

# Query the “actor” tables to get all the rows.

result = dbSendQuery (mysqlconnection, “select * from actor”)

# Store the result in a R data frame object. n = 5 is used to fetch first 5 rows.

data.frame = fetch (result, n = 5)

print (data.fame)

When we run the above code; The following result is obtained.

actor_id first_name last_name last_update

1 1 PENELOPE GUINESS 2006-02-15 04:34:33

2 2 NICK WAHLBERG 2006-02-15 04:34:33

3 3 ED CHASE 2006-02-15 04:34:33

4 4 JENNIFER DAVIS 2006-02-15 04:34:33

5 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33

Inquiry with filter strap

We can use any virtual selection query to get the result; Transfer:

result = dbSendQuery (mysqlconnection, “select * from actor where last_name = ‘TORN'”)

# Fetch all the records (with n = -1) and store it as a data frame.

data.frame = fetch (result, n = -1)

print (data)

When we run the above code; The following results are obtained:

actor_id first_name last_name last_update

1 18 DAN TORN 2006-02-15 04:34:33

2 94 KENNETH TORN 2006-02-15 04:34:33

3 102 WALTER TORN 2006-02-15 04:34:33

Update row rows in tables

We can update the rows in the Mysql table by passing the update query to the dbSendQuery () function.

dbSendQuery (mysqlconnection, “update mtcars set disp = 168.5 where hp = 110”)

After executing the above code, we can see the updated table in MySql environment.

Insert data into tables

dbSendQuery (mysqlconnection,

Insert into mtcars (row_names, mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb)

values ​​(‘New Mazda RX4 Wag’, 21, 6, 168.5, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4) ”

)

After executing the above code, we can see the rows inserted in the table in the MySql environment.

Create tables in MySql

Using the dbWriteTable () function we can create a table in MySql. This function if the table is already; would have existed; It overwrites it and adopts a data framework as input.

# Create the connection object to the database where we want to create the table.

mysqlconnection = dbConnect (MySQL (), user = ‘root’, password = ”, dbname = ‘sakila’,

host = ‘localhost’)

# Use the R data frame “mtcars” to create the table in MySql.

# All the rows of mtcars are taking inot MySql.

dbWriteTable (mysqlconnection, “mtcars”, mtcars [,], overwrite = TRUE)

After executing the above code, we can see the table created in the MySql environment.

Delete tables in MySql

We can also delete tables in the MySql database; To do this, we need to put the drop table expression in the dbSendQuery () function; In the same way that we can use it to search table data.

dbSendQuery (mysqlconnection, ‘drop table if exists mtcars’)

After executing the above code, we can see that the table has been removed in the MySql environment.