How to connect MySql database in R

Today I want to show you an example to connect Rstudio with MySQL server, to do that, yo will need the library RMySQL

library(RMySQL)

## Loading required package: DBI

First of all, we need to create a conexion with the Database, to do that we need the user, password, db name and host.

MyDataBase = dbConnect(MySQL(), user='User_DataBase', password='Password_DB', dbname='Name_DataBase', host='Your hosting')

If we want to know the name of the table in the data base we can use the function “dbListTables”

dbListTables(MyDataBase)

##  [1] "Table1"              "Table7"           
##  [3] "Table2"              "Table8"             
##  [5] "Table3"              "Table9"     
##  [7] "Table4"              "Table10"             
##  [9] "Table5"              "Table11"             
## [11] "Table6"              "Table12"

Once we know the tables, we can see what is inside of each one with “dbListFields”.

dbListFields(MyDataBase, 'Table1')

## [1] "variable1"    "variable2"       "variable3"

Now, we have connection with the database and using the function “dbGetQuery”, we can get the data that we need.

DataDB = dbGetQuery(MyDataBase, "select * from Table1")
head(DataDB)


##   variable1           variable2            variable3
## 1   data1               data5               data8
## 2   data2               data6               data9
## 3   data3               data7               data10
## 6   data4               data8               data11

As you can see is very easy extract data from a database in MySql with R. As always if you need help or dudes write us in the comments. Bye!

Summary