{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "**Table of Contents**\n", "\n", "
\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Demo based on these:\n", "\n", "- https://plot.ly/python/graph-data-from-mysql-database-in-python/\n", "- http://moderndata.plot.ly/graph-data-from-mysql-database-in-python/\n", "- http://moderndata.plot.ly/widgets-in-ipython-notebook-and-plotly/\n", "\n" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "# First download the world database\n", "http://dev.mysql.com/doc/index-other.html" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "--2016-09-22 13:08:55-- http://downloads.mysql.com/docs/world.sql.gz\n", "Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14\n", "Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:80... connected.\n", "HTTP request sent, awaiting response... 200 OK\n", "Length: 92094 (90K) [application/x-gzip]\n", "Saving to: ‘world.sql.gz’\n", "\n", " 0K .......... .......... .......... .......... .......... 55% 205K 0s\n", " 50K .......... .......... .......... ......... 100% 490K=0.3s\n", "\n", "2016-09-22 13:08:56 (277 KB/s) - ‘world.sql.gz’ saved [92094/92094]\n", "\n" ] } ], "source": [ "%%sh\n", "wget http://downloads.mysql.com/docs/world.sql.gz" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "-rw-r--r-- 1 takanori takanori 90K Sep 22 12:27 world.sql.gz\n" ] } ], "source": [ "%%sh\n", "# aboute 90KB\n", "ls -lh world*" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": true }, "outputs": [], "source": [ "%%sh\n", "# unzip\n", "gunzip world.sql.gz" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "-rw-r--r-- 1 takanori takanori 389K Sep 22 12:27 world.sql\r\n" ] } ], "source": [ "# now we have world.sql file\n", "ls -lh world*" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "-- MySQL dump 10.13 Distrib 5.1.51, for pc-linux-gnu (i686)\n", "--\n", "-- Host: 127.0.0.1 Database: world\n", "-- ------------------------------------------------------\n", "-- Server version 5.1.51-debug-log\n", "\n", "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n", "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n", "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n", "/*!40101 SET NAMES latin1 */;\n", "/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;\n", "/*!40103 SET TIME_ZONE='+00:00' */;\n", "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n", "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n", "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n", "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\n", "\n", "DROP SCHEMA IF EXISTS world;\n", "CREATE SCHEMA world;\n", "USE world;\n", "SET AUTOCOMMIT=0;\n", "\n", "--\n", "-- Table structure for table `city`\n", "--\n", "\n", "DROP TABLE IF EXISTS `city`;\n", "/*!40101 SET @saved_cs_client = @@character_set_client */;\n", "/*!40101 SET character_set_client = utf8 */;\n", "CREATE TABLE `city` (\n" ] } ], "source": [ "%%sh\n", "head -30 world.sql" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Install database in mysql\n", "https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html\n", "\n", "```sql\n", "$ mysql -u root -p\n", "mysql> source world.sql;\n", "mysql> show databases;\n", "+--------------------+\n", "| Database |\n", "+--------------------+\n", "| information_schema |\n", "| TUTORIALS |\n", "| mysql |\n", "| performance_schema |\n", "| tutorial |\n", "| world |\n", "+--------------------+\n", "6 rows in set (0.01 sec)\n", "```" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "show databases;\n", "system ls\n", "\\#\n", "source world.sql;\n" ] } ], "source": [ "%%sh\n", "# sourced the sql file to create database\n", "\n", "tail -4 ~/.mysql_history" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## For this demo, create user with all privileges to this `world` database" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```bash\n", "mysql> CREATE USER 'tak'@'localhost' IDENTIFIED BY 'nori';\n", "Query OK, 0 rows affected (0.16 sec)\n", "\n", "mysql> USE world;\n", "Reading table information for completion of table and column names\n", "You can turn off this feature to get a quicker startup with -A\n", "\n", "Database changed\n", "mysql> GRANT ALL ON world.* TO 'tak'@'localhost';\n", "Query OK, 0 rows affected (0.02 sec)\n", "```\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Alright, we are in business. Let's analyze this database!" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import MySQLdb\n", "import pandas as pd\n", "import plotly.plotly as py\n", "from plotly.graph_objs import *" ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# use credential i create above to connect to the world-database\n", "conn = MySQLdb.connect(host=\"localhost\", user=\"tak\", passwd=\"nori\", db=\"world\")\n", "cursor = conn.cursor()" ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(('city',), ('country',), ('countrylanguage',))" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# show names of the table in the database\n", "cursor.execute('show tables')\n", "cursor.fetchall()" ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "(('Code', 'char(3)', 'NO', 'PRI', '', ''),\n", " ('Name', 'char(52)', 'NO', '', '', ''),\n", " ('Continent',\n", " \"enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')\",\n", " 'NO',\n", " '',\n", " 'Asia',\n", " ''),\n", " ('Region', 'char(26)', 'NO', '', '', ''),\n", " ('SurfaceArea', 'float(10,2)', 'NO', '', '0.00', ''),\n", " ('IndepYear', 'smallint(6)', 'YES', '', None, ''),\n", " ('Population', 'int(11)', 'NO', '', '0', ''),\n", " ('LifeExpectancy', 'float(3,1)', 'YES', '', None, ''),\n", " ('GNP', 'float(10,2)', 'YES', '', None, ''),\n", " ('GNPOld', 'float(10,2)', 'YES', '', None, ''),\n", " ('LocalName', 'char(45)', 'NO', '', '', ''),\n", " ('GovernmentForm', 'char(45)', 'NO', '', '', ''),\n", " ('HeadOfState', 'char(60)', 'YES', '', None, ''),\n", " ('Capital', 'int(11)', 'YES', '', None, ''),\n", " ('Code2', 'char(2)', 'NO', '', '', ''))" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# ugly format, but print schema for the country table\n", "cursor.execute('describe country')\n", "cursor.fetchall()" ] }, { "cell_type": "code", "execution_count": 49, "metadata": { "collapsed": false }, "outputs": [ { "ename": "ProgrammingError", "evalue": "(1146, \"Table 'world.Country' doesn't exist\")", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mProgrammingError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\n", " | Name | \n", "Continent | \n", "Population | \n", "LifeExpectancy | \n", "GNP | \n", "
---|---|---|---|---|---|
237 | \n", "Zambia | \n", "Africa | \n", "9169000 | \n", "37.2 | \n", "3377.0 | \n", "
143 | \n", "Mozambique | \n", "Africa | \n", "19680000 | \n", "37.5 | \n", "2891.0 | \n", "
148 | \n", "Malawi | \n", "Africa | \n", "10925000 | \n", "37.6 | \n", "1687.0 | \n", "
238 | \n", "Zimbabwe | \n", "Africa | \n", "11669000 | \n", "37.8 | \n", "5951.0 | \n", "
2 | \n", "Angola | \n", "Africa | \n", "12878000 | \n", "38.3 | \n", "6648.0 | \n", "