Visualizing Arlington Bikometers

Part 3: Setup Your Database and Connections

   

Introduction

One of the aims for this project was to create a pipeline that foregoes saving individual ‘data’ files (e.g. csv, xml, json, txt) to the computer and instead uses a database to store the data. After reading over this great article that gives a good overview of SQLite, PostgreSQL, and MySQL, I decided on MySQL to handle my database. Also, my favorite Udemy instructor, Colt Steele, had a MySQL course on sale The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert that I highly recommend.

Goal

Setup MySQL database locally and connect Python and R to the databases directly.

Steps

1. Install MySQL
2. Create the Database
3. Connect Python to MySQL with SQLAlchemy and MySQLdb
4. Connect R to MySQL with odbc

 

Step 1: Install MySQL

I followed these instructions to download MySQL for my operating system (Windows). I chose the ‘Developer Default’ setup type to get the server, the MySQL Workbench, and the odbc/MySQL connector. If you are a pro at MySQL, you may not need the Workbench, but the other two are essential.

Under the ‘Type and Networking’ section, I chose the ‘Standalone MySQL Server/ Classic MySQL Replication. I left the default config type: development machine, set the ’root’ password and applied the configuration.

Step 2: Create the MySQL Database

We need to run some SQL commands. I usually use the MySQL workbench to run the MySQL statements but the GUI is a little slow so if you can manage it, using the MySQL Command Line Client will be more responsive.

First let’s create the database and call it bikeometers_db.

CREATE DATABASE bikeometers_db;

Now that we have the database created, let’s connect it to Python.

Step 3: Connect Python to MySQL

We will need to install the Python modules: SQLAlchemy and MySQLdb

If you are confused about how to install Python modules, this is a great article.

For me, I don’t have stand-alone Python installed on Windows, I just have my conda environment installed from the Anaconda Suite, so using python from the Windows command line is out of the question. Instead, I can type ‘pip install SQLAlchemy mysqlclient’ directly from my Spyder IDE and it will install the modules SQLAlchemy and MySQLdb respectively.

Next, we will import the two modules into Python: SQLAlchemy and the built-in Python module getpass which we will use to enter our database password.

from sqlalchemy import create_engine
from getpass import getpass

Now, let’s make the connection from Python to the database.

Using the SQLAlchemy create_engine function, pass in the name of your database as an f-string like I do below.

Note: below is the how the function is supposed to look when using Python. However, to create this post I’m actually using R and executing the Python code using an R library called ‘reticulate’ and I couldn’t get the the python module getpass to work. However, I’ve tried this code in Python and it works like a charm.

engine = create_engine(f'mysql+mysqldb://{input("Enter username: ")}:{getpass("Enter password: ")}@localhost/bikeometers_db')

As an alternative to securely entering your password each time you connect using the code above, I’ll use the Python module os to import my Environment Variables arlington_user and arlington_password which wont be saved in this code, protecting my database credentials.

import os
# Workaround to get this document to Knit
engine = create_engine(f'mysql+mysqldb://{os.environ["arlington_user"]}:{os.environ["arlington_password"]}@localhost/bikeometers_db', echo=False)

However, SQLAlchemy is ‘lazy’ and won’t actually try to make the connection until we explicitly tell it. So, the below code will test the connection and throw an error if a connection cannot be made.

engine.connect()

## <sqlalchemy.engine.base.Connection object at 0x0000000035336080>

If a ‘sqlalchemy.engine.base.Connection object’ is returned, that means you have successfully connected to your database!

Let’s make our first request from Python to MySQL.

First, we will assign the connection to the variable ‘con’.

con = engine.connect()

Next, we will use the execute method from SQLAlchemy on our ‘con’ variable and pass in a SQL command as an argument.

databases = con.execute('SHOW DATABASES;')
databases

## <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000000035343240>

We can look inside our ‘cursor’ object to see the data using a for loop.

for item in databases:
  print(item[0])

## bikeometers_db
## counts
## information_schema
## mysql
## performance_schema
## sys

These are the databases currently on my computer.

When we are done with our SQL commands, best practice is to close the connection with the following code.

engine.dispose()

Great! Now that Python can talk to MySQL, we will connect R to MySQL.

Step 4: Connect R to MySQL

RStudio has a great website about connecting R and MySQL here that you can use if you are having trouble making the connection. I’ll outline the steps I used below.

In Step 1 of this post, we installed the MySQL/odbc Connector when we installed MySQL. If you didn’t install the connector, you can find it here. After installation, you will want to restart your computer to ensure it is properly recognized.

Once installed, make sure that both the odbc and DBI packages are installed in R.

First, we will load the odbc and DBI package in R.

library(odbc)
library(DBI)

Next, we will assign the connection to the variable ‘con’. You will notice that another password package is used to enter your database password. This ‘rstudioapi::askForPassword’ code will call the askForPassword function inside the rstudioapi package.

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={MySQL ODBC 8.0 Unicode Driver};", 
                 server = "localhost", db = "bikeometers_db", user = readline(prompt = 'Enter user name: '), password = rstudioapi::askForPassword("Database password"))

However, I ran into a similar problem as before in that when ‘building’ this website, I can’t use the askForPassword R library. So again, I have saved my database login credentials in as Environment Variables. I can now ‘build’ this post without problems and subsequently publish the code without exposing my credentials. To learn more about my decision to use Enviornment Variables, see my post Publish Rmarkdown Documents With Database Connections Without Exposing Credentials

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={MySQL ODBC 8.0 Unicode Driver};", 
                 server = "localhost", db = "bikeometers_db", user = Sys.getenv("arlington_user"), password = Sys.getenv("arlington_password"))

If you are using RStudio, and your connection is successful, you will now see a list of your MySQL databases in your ‘Connections’ tab.

Let’s send a MySQL Command from R to MySQL to confirm the two can communicate.

We will use R to send the same MySQL command as we did above using Python.

sql_cmd <- "SHOW DATABASES;"
bikeometers_db <- dbGetQuery(con, sql_cmd)
bikeometers_db

##             Database
## 1     bikeometers_db
## 2             counts
## 3 information_schema
## 4              mysql
## 5 performance_schema
## 6                sys

If you see a table of your databases, congratulations!

In the next post, we will save the data we pull from the Bike Arlington API into our MySQL database.