数据库迁移--字段拆分对比

import pymysql
import decimal
import datetime
import json


class DecimalEncoder(json.JSONEncoder):
    def default(self,obj):
        if isinstance(obj,decimal.Decimal):#decimal类型转换，使其可以转换为json格式数据
            return float(obj)
        elif isinstance(obj,datetime.datetime):#datetime类型转换，使其可以转换为json格式数据
            return obj.__str__()
        return super(DecimalEncoder,self).default(obj)

class RUNSQL(object):
    def run_sql(self):
        new_db=pymysql.connect(host='服务器地址',port=3306,user='用户名',password='密码',db='数据库名称',charset='utf8')
        old_db=pymysql.connect(host='服务器地址',port=3306,user='用户名',password='密码',db='数据库名称',charset='utf8')

        #获取字段属性值
        self.new_cursor1=new_db.cursor()
        self.old_cursor1=old_db.cursor()
        self.new_cursor1.execute("desc order_logistic;")
        self.old_cursor1.execute('desc t_wms2tms_orderflow_temp;')

        #数据对比
        self.new_max_data=new_db.cursor()
        self.old_max_data=old_db.cursor()
        self.new_max_data.execute("SELECT express_no,remark,logistic_status,created_at FROM tms.order_logistic ORDER BY express_no ;")
        self.old_max_data.execute("SELECT mailNo,remark,state,createTime FROM tms_tmp.t_wms2tms_orderflow_temp a JOIN tms.order_id_tmp b ON a.txLogisticId=b.order_no ORDER BY mailNo;")

        #all test
        self.new_all_data=new_db.cursor()
        self.old_all_data=old_db.cursor()
        self.new_all_data.execute("SELECT express_no,remark,logistic_status,created_at FROM tms.order_logistic a JOIN tms.order_id_tmp b ON a.order_id=b.order_id;")
        self.old_all_data.execute("SELECT mailNo,remark,state,createTime FROM tms_tmp.t_wms2tms_orderflow_temp a JOIN tms.order_id_tmp b ON a.txLogisticId=b.order_no;")

        new_db.close()
        old_db.close()


    def sql_test(self):
        '''新旧字段对比'''
        self.run_sql()
        print('字段名称','字段类型','是否为空','主键外键','默认值','附加说明')
        # print(self.new_cursor1.fetchall())
        # print(self.old_cursor1.fetchall())
        new_list=[]
        old_list=[]
        for res_new in self.new_cursor1.fetchall():
            if res_new[0]=='express_no'or res_new[0]=='remark'or res_new[0]=='logistic_status'or res_new[0]=='created_at':
                new_list.append(res_new)
            else:
                pass
        #         print(res_new)
        # print('\n')

        for res_old in self.old_cursor1.fetchall():
            if res_old[0]=='mailNo'or res_old[0]=='remark'or res_old[0]=='state'or res_old[0]=='createTime':
                old_list.append(res_old)
            else:
                pass

        # print(new_list)
        # print(old_list)
        for i in range(len(new_list)):
            if new_list[i][0]=='express_no':
                print(new_list[i])
        for i in range(len(new_list)):
            if old_list[i][0]=='mailNo':
                print(old_list[i])
        print('\n')
        for i in range(len(new_list)):
            if new_list[i][0]=='remark':
                print(new_list[i])
        for i in range(len(new_list)):
            if old_list[i][0]=='remark':
                print(old_list[i])
        print('\n')
        for i in range(len(new_list)):
            if new_list[i][0]=='logistic_status':
                print(new_list[i])
        for i in range(len(new_list)):
            if old_list[i][0]=='state':
                print(old_list[i])
        print('\n')
        for i in range(len(new_list)):
            if new_list[i][0]=='created_at':
                print(new_list[i])
        for i in range(len(new_list)):
            if old_list[i][0]=='createTime':
                print(old_list[i])
        print('\n')

    def max_data(self):
        '''数据量对比'''
        self.run_sql()
        new_all_data=[]
        new_data=self.new_max_data.fetchall()
        # print(new_data)
        for i in new_data:
            if i[1]=='{[]}':
                new_all_data.append(i[1])
            else:
                new_all_data.append(json.dumps(json.loads(i[1])))
        # print(new_all_data)

        old_all_data=[]
        old_data=self.old_max_data.fetchall()
        # print(type(old_data))
        for i in old_data:
            # print(type(i))
            if i[1]=='[]':
                old_all_data.append('{[]}')
            else:
                s=json.loads(i[1])
                for j in range(len(s)):
                    old_all_data.append(json.dumps(s[j]))
        # print(old_all_data)
        print('count new_all_data:',len(new_all_data))
        print('count old_all_data:',len(old_all_data))
        a=sorted(new_all_data)
        b=sorted(old_all_data)
        if a==b:
            print("新旧数据相等")
        else:
            print("数据不等，请重新检查")
            for i in range(len(a)):
                if a[i]==b[i]:
                    continue
                else:
                    print(a[i])
                    print(b[i])

    def all_data(self):
        '''all test 字段拆分对比'''
        print('字段拆分对比')
        self.run_sql()
        data_all_new=[]
        new_all_data=self.new_all_data.fetchall()

        for i in new_all_data:
            data_all_new.append([i[0],i[1],i[2],i[3]])
        # print(data_all_new)

        old_all_data=self.old_all_data.fetchall()
        data_all_old=[]
        for i in old_all_data:
            if i[1]=='[]':
                data_all_old.append([i[0],'{[]}',i[2],i[3]])
            else:
                s=json.loads(i[1])
                for j in range(len(s)):
                    str_s=json.dumps(s[j],cls=DecimalEncoder,ensure_ascii=False)
                    if'", "'in str_s:
                        str_s=str_s.replace('", "','","')
                    if '": "'in str_s:
                        str_s=str_s.replace('": "','":"')
                    if '": 'in str_s:
                        str_s=str_s.replace('": ','":')
                    if ', "'in str_s:
                        str_s=str_s.replace(', "',',"')
                    data_all_old.append([i[0],str_s,i[2],i[3]])
        # print(data_all_old)
        print('count data_all_new:',len(data_all_new))
        print('count data_all_old:',len(data_all_old))
        a=sorted(data_all_new)
        b=sorted(data_all_old)
        if len(data_all_new)==len(data_all_old):
            print('新旧数据数量相等','\n')
            if a==b:
                print('新旧数据对比相等')
            else:
                print('新旧数据对比不等，请检查')
                for i in range(len(a)):
                    if a[i]==b[i]:
                        continue
                    else:
                        print(a[i])
                        print(b[i])
        else:
            print('新旧数据数量不相等')




if __name__=='__main__':
    s=RUNSQL()
    s.sql_test()
    s.all_data()