All of the Python code for this tutorial can be found online at theIPython notebookGet in.
Considering using Plotly in your company, take a look at Plotly's on-premisesEnterprise Edition. (Note: On-premises means that the software is running in the workplace or within the company, as detailed inWikipedia (online encyclopedia)）
Note about operating systems: although Windows or Mac users can follow along with this article, this article assumes you are using an Ubuntu system (Ubuntu Desktop or Ubuntu Server). If you don't have Ubuntu Server, you can set up a cloud platform through Amazon's web service (read thistutorials(the first half of the program). If you're using a Mac, we recommend purchasing and downloading VMware Fusion and installing the Ubuntu desktop version on it. You can also buy an inexpensive laptop or server with Ubuntu desktop/server edition pre-installed through Zareason.
Reading and mapping MySQL data using Python is easy, and all the tools you need are available for free download. This article will show how to do it. If you run into problems or get stuck, you can email feedback@, comment below this article, or @plotlygraphs on tweeter.
Step 1: Ensure MySQL is installed and running
First, you need to have a computer or server with MySQL installed. You can check if MySQL is installed by opening the console and typing "mysql". If you get an error that MySQL cannot connect, it means that MySQL is installed but not running. At the command line or "Terminal", try typing sudo /etc/mysql start and press enter to start MySQL.
Don't be disappointed if MySQL is not installed. Downloading and installing it in Ubuntu is a one-line command:
shell> sudo apt-get install mysql-server --fix-missing
The installation process will ask you to enter a password. After the installation is complete, you can enter the MySQL console by typing the following command in a terminal:
shell> sudo mysql -uroot -p
Type "exit" to exit the MySQL console.
This tutorial uses the classic MySQL "world" sample database. If you want to follow along, you can find the steps in theMySQL Documentation CenterDownload the world database. You can also download it from the command line using wget:
shell> wget /docs/
Then unzip the file:
shell> unzip
(If unzip is not installed, type sudo apt-get install unzip to install it)
Now you need to import the world database into MySQL and start the MySQL console:
shell> sudo mysql -uroot -p
After entering the console, create the world database using the file with the following MySQL command:
mysql> CREATE DATABASE world; mysql> USE world; mysql> SOURCE /home/ubuntu/;
(In the SOURCE command above, make sure you change the path to your own directory).
The above operating instructions are taken fromMySQL Documentation Center。
Step 2: Connecting to MySQL using Python
Connecting to MySQL using Python is easy. The key is to install the MySQLdb package for Python. First you need to install two dependencies:
shell> sudo apt-get install python-dev shell> sudo apt-get install libmysqlclient-dev
Then install the MySQLdb package for Python:
shell> sudo pip install MySQL-python
Now, start Python and import MySQLdb. you can do this on the command line or in IPython notebook:
shell> python >>> import MySQLdb
Creates a connection to the world database in MySQL:
>>> conn = (host="localhost", user="root", passwd="XXXX", db="world")
The cursor is the object used to create the MySQL request.
>>> cursor = ()
We will execute the query in the Country table.
Step 3: Execute MySQL Queries in Python
The cursor object executes a query using the MySQL query string, returning a tuple containing multiple tuples - one for each row. If you're new to MySQL syntax and commands, the onlineMySQL Reference ManualIt's a great resource for learning.
>>> ('select Name, Continent, Population, LifeExpectancy, GNP from Country');
>>> rows = ()
rows, the result of the query, is a tuple containing multiple tuples, like the following:
It's easier to use Pandas' DataFrame for each row than to use a tuple containing tuples. The following Python code snippet transforms all rows into DataFrame instances:
>>> import pandas as pd
>>> df = ( [[ij for ij in i] for i in rows] )
>>> (columns={0: 'Name', 1: 'Continent', 2: 'Population', 3: 'LifeExpectancy', 4:'GNP'}, inplace=True);
>>> df = (['LifeExpectancy'], ascending=[1]);
The full code can be found inIPython notebook
Step 4: Plotting MySQL Data with Plotly
Now that the MySQL data is stored in a DataFrame in Pandas, it can be easily plotted. The code below is used to plot country GNP (Gross National Product) vs Average Life Expectancy, with the country name displayed at the point where the mouse hovers. Make sure you have downloaded the Python library for Plotly. If not, you can refer to itsGetting Started。
import as py from 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) (fig, filename='world GNP vs life expectancy')
The full code is in thisIPython notebookin. Here's what you'll find as aiframeEmbedded result graph:
Utilizing Plotly'sPython User's GuideIn the Bubble Map tutorial, we can use the same MySQL data to draw a bubble map, the size of the bubble indicates the population, the color of the bubble represents the different continents, and the name of the country is displayed on mouse hover. The following is shown as aiframeEmbedded bubble chart.
Creating this chart and all the python code in this blog can be done from thisIPython notebookCopy in.