Python进行数据的多表Join关联操作

在[Python进行数据的Join关联操作及从分表取数据一例]--->http://blog.csdn.net/babyfish13/article/details/53411743 一文中，数据的关联操作仅支持从表是key/vaule两列的形式；本文中要实现的关联对主从表的列数都没有限制。
对于精通Java、Python及C等开发语言的用户，数据的关联取数采用本文介绍的for循环if判断的方式只是小儿科；可对于像我这样，强于数据库，初识Python开发的用户来说，具有一定的意义。
1、数据准备

--建表
drop table if exists `student`;
CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=innoDB DEFAULT CHARSET=utf8;

drop table if exists `item`;
CREATE TABLE `item` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=innoDB DEFAULT CHARSET=utf8;

drop table if exists `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `scores` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

--插入数据
insert into item(id,name)
select 1,'语文'
union all
select 2,'数学'
union all
select 3,'外语'
union all
select 4,'物理'
union all
select 5,'化学'
union all
select 6,'生物'
;

insert into student(id,name)
select 1,'张超'
union all
select 2,'李佳'
union all
select 3,'王子'
union all
select 4,'何家传'
union all
select 5,'杨明'
union all
select 6,'李飞'
;

insert into score(student_id,item_id,scores)
select a.id student_id,b.id item_id,ROUND((RAND() *100),2) score from student a ,item b;

数据准备的时候，用到了Mysql取随机数RAND(),Score采用了自增主键。

2、数据处理的Python
/Users/nisj/PycharmProjects/EsDataProc/mysql_submeter_moretab_join.py

# -*- coding=utf-8 -*-
import MySQLdb
import warnings
import datetime
import time

warnings.filterwarnings("ignore")

db_config_src1 = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'test'
}

db_config_src2 = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'test'
}

db_config_tgt = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'timeline'
}

def getDB(host, user, passwd, port, db):
    try:
        conn = MySQLdb.connect(host=host, user=user, passwd=passwd, port=port ,db=db)
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db);

        return conn, curr
    except MySQLdb.Error, e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None


conn1, curr1 = getDB(host=db_config_src1['host'], user=db_config_src1['user'], passwd=db_config_src1['passwd'],port=db_config_src1['port'],db=db_config_src1['db'])
conn2, curr2 = getDB(host=db_config_src2['host'], user=db_config_src2['user'], passwd=db_config_src2['passwd'],port=db_config_src2['port'],db=db_config_src2['db'])

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)
now_time = time.strftime('%Y-%m-%d %X', time.localtime())

print "当前时间是：",now_time

sql_text = "select table_name from information_schema.tables where table_schema='test' and table_name like 'score%';"
curr1.execute(sql_text)
tab_list = curr1.fetchall()

score_datas_union = []
for tab_name in tab_list:
    tab_name = tab_name[0]

    sql_text = "select id,student_id,item_id,scores from test.%s ;" % (tab_name)
    curr1.execute(sql_text)
    score_datas = curr1.fetchall()

    score_datas_union.extend(score_datas)

sql_text = "select id,name from student;"
curr2.execute(sql_text)
student_data = curr2.fetchall()

sql_text = "select id,name from item;"
curr2.execute(sql_text)
item_data = curr2.fetchall()

print "id","姓名","科目",'分数'
for score in score_datas:
    for student in student_data:
        if score[1] == student[0]:
            for item in item_data:
                if score[2] == item[0]:
                    print score[0],student[1],item[1],score[3]

curr1.close()
conn1.close()
curr2.close()
conn2.close()

3、Python脚本说明
3.1、优化了Mysql数据库连接部分
Mysql的连接做了一定程度的优化，简化了代码，实现了重复调用。

3.2、添加了当前时间的打印
now_time = time.strftime('%Y-%m-%d %X', time.localtime())

3.3、多表关联的处理
多表关联采用了for if语句处理。