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 allselect 2,'数学'union allselect 3,'外语'union allselect 4,'物理'union allselect 5,'化学'union allselect 6,'生物';insert into student(id,name)select 1,'张超'union allselect 2,'李佳'union allselect 3,'王子'union allselect 4,'何家传'union allselect 5,'杨明'union allselect 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 MySQLdbimport warningsimport datetimeimport timewarnings.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, Noneconn1, 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_timesql_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语句处理。