Mysql分表数据通过Python进行汇总统计



接上文：【Mysql分表数据通过Shell进行导出与统计】---> http://blog.csdn.net/babyfish13/article/details/52777780
1、每个分表数据量及总数据量统计
mysql_much_tab_data_static1.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config = {
    'host': 'mysql-server-ip',
    'user': 'mysql-server-user',
    'passwd': 'mysql-server-passwd',
    'port': 3306,
    'db': 'jellyfish_server'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

sql_text = "select table_name from information_schema.tables where table_schema='jellyfish_server' and table_name like 'room_subscriber_roomid_%';"
curr.execute(sql_text)
tab_list = curr.fetchall()

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

    sql_text = "select count(*) from jellyfish_server.%s where substr(updated_time,1,10) ='%s' and state='0';" % (tab_name,yesterday)
    curr.execute(sql_text)
    cnts = curr.fetchall()
    for cnt in cnts:
        cnt = cnt[0]
        print  (tab_name + '\'s data cnt is : ' + str(cnt) )
        cnt_sum = cnt_sum + cnt
        print cnt_sum

curr.close()
conn.close()

2、各分表按关键字段分别汇总（相当于sql:group by sum count）
mysql_much_tab_data_static2.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil
from itertools import groupby
from operator import itemgetter

warnings.filterwarnings("ignore")

db_config = {
    'host': 'mysql-server-ip',
    'user': 'mysql-server-user',
    'passwd': 'mysql-server-passwd',
    'port': 3306,
    'db': 'jellyfish_server'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

sql_text = "select table_name from information_schema.tables where table_schema='jellyfish_server' and table_name like 'room_subscriber_roomid_%';"
curr.execute(sql_text)
tab_list = curr.fetchall()

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

    sql_text = "select room_id,uid,state,created_time,updated_time from jellyfish_server.%s where substr(updated_time,1,10) ='%s' and state='0';" % (tab_name,yesterday)
    curr.execute(sql_text)
    col_datas = curr.fetchall()

    #col_datas.sort(key = itemgetter(0,2))
    #sorted(col_datas,key = itemgetter(0,2))
#    group_col_datas = groupby(col_datas,itemgetter(0,2))
    group_col_datas = groupby(sorted(col_datas,key = itemgetter(0,2)),itemgetter(0,2))   

    for key,item in group_col_datas:
        i=0
        j=0
        for jtem in item:
            i+=float(jtem[1])
            j+=1
        print key,i,j

'''
    for col_data in col_datas:
        room_id            = col_data[0] 
        uid                = col_data[1]
        state              = col_data[2]
        created_time       = col_data[3]
        updated_time       = col_data[4]
        print  (room_id + uid )
'''
curr.close()
conn.close()

3、各分表按关键字段总体汇总（相当于sql:group by sum count） 
mysql_much_tab_data_static3.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil
from itertools import groupby
from operator import itemgetter

warnings.filterwarnings("ignore")

db_config = {
    'host': 'mysql-server-ip',
    'user': 'mysql-server-user',
    'passwd': 'mysql-server-passwd',
    'port': 3306,
    'db': 'jellyfish_server'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

sql_text = "select table_name from information_schema.tables where table_schema='jellyfish_server' and table_name like 'room_subscriber_roomid_9%';"
curr.execute(sql_text)
tab_list = curr.fetchall()

col_datas_union = ''
for tab_name in tab_list:
    tab_name = tab_name[0]

    sql_text = "select room_id,uid,state,created_time,updated_time from jellyfish_server.%s where substr(updated_time,1,10) ='%s' and state='0';" % (tab_name,yesterday)
    curr.execute(sql_text)
    col_datas = curr.fetchall()
    col_datas_union = list(set(col_datas_union).union(set(col_datas)))
#print col_datas_union

    #col_datas.sort(key = itemgetter(0,2))
    #sorted(col_datas,key = itemgetter(0,2))
#    group_col_datas = groupby(col_datas,itemgetter(0,2))
group_col_datas = groupby(sorted(col_datas_union,key = itemgetter(1,2)),itemgetter(1,2))   
#col_data_list = [item for item in group_col_datas]
    #print col_data_list
    #print dir(col_data_list)
    #print type(col_data_list)
    

for key,item in group_col_datas:
#for key,item in col_data_list:
    i=0
    j=0
    for jtem in item:
        i+=float(jtem[0])
        j+=1
    print key,i,j

'''
    for col_data in col_datas:
        room_id            = col_data[0] 
        uid                = col_data[1]
        state              = col_data[2]
        created_time       = col_data[3]
        updated_time       = col_data[4]
        print  (room_id + uid )
'''
curr.close()
conn.close()

附：数据验证sql
r3.dataver

select 'room_subscriber_roomid_90' tab_name, a.* from room_subscriber_roomid_90 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_91' tab_name, a.* from room_subscriber_roomid_91 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_92' tab_name, a.* from room_subscriber_roomid_92 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_93' tab_name, a.* from room_subscriber_roomid_93 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_94' tab_name, a.* from room_subscriber_roomid_94 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_95' tab_name, a.* from room_subscriber_roomid_95 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_96' tab_name, a.* from room_subscriber_roomid_96 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_97' tab_name, a.* from room_subscriber_roomid_97 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_98' tab_name, a.* from room_subscriber_roomid_98 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_99' tab_name, a.* from room_subscriber_roomid_99 a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0 union all
select 'room_subscriber_roomid_9 ' tab_name, a.* from room_subscriber_roomid_9  a where substr(updated_time,1,10) ='2016-11-06' and uid='1133980217' and state=0;