Data visualization with Python-drawn MySQL data graph

All Python code for this tutorial can be obtained from Ipython notebook on the web.

Consider using plotly in your company? Take a look at Plotly's On-premises Enterprise Edition. (Note: On-premises refers to the software running in the workplace or within the company, see Wikipedia)

Note the operating system: Although Windows or Mac users can follow this article, this article assumes that you are using Ubuntu (Ubuntu Desktop or Ubuntu Server Edition). If you don't have Ubuntu Server, you can build a cloud platform from Amazon's Web service (read the first half of the tutorial). If you're using a Mac, we recommend that you buy and download vmware Fusion and install Ubuntu desktop on it. You can also buy a cheap pre-installed Ubuntu Desktop/server version of your notebook or server via Zareason.

Using Python to read MySQL data and draw it is simple, all the tools you need are free to download. This article will show you how to do it. If you have a problem or are stuck, you can send an email to feedback@plot.ly, or you can comment under this article, or @plotlygraphs on tweeter.
1th step: Make sure MySQL is installed and running

First, you need to have a MySQL-installed computer or server. You can check whether MySQL is installed by opening the console, entering "MySQL" If you receive an error that MySQL cannot connect to, which means that MySQL is installed but not running. In the command line or "Terminal", try entering sudo/etc/init.d/mysql start and press ENTER to start MySQL.

If MySQL is not installed, don't be disappointed. To download and install in Ubuntu, just one line of command:

shell> sudo apt-get install mysql-server--fix-missing

During the installation process, you will be asked to enter a password. After the installation is complete, you can enter the following command into the MySQL console in the terminal:

shell> sudo mysql-uroot-p

Enter "Exit" to exit the MySQL console.

This tutorial uses the MySQL classic "World" sample database. If you want to follow our steps, you can download the world database from the MySQL Documentation Center. You can also use wget to download from the command line:

shell> wget Http://downloads.mysql.com/docs/world.sql.zip

Then unzip the file:

Shell> Unzip World.sql.zip

(if unzip is not installed, enter sudo apt-get install unzip installation)

Now you need to import the world database into MySQL and start the MySQL console:

shell> sudo mysql-uroot-p

After entering the console, use the following MySQL command to create the world database using the World.sql file:

mysql> CREATE DATABASE world;mysql> use world;mysql> source/home/ubuntu/world.sql;

(in the source command above, make sure to change the path to your own World.sql directory).
The above instructions are excerpted from the MySQL Documentation Center.
2nd step: Connect MySQL with Python

Using Python to connect to MySQL is simple. The key is to install Python's mysqldb package. You need to install two dependencies first:

shell> sudo apt-get install python-devshell> sudo apt-get install Libmysqlclient-dev

Then install the Python mysqldb package:

shell> sudo pip install Mysql-python

Now, start Python and import mysqldb. You can do this in the command line or Ipython notebook:

shell> python>>> Import MySQLdb

To create a connection to the world database in MySQL:

>>> conn = MySQLdb.connect (host= "localhost", user= "root", passwd= "XXXX", db= "World")

The cursor is the object used to create the MySQL request.

>>> cursor = conn.cursor ()

We will execute the query in the Country table.
3rd step: Execute MySQL query in python

The cursor object executes the query using the MySQL query string, returning a tuple with multiple tuples-one for each row. If you have just contacted MySQL syntax and commands, the online MySQL reference manual is a great Learning resource.

>>> cursor.execute (' Select Name, Continent, Population, Lifeexpectancy, GNP from country ');>>> rows = C Ursor.fetchall ()

Rows, the result of a query, is a tuple that contains multiple tuples, as follows:

It is easier to use Pandas's dataframe to process each row than to use a tuple containing tuples. The following Python code fragment converts all rows to Dataframe instances:

>>> Import Pandas as pd>>> df = PD. DataFrame ([[IJ for IJ in i] for I in rows]) >>> df.rename (columns={0: ' Name ', 1: ' Continent ', 2: ' Population ', 3 : ' Lifeexpectancy ', 4: ' GNP '}, inplace=true);>>> df = Df.sort ([' lifeexpectancy '], ascending=[1]);

Complete code can be found in Ipython notebook
4th step: Using plotly to draw MySQL data

Now, MySQL data is stored in Pandas's dataframe, which makes it easy to draw. The following code is used to plot the national GNP (GNP) vs. average life chart, and the mouse hover point will show the country name. Make sure you have downloaded the plotly Python library. If not, you can refer to its getting Started guide.

Import plotly.plotly as Pyfrom plotly.graph_objs import * Trace1 = scatter (   x=df[' lifeexpectancy '],   y=df[' GNP ') ,   text=country_names,   mode= ' markers ') layout = layout (   xaxis=xaxis (title= ' life expectancy '),   YAxis =yaxis (type= ' log ', title= ' GNP ')) data = data ([Trace1]) FIG = figure (Data=data, layout=layout) py.iplot (Fig, filename= ') World GNP vs. life expectancy ')

The complete code is in this Ipython notebook. The following is a result diagram embedded as an iframe:

Using the bubble Chart tutorial in Plotly's Python User guide, we can draw a bubble chart with the same MySQL data, the bubble size represents the number of people, the color of the bubbles represents different continents, and the mouse hover displays the country name. The following shows a bubble chart embedded as an IFRAME.

Creating this diagram and all the Python code in this blog can be copied from this Ipython notebook.

