Use Python to draw MySQL data graphs for data visualization

This article mainly introduces how to use Python to plot MySQL data graphs for data visualization, including connection setup between Python and MySQL, and execution of MySQL statement queries using Python, for more information about the Python code, see IPython notebook.

Consider using Plotly in the company? Take a look at the on-premises Enterprise Edition of Plotly. (Note: On-premises indicates that the software runs in the workplace or within the company. for details, see Wikipedia)

Note: Although Windows or Mac users can follow this article, this article assumes that you are using Ubuntu (Ubuntu desktop or Ubuntu Server ). If you do not have Ubuntu Server, you can create a cloud platform through Amazon Web Services (read the first half of this tutorial ). If you are using a Mac, we recommend that you purchase and download VMware Fusion and install the Ubuntu Desktop version on it. You can also purchase a cheap pre-installed Ubuntu Desktop/Server version notebook or server through Zareason.

Using Python to read MySQL data and draw easily, all the tools you need can be downloaded for free. This article will show you how to do this. If you encounter problems or get stuck, you can send an email to The feedback@plot.ly or comment below this article, or on tweeter @ plotlygraphs.
Step 2: Make sure that MySQL is installed and running

First, you need a computer or server installed with MySQL. You can use the following method to check whether MySQL is installed: Open the console and enter "mysql". if you receive an error that MySQL cannot be connected, it means that MySQL is installed but not running. In the command line or "Terminal", enter sudo/etc/init. d/mysql start and press enter to start MySQL.

Do not be disappointed if MySQL is not installed. To download and install the SDK in Ubuntu, run the following command:

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

You will be asked to enter a password during installation. After the installation is complete, you can enter the MySQL console by typing the following command 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

Decompress the file:

shell> unzip world.sql.zip

(If unzip is not installed, enter sudo apt-get install unzip to install it)

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

shell> sudo mysql -uroot -p

On the console, run the following MySQL command to create a 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 the directory where your own world. SQL is located ).
The preceding operation instructions are taken from the MySQL documentation center.
Step 2: Connect to MySQL using Python

Using Python to connect to MySQL is simple. Install the MySQLdb package of python. First, install two dependencies:

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 run the following command in the command line or IPython notebook:

shell> python>>> import MySQLdb

Create a connection to the world Database in MySQL:

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

Cursor is the object used to create a MySQL request.

>>> cursor = conn.cursor()

We will execute the query in the Country table.
Step 2: execute MySQL Query in Python

The cursor object uses the MySQL Query string to perform the query. a tuples containing multiple tuples are returned. each row corresponds to one tuple. If you are new to MySQL syntax and commands, the online MySQL Reference Manual is a good learning resource.

>>> cursor.execute('select Name, Continent, Population, LifeExpectancy, GNP from Country');>>> rows = cursor.fetchall()

Rows, that is, the query result, is a tuple containing multiple tuples, as shown below:

Using Pandas DataFrame to process each row is more convenient than using a tuples containing tuples. The following Python code snippet converts all rows to a DataFrame instance:

>>> 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]);

For the complete code, see IPython notebook.
Step 2: Use Plotly to plot MySQL data

Currently, MySQL data is stored in Pandas DataFrame for easy plotting. The following code is used to draw a map of the national GNP (GDP) VS average life cycle. the country name is displayed when you hover the cursor over it. Make sure that you have downloaded the Python library of Plotly. 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 figure shows the result of iframe embedding:

Using the bubble chart tutorial in the Python User Guide of Plotly, we can use the same MySQL data to create a bubble chart. the bubble size indicates the population, and the bubble color indicates different continents, hover the mouse over the country name. The following shows a bubble chart embedded as an iframe.

Create this chart and all the python code in this blog can be copied from this IPython notebook.