Home » How to connect MySql database in R

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
How to connect MySql database in R
Article Name
How to connect MySql database in R
Description
If do you want to connect MySql database in R, you can use the library MySql, Here an example where you can see how to do that.
Author
Publisher Name
Blog-R
Publisher Logo