Graph data from a MySQL database in Python

All Python code for this tutorial is available online in this IPython notebook.

Thinking of using Plotly at your company? See Plotly’s on-premise, Plotly Enterprise options.

Note on operating systems: While this tutorial can be followed by Windows or Mac users, it assumes a Ubuntu operating system (Ubuntu Desktop or Ubuntu Server). If you don’t have a Ubuntu server, its possible to set up a cloud one with Amazon Web Services (follow the first half of this tutorial). If you’re using a Mac, we recommend purchasing and downloading VMware Fusion, then installing Ubuntu Desktop through that. You can also purchase an inexpensive laptop or physical server from Zareason, with Ubuntu Desktop or Ubuntu Server preinstalled.

Reading data from a MySQL database and graphing it in Python is straightforward, and all the tools that you need are free and online. This post shows you how. If you have questions or get stuck, email, write in the comments below, or tweet to @plotlygraphs.

STEP 1: make sure mysql is installed and running

First, you should have a computer or server with MySQL installed. You can test to see if MySQL is installed by opening your terminal and typing “mysql”. If you receive an error that MySQL cannot connect, it means that MySQL is installed, but not running. In the command line or “Terminal”, try typing sudo /etc/init.d/mysql start and press return to start MySQL.

If MySQL isn’t installed, don’t despair. Its a one-liner in Ubuntu to download and install:

The installation procedure will ask you to choose a password. When finished, you can access the MySQL shell in your terminal by typing:

Exit the MySQL shell by typing “exit”.

This tutorial uses the classic “world” MySQL example database. If you want to follow along, you can download it here from the MySQL documentation center. You can also download it from the command line with wget:

And unzip the file with:

(Type sudo apt-get install unzip if unzip isn’t installed).

To add the world database in MySQL, start the MySQL shell:

We’re in! Now create the world database using the world.sql file with these MySQL commands:

(Make sure you change the path for the SOURCE command to the directory where your world.sql file is). These instructions were taken from the MySQL documentation center.

STEP 2: connect to mysql from python

MySQL is easy to access from Python. The key is to install the Python library MySQLdb. Two dependencies need to be installed first:

Finally, install the Python library MySQLdb:

Now, start Python and import MySQLdb. You can do this from the shell or an IPython notebook.

Create a connection to the MySQL world database:

cursor is the object you’ll use to create MySQL queries.

Let’s run a query from the Country table.

STEP 3: run a mysql query in python

The cursor object takes MySQL query strings and returns the result as a tuple of tuples – one tuple for every row. If you’re new to MySQL syntax and commands, the online MySQL Reference Manual is a great resource.

rows, the result of this query, is a tuple of tuples that looks like this: (('Aruba', 'North America', 103000L, 78.4, 828.0), ('Afghanistan', 'Asia', 22720000L, 45.9, 5976.0), ('Angola', 'Africa', 12878000L, 38.3, 6648.0), ('Anguilla', 'North America', 8000L, 76.1, 63.2) ...

Where each tuple is a row. As a table, this looks like:

Name Continent Population LifeExpectancy GNP
237 Zambia Africa 9169000 37.2 3377
143 Mozambique Africa 19680000 37.5 2891
148 Malawi Africa 10925000 37.6 1687
238 Zimbabwe Africa 11669000 37.8 5951
2 Angola Africa 12878000 38.3 6648

Its easier to work with rows as a Pandas DataFrame than a tuple of tuples. Here’s a Python code snippet to convert rows to a DataFrame object:

For the full code, see this IPython notebook.

STEP 4: graph the MYSQL data using Plotly

Now, with the MySQL data inside a Pandas DataFrame, its easy graph this data. Here’s a code snippet for a graph of country GNP versus life expectancy, with the country name showing on hover. Make sure you’ve downloaded Plotly’s Python library. If you haven’t, you can follow this getting started guide.

You can access the full-code in this IPython notebook. Here is the resulting graph embedded as an iframe:

Using the bubble chart tutorial from Plotly’s Python User Guide, we can use the same MySQL data to create a bubble chart with markers sized by population, colored by continent, and displaying country name on hover. Below is the resulting bubble chart embedded as an iframe.

All of the Python code to create this chart and for this blog post can be copied from this IPython notebook.

Questions? Stuck? Email, write in the comments below, or tweet to @plotlygraphs.