SplitHisTable

Split.py

__author__ = 'hulkwo'

import MySQLdb
import MySQLdb.cursors
import traceback
from collections import defaultdict

conn = MySQLdb.connect(host="192.168.1.218",
                       user="mysql",
                       passwd="homewell",
                       db="udcweb",
                       charset="gbk")


def create_history_table(conn, count):
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    for i in range(1, count):
        try:
            create_table_sql = ("CREATE TABLE `historydata%d` ("
                                "`id` bigint(8) NOT NULL AUTO_INCREMENT,"
                                "`nodeid` varchar(48) NOT NULL,"
                                "`nodename` varchar(256) NOT NULL,"
                                "`position` varchar(32) NOT NULL,"
                                "`sensorno` int(4) NOT NULL,"
                                "`sensorname` varchar(256) NOT NULL,"
                                "`orgid` int(4) NOT NULL,"
                                "`orgname` varchar(256) DEFAULT NULL,"
                                "`entityid` int(4) DEFAULT NULL,"
                                "`entityname` varchar(256) DEFAULT NULL,"
                                "`type` smallint(2) NOT NULL,"
                                "`data` float(16,6) NOT NULL,"
                                "`reservedata` float(16,6) NOT NULL,"
                                "`time` bigint(8) NOT NULL,"
                                "`isAlarm` tinyint(1) default '0',"
                                "`alarmlevel` int(4) DEFAULT NULL,"
                                "`isDealed` tinyint(1) DEFAULT '0',"
                                "`dealUserId` int(4) DEFAULT NULL,"
                                "`dealTime` bigint(8) DEFAULT NULL,"
                                "`message` varchar(4096) DEFAULT '',"
                                "PRIMARY KEY (`id`),"
                                "KEY `idx_time` (`time`),"
                                "KEY `idx_entityid_time_alarm_level_deal` (`entityid`,`time`,`isAlarm`,`alarmlevel`,`isDealed`),"
                                "KEY `idx_nodeid_sensorno_time_alarm_level_deal` (`nodeid`,`sensorno`,`time`,`isAlarm`,`alarmlevel`,`isDealed`),"
                                "KEY `idx_orgid_time_alarm_level_deal` (`orgid`,`time`,`isAlarm`,`alarmlevel`,`isDealed`)"
                                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" % i)
            cursor.execute(create_table_sql)
            conn.commit()
        except:
            continue


def xstr(str):
    if str is None:
        return "null"
    else:
        return str


def truncate_table(conn, count):
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)

    for i in range(1, count):
        histable = "historydata%d" % i
        cursor.execute("truncate table %s" % histable)
        conn.commit()


def split_data(conn):
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    start = 139666510
    data_dict = defaultdict(list)
    round = 1
    data_count_per_query = 20000
    while True:
        try:
            print "Round:%d" % round
            cursor.execute("select * from historydata3month where id > %d order by id asc limit 0, %d" % (start, data_count_per_query))
            rows = cursor.fetchall()
            if rows is not None:
                for row in rows:
                    histable_index = row['orgid'] % 50
                    if histable_index == 0:
                        histable_index = 50
                    data_dict[histable_index].append(row)

                for key in data_dict.keys():
                    if len(data_dict[key]) > 0:
                        histable = "historydata%d" % key
                        sql = (
                            "insert into %s (nodeid, nodename, position, sensorno, sensorname, orgid, orgname, entityid, "
                            "entityname, type, data, reservedata, time, isAlarm, alarmlevel, isDealed, dealUserId, dealTime, "
                            "message) values " % histable)
                        sql_data = ""
                        for data in data_dict[key]:
                            sql_data += (
                                '("%s", "%s", "%s", %s, "%s", %s, "%s", %s, "%s", %s, %f, %f, %s, %s, %s, %s, %s, %s, "%s"),'
                                % (xstr(data['nodeid']), xstr(data['nodename']), xstr(data['position']),
                                   xstr(data['sensorno']),
                                   xstr(data['sensorname']), xstr(data['orgid']), xstr(data['orgname']),
                                   xstr(data['entityid']),
                                   xstr(data['entityname']), xstr(data['type']), xstr(data['data']),
                                   xstr(data['reservedata']),
                                   xstr(data['time']), xstr(data['isAlarm']), xstr(data['alarmlevel']),
                                   xstr(data['isDealed']),
                                   xstr(data['dealUserId']), xstr(data['dealTime']), xstr(data['message'])))
                        sql = sql + sql_data
                        cursor.execute(sql[:-1])
                        conn.commit()

                if len(rows) != data_count_per_query:
                    break

                data_dict.clear()
                round += 1

                if len(rows) > 0:
                    start = rows[-1]['id']
                    print start

        except Exception, e:
            print e
            print traceback.print_exc()
            continue

truncate_table(conn, 51)
# split_data(conn)