sample data for http://www.tsuyukimakoto.com/blog/2013/08/24/python_pandas_and_sql/

sample.py

import pandas as pd
from pandas.io import sql
import numpy as np
import pylab as plt
import pymysql

# i use local ubuntu box 
con = pymysql.connect(host='192.168.215.128', user='panda', passwd='weakpassword', db='pandasample')

df = sql.frame_query('''select sbj.title as title, stdnt.gender as gender, scr.point as point
                        from subject sbj, student stdnt, scores scr
                        where sbj.id = scr.subject_id and scr.student_id = stdnt.id;''', con)

# devide DataFrame by gender.
men = df[df['gender'] == 1]
women = df[df['gender'] == 2]

# output boxplot
plt.xlabel("Gender")
plt.ylabel("Score")
ax = plt.gca()
plt.setp(ax, xticklabels=["Men", "Women"])
plt.boxplot([men['point'], women['point']])

# devide devided DataFrame by subject.
men_sience = men[men["title"] == 'science']
women_sience = women[women["title"] == 'science']

# output boxplot
plt.xlabel("Gender")
plt.ylabel("Score")
ax = plt.gca()
plt.setp(ax, xticklabels=["Men", "Women"])
plt.boxplot([men_sience['point'], women_sience['point']])

#
plt.xlabel("Count")
plt.ylabel("Point")
plt.plot(men["point"])
plt.plot(women["point"])
plt.legend(['Men', 'Women'])

#
plt.xlabel("Point")
plt.ylabel("Count")
plt.hist(men["point"], color="blue")
plt.hist(women["point"], color="red")
plt.legend(['Men', 'Women'])

sample_data.sql

-- insert data
start transaction;
INSERT INTO `student`(name, gender) values ('D00', 2);
INSERT INTO `student`(name, gender) values ('O01', 2);
INSERT INTO `student`(name, gender) values ('N02', 1);
INSERT INTO `student`(name, gender) values ('D03', 2);
INSERT INTO `student`(name, gender) values ('M04', 1);
INSERT INTO `student`(name, gender) values ('U05', 1);
INSERT INTO `student`(name, gender) values ('Q06', 1);
INSERT INTO `student`(name, gender) values ('M07', 1);
INSERT INTO `student`(name, gender) values ('S08', 1);
INSERT INTO `student`(name, gender) values ('M09', 1);
INSERT INTO `student`(name, gender) values ('L10', 1);
INSERT INTO `student`(name, gender) values ('F11', 2);
INSERT INTO `student`(name, gender) values ('D12', 2);
INSERT INTO `student`(name, gender) values ('Q13', 2);
INSERT INTO `student`(name, gender) values ('L14', 1);
INSERT INTO `student`(name, gender) values ('Y15', 1);
INSERT INTO `student`(name, gender) values ('P16', 2);
INSERT INTO `student`(name, gender) values ('K17', 2);
INSERT INTO `student`(name, gender) values ('W18', 1);
INSERT INTO `student`(name, gender) values ('A19', 2);
INSERT INTO `student`(name, gender) values ('Q20', 2);
INSERT INTO `student`(name, gender) values ('T21', 1);
INSERT INTO `student`(name, gender) values ('G22', 2);
INSERT INTO `student`(name, gender) values ('J23', 1);
INSERT INTO `student`(name, gender) values ('Y24', 2);
INSERT INTO `student`(name, gender) values ('V25', 1);
INSERT INTO `student`(name, gender) values ('X26', 2);
INSERT INTO `student`(name, gender) values ('I27', 2);
INSERT INTO `student`(name, gender) values ('Z28', 2);
INSERT INTO `student`(name, gender) values ('L29', 1);
INSERT INTO `student`(name, gender) values ('V30', 2);
INSERT INTO `student`(name, gender) values ('I31', 1);
INSERT INTO `student`(name, gender) values ('R32', 2);
INSERT INTO `student`(name, gender) values ('L33', 1);
INSERT INTO `student`(name, gender) values ('Y34', 2);
INSERT INTO `student`(name, gender) values ('U35', 2);
INSERT INTO `student`(name, gender) values ('A36', 1);
INSERT INTO `student`(name, gender) values ('V37', 1);
INSERT INTO `student`(name, gender) values ('D38', 1);
INSERT INTO `student`(name, gender) values ('D39', 1);
INSERT INTO `student`(name, gender) values ('J40', 2);
INSERT INTO `student`(name, gender) values ('R41', 1);
INSERT INTO `student`(name, gender) values ('D42', 1);
INSERT INTO `student`(name, gender) values ('I43', 2);
INSERT INTO `student`(name, gender) values ('P44', 2);
INSERT INTO `student`(name, gender) values ('O45', 2);

INSERT INTO `subject`(title) values ('science'), ('maths');

INSERT INTO `scores`(student_id, subject_id, point) values (1, 1, 64);
INSERT INTO `scores`(student_id, subject_id, point) values (2, 1, 57);
INSERT INTO `scores`(student_id, subject_id, point) values (3, 1, 66);
INSERT INTO `scores`(student_id, subject_id, point) values (4, 1, 68);
INSERT INTO `scores`(student_id, subject_id, point) values (5, 1, 67);
INSERT INTO `scores`(student_id, subject_id, point) values (6, 1, 66);
INSERT INTO `scores`(student_id, subject_id, point) values (7, 1, 58);
INSERT INTO `scores`(student_id, subject_id, point) values (8, 1, 64);
INSERT INTO `scores`(student_id, subject_id, point) values (9, 1, 52);
INSERT INTO `scores`(student_id, subject_id, point) values (10, 1, 88);
INSERT INTO `scores`(student_id, subject_id, point) values (11, 1, 82);
INSERT INTO `scores`(student_id, subject_id, point) values (12, 1, 64);
INSERT INTO `scores`(student_id, subject_id, point) values (13, 1, 86);
INSERT INTO `scores`(student_id, subject_id, point) values (14, 1, 88);
INSERT INTO `scores`(student_id, subject_id, point) values (15, 1, 69);
INSERT INTO `scores`(student_id, subject_id, point) values (16, 1, 50);
INSERT INTO `scores`(student_id, subject_id, point) values (17, 1, 88);
INSERT INTO `scores`(student_id, subject_id, point) values (18, 1, 97);
INSERT INTO `scores`(student_id, subject_id, point) values (19, 1, 77);
INSERT INTO `scores`(student_id, subject_id, point) values (20, 1, 82);
INSERT INTO `scores`(student_id, subject_id, point) values (21, 1, 53);
INSERT INTO `scores`(student_id, subject_id, point) values (22, 1, 67);
INSERT INTO `scores`(student_id, subject_id, point) values (23, 1, 79);
INSERT INTO `scores`(student_id, subject_id, point) values (24, 1, 73);
INSERT INTO `scores`(student_id, subject_id, point) values (25, 1, 63);
INSERT INTO `scores`(student_id, subject_id, point) values (26, 1, 97);
INSERT INTO `scores`(student_id, subject_id, point) values (27, 1, 52);
INSERT INTO `scores`(student_id, subject_id, point) values (28, 1, 62);
INSERT INTO `scores`(student_id, subject_id, point) values (29, 1, 75);
INSERT INTO `scores`(student_id, subject_id, point) values (30, 1, 74);
INSERT INTO `scores`(student_id, subject_id, point) values (31, 1, 95);
INSERT INTO `scores`(student_id, subject_id, point) values (32, 1, 97);
INSERT INTO `scores`(student_id, subject_id, point) values (33, 1, 63);
INSERT INTO `scores`(student_id, subject_id, point) values (34, 1, 70);
INSERT INTO `scores`(student_id, subject_id, point) values (35, 1, 87);
INSERT INTO `scores`(student_id, subject_id, point) values (36, 1, 71);
INSERT INTO `scores`(student_id, subject_id, point) values (37, 1, 92);
INSERT INTO `scores`(student_id, subject_id, point) values (38, 1, 77);
INSERT INTO `scores`(student_id, subject_id, point) values (39, 1, 84);
INSERT INTO `scores`(student_id, subject_id, point) values (40, 1, 86);
INSERT INTO `scores`(student_id, subject_id, point) values (41, 1, 57);
INSERT INTO `scores`(student_id, subject_id, point) values (42, 1, 56);
INSERT INTO `scores`(student_id, subject_id, point) values (43, 1, 92);
INSERT INTO `scores`(student_id, subject_id, point) values (44, 1, 58);
INSERT INTO `scores`(student_id, subject_id, point) values (45, 1, 54);
INSERT INTO `scores`(student_id, subject_id, point) values (46, 1, 86);
INSERT INTO `scores`(student_id, subject_id, point) values (1, 2, 59);
INSERT INTO `scores`(student_id, subject_id, point) values (2, 2, 76);
INSERT INTO `scores`(student_id, subject_id, point) values (3, 2, 70);
INSERT INTO `scores`(student_id, subject_id, point) values (4, 2, 94);
INSERT INTO `scores`(student_id, subject_id, point) values (5, 2, 85);
INSERT INTO `scores`(student_id, subject_id, point) values (6, 2, 67);
INSERT INTO `scores`(student_id, subject_id, point) values (7, 2, 83);
INSERT INTO `scores`(student_id, subject_id, point) values (8, 2, 58);
INSERT INTO `scores`(student_id, subject_id, point) values (9, 2, 89);
INSERT INTO `scores`(student_id, subject_id, point) values (10, 2, 73);
INSERT INTO `scores`(student_id, subject_id, point) values (11, 2, 93);
INSERT INTO `scores`(student_id, subject_id, point) values (12, 2, 93);
INSERT INTO `scores`(student_id, subject_id, point) values (13, 2, 93);
INSERT INTO `scores`(student_id, subject_id, point) values (14, 2, 99);
INSERT INTO `scores`(student_id, subject_id, point) values (15, 2, 80);
INSERT INTO `scores`(student_id, subject_id, point) values (16, 2, 65);
INSERT INTO `scores`(student_id, subject_id, point) values (17, 2, 53);
INSERT INTO `scores`(student_id, subject_id, point) values (18, 2, 63);
INSERT INTO `scores`(student_id, subject_id, point) values (19, 2, 78);
INSERT INTO `scores`(student_id, subject_id, point) values (20, 2, 82);
INSERT INTO `scores`(student_id, subject_id, point) values (21, 2, 60);
INSERT INTO `scores`(student_id, subject_id, point) values (22, 2, 43);
INSERT INTO `scores`(student_id, subject_id, point) values (23, 2, 44);
INSERT INTO `scores`(student_id, subject_id, point) values (24, 2, 99);
INSERT INTO `scores`(student_id, subject_id, point) values (25, 2, 49);
INSERT INTO `scores`(student_id, subject_id, point) values (26, 2, 81);
INSERT INTO `scores`(student_id, subject_id, point) values (27, 2, 47);
INSERT INTO `scores`(student_id, subject_id, point) values (28, 2, 50);
INSERT INTO `scores`(student_id, subject_id, point) values (29, 2, 60);
INSERT INTO `scores`(student_id, subject_id, point) values (30, 2, 63);
INSERT INTO `scores`(student_id, subject_id, point) values (31, 2, 48);
INSERT INTO `scores`(student_id, subject_id, point) values (32, 2, 92);
INSERT INTO `scores`(student_id, subject_id, point) values (33, 2, 49);
INSERT INTO `scores`(student_id, subject_id, point) values (34, 2, 94);
INSERT INTO `scores`(student_id, subject_id, point) values (35, 2, 96);
INSERT INTO `scores`(student_id, subject_id, point) values (36, 2, 90);
INSERT INTO `scores`(student_id, subject_id, point) values (37, 2, 67);
INSERT INTO `scores`(student_id, subject_id, point) values (38, 2, 72);
INSERT INTO `scores`(student_id, subject_id, point) values (39, 2, 72);
INSERT INTO `scores`(student_id, subject_id, point) values (40, 2, 78);
INSERT INTO `scores`(student_id, subject_id, point) values (41, 2, 47);
INSERT INTO `scores`(student_id, subject_id, point) values (42, 2, 44);
INSERT INTO `scores`(student_id, subject_id, point) values (43, 2, 50);
INSERT INTO `scores`(student_id, subject_id, point) values (44, 2, 61);
INSERT INTO `scores`(student_id, subject_id, point) values (45, 2, 66);
INSERT INTO `scores`(student_id, subject_id, point) values (46, 2, 86);
commit;

scheme.sql

-- for mysql5.5
-- http://www.tsuyukimakoto.com/blog/2013/08/24/python_pandas_and_sql/

-- create database and grant user
CREATE DATABASE `pandasample`;
GRANT ALL ON `pandasample`.* TO `panda`@`%` IDENTIFIED BY 'weakpassword'; -- dangerous
GRANT ALL ON `pandasample`.* TO `panda`@`localhost` IDENTIFIED BY 'weakpassword';

-- create table
CREATE TABLE `student`(
  `id` integer AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `gender` tinyint default 1,
  PRIMARY KEY (`id`)
);
CREATE TABLE `subject`(
  `id` integer AUTO_INCREMENT,
  `title` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `scores`(
  `id` integer AUTO_INCREMENT,
  `student_id` integer,
  `subject_id` integer,
  `point` integer,
  PRIMARY KEY (`id`)
);