Using Python to draw MySQL data graph to realize data visualization _python

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

Consider using plotly in your company? You can 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, as detailed in Wikipedia)

Note the operating system: Although Windows or Mac users can follow this article, this article assumes you are using an Ubuntu system (Ubuntu Desktop version 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 the Ubuntu Desktop version on it. You can also buy a cheap laptop or server that comes with the Ubuntu Desktop version/server version 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 problems or are stuck, you can send mail to feedback@plot.ly, or you can comment below this article, or @plotlygraphs on tweeter.
Step 1th: Make sure MySQL is installed and running

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

If MySQL is not installed, don't be disappointed. Download and install only one line of commands in Ubuntu:

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

The installation process will allow you to enter a password. After installation, you can type the following command into the MySQL console at the end:

shell> sudo mysql-uroot-p

Enter the "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 at the MySQL Documentation Center. You can also use the wget download at 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 World.sql file to create the world database using the following MySQL command:

Mysql> CREATE DATABASE World;
mysql> use world;
Mysql> Source/home/ubuntu/world.sql;

(in the source command above, be sure to change the path to your own World.sql directory).
The above instructions are excerpted from the MySQL Documentation Center.
Step 2nd: Use Python to connect to MySQL

Using Python to connect to MySQL is simple. The key is to install the Python mysqldb package. First you need to install two dependencies:

shell> sudo apt-get install python-dev
shell> 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")

Cursor is the object used to create a 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 that contains multiple tuples-one tuple for each row. If you have just contacted MySQL syntax and commands, the online MySQL reference manual is a very good learning resource.

>>> cursor.execute (' Select Name, Continent, Population, Lifeexpectancy, GNP from Country ');
>>> rows = Cursor.fetchall ()

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

Using Pandas's dataframe to process each row is easier than using a tuple that contains tuples. The following Python code fragment converts all rows into 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: ' Populatio N ', 3: ' Lifeexpectancy ', 4: ' GNP '}, inplace=true);
>>> df = Df.sort ([' lifeexpectancy '], ascending=[1]);

The complete code can see Ipython notebook
4th step: Use plotly to draw MySQL data

The MySQL data is now stored in Pandas's dataframe and can be easily plotted. The following code is used to draw the national GNP (GNP) vs average life of the graph, the mouse hover point will display the country name. Make sure you have downloaded the plotly Python library. If not, you can refer to its introductory guide.

Import plotly.plotly as py
from PLOTLY.GRAPH_OBJS import *
 
Trace1 = scatter (
   x=df[' lifeexpectancy '],
   y =df[' GNP '],
   text=country_names,
   mode= ' markers '
)
layout = layout (Xaxis=xaxis
   ' life Expectancy '),
   yaxis=yaxis (type= ' log ', title= ' GNP ')
)
data = data ([Trace1])
fig = Figure (data= Data, Layout=layout)
py.iplot (Fig, filename= ' World of GNP vs life expectancy ')

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

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

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