Skip to content

Access MySQL from R Notebook using RMySQL

← Jupyter Notebooks
MySQL is one of the most popular open source relational databases. In this article we will explore how to connect to a MySQL database from a R notebook in Jupyter and perform data access operations.

We will use the RMySQL package, which allows you to access MySQL (and MariaDB) databases using R. RMySQL is pre-installed in your Workbench.

Create a new Jupyter notebook in Data Scientist Workbench. Set it to use R by selecting it in the drop-down box in top right corner Create a new cell, type the following command to load RyMySQL and execute it (Ctrl+Enter):
library(RMySQL)
Next, enter your database credentials. 
#Enter the values for you database connection
dsn_database = "<DB Name>"            
            # e.g. "db89c1c13a2014642a38dee00666f9d0c"
dsn_hostname = "<hostname or IP>" # e.g.: "50.23.230.134"
dsn_port = <port number>          # e.g. 3307 without quotes
dsn_uid = "<username>"            # e.g. "user1"
dsn_pwd = "<password>"         # e.g. "7dBZ3jWt9xN6$o0JiX!m"
Now establish the database connection. 
conn = dbConnect(MySQL(), user=dsn_uid, password=dsn_pwd, dbname=dsn_database, host=dsn_hostname)
conn
Let's create a sample table and insert some data into it.
dbSendQuery(conn, 'DROP TABLE IF EXISTS Cars')
dbSendQuery(conn, 'CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)')
dbSendQuery(conn,"INSERT INTO Cars VALUES(1,'Audi',52642)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(2,'Mercedes',57127)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(3,'Skoda',9000)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(4,'Volvo',29000)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(5,'Bentley',350000)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(6,'Citroen',21000)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(7,'Hummer',41400)")
dbSendQuery(conn,"INSERT INTO Cars VALUES(8,'Volkswagen',21600)")
You can now use the connection object conn to query the database.
query = "SELECT * FROM Cars";
rs = dbSendQuery(conn, query);
df = fetch(rs, -1);
df
It is a good practice to close the connection when you are done.
dbDisconnect(conn)
Good luck!

Feedback and Knowledge Base

(thinking…)