import pymysql
import decimal
import datetime
import json
import os
__description__='Testting order_delivery and t_wms2tms_order'
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.new_cursor2=new_db.cursor()
self.old_cursor1=old_db.cursor()
self.old_cursor2=old_db.cursor()
self.id_order_delivery=new_db.cursor()
self.id_order_id_tmp=new_db.cursor()
self.count_order_delivery=new_db.cursor()
self.count_order_id_tmp=new_db.cursor()
self.count_Interval_order_delivery=new_db.cursor()
self.count_Interval_t_wms2tms_order=old_db.cursor()
self.max_new_data=new_db.cursor()
self.max_old_data=old_db.cursor()
self.new_cursor1.execute("desc order_delivery;")
self.old_cursor1.execute('desc t_wms2tms_order')
self.max_new_data.execute("SELECT toid,need_invoice,batch_num,customer_remark,delivery_method,picked_id,picked_store_id,get_at,get_by,send_at,send_by,send_phone,sign_at,sign_remark ,rejection_remark,send_time_start,send_time_end,STATUS,push_start_time,push_end_time,created_at FROM order_delivery LIMIT 2000 ;")
self.max_old_data.execute("select toid,needInvoice,batchNum,customerRemark,deliverymode,selfFetchPointId,selfFetchMerchantId,gettime,getperson,sendtime,sender,senderphone,signtime,signremark,refuseBackRemark,minHours,maxHours,deletestatus,pushStartTime,pushEndTime,arriveTime FROM t_wms2tms_order WHERE createTime>'2018-01-01' ORDER BY createTime limit 2000;")
#新旧数据对比 sum
self.new_all_data=new_db.cursor()
self.old_all_data=old_db.cursor()
self.new_all_data.execute("SELECT SUM( CRC32( CONCAT( IFNULL(toid, ''), IFNULL(need_invoice, ''), IFNULL(batch_num, ''), IFNULL(customer_remark, ''), IFNULL(delivery_method, ''), IFNULL(get_at, ''), IFNULL(get_by, ''), IFNULL(send_at, ''), IFNULL(send_by, ''), IFNULL(send_phone, ''), IFNULL(sign_at, ''), IFNULL(sign_remark, ''), IFNULL(rejection_remark, ''), IFNULL(status, ''), IFNULL(push_start_time, ''), IFNULL(push_end_time, ''), IFNULL(created_at, '') ) ) ) FROM tms.order_delivery a JOIN tms.order_id_tmp b ON a.order_id=b.order_id; ")
self.old_all_data.execute("SELECT SUM(CRC32(CONCAT(IFNULL(toid,''), IFNULL(needInvoice,''), IFNULL(batchNum,''), IFNULL(customerRemark,''), IFNULL(deliverymode,''), IFNULL(gettime,''), IFNULL(getperson,''), IFNULL(sendtime,''), IFNULL(sender,''), IFNULL(senderphone,''), IFNULL(signtime,''), IFNULL(signremark,''), IFNULL(refuseBackRemark,''), IFNULL(deletestatus,''), IFNULL(pushStartTime,''), IFNULL(pushEndTime,''), IFNULL(arriveTime,'') ))) FROM tms_tmp.t_wms2tms_order a JOIN tms.order_id_tmp b ON a.txLogisticID=b.order_no;")
#验证特殊字段 toid,selfFetchPointId,selfFetchMerchantId,minHours,maxHours,arrivalTime toid,picked_id,picked_store_id,send_time_start,send_time
self.new_Fields=new_db.cursor()
self.old_Fields=old_db.cursor()
self.new_Fields.execute("SELECT a.order_id,picked_id,picked_store_id,send_time_start,send_time_end FROM tms.order_delivery a JOIN tms.order_id_tmp b ON a.order_id=b.order_id ORDER BY a.order_id DESC ;")
self.old_Fields.execute("SELECT order_id,IF(selfFetchPointId IS NULL or selfFetchPointId = '', 0, selfFetchPointId) selfFetchPointId,IF(selfFetchMerchantId IS NULL or selfFetchMerchantId = '', 0, selfFetchMerchantId) selfFetchMerchantId,\
if((minHours is null and arrivalTime is null) ,null,CONCAT(arrivalTime,' ', minHours)) as minHours,if((maxHours is null and arrivalTime is null) ,null,CONCAT(arrivalTime,' ', maxHours)) as maxHours \
FROM tms_tmp.t_wms2tms_order a JOIN tms_testdba.order_id_tmp b ON a.txLogisticID=b.order_no ORDER BY order_id DESC ;")
new_db.close()
old_db.close()
def sql_test(self):
'''新旧字段对比'''
self.run_sql()
print('字段名称','字段类型','是否为空','主键外键','默认值','附加说明')
new_list=[]
old_list=[]
for res_new in self.new_cursor1.fetchall():
if res_new[0]=='toid'or res_new[0]=='need_invoice'or res_new[0]=='batch_num'or res_new[0]=='customer_remark'or res_new[0]=='delivery_method'or \
res_new[0]=='picked_id'or res_new[0]=='picked_store_id'or res_new[0]=='get_at'or res_new[0]=='get_by'or res_new[0]=='send_at'or \
res_new[0]=='send_by'or res_new[0]=='send_phone'or res_new[0]=='sign_at'or res_new[0]=='sign_remark'or res_new[0]=='rejection_remark'or \
res_new[0]=='send_time_start'or res_new[0]=='send_time_end'or res_new[0]=='status'or res_new[0]=='push_start_time'or res_new[0]=='push_end_time'or \
res_new[0]=='created_at':
new_list.append(res_new)
else:
print(res_new)
print('\n')
for res_old in self.old_cursor1.fetchall():
if res_old[0]=='toid'or res_old[0]=='needInvoice'or res_old[0]=='batchNum'or res_old[0]=='customerRemark'or res_old[0]=='deliverymode' or \
res_old[0]=='selfFetchPointId'or res_old[0]=='selfFetchMerchantId'or res_old[0]=='gettime'or res_old[0]=='getperson'or res_old[0]=='sendtime'or \
res_old[0]=='sender'or res_old[0]=='senderphone' or res_old[0]=='signtime'or res_old[0]=='signremark'or res_old[0]=='refuseBackRemark' or \
res_old[0]=='minHours'or res_old[0]=='maxHours'or res_old[0]=='deletestatus'or res_old[0]=='pushStartTime'or res_old[0]=='pushEndTime'or \
res_old[0]=='arriveTime':
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]=='toid':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='toid':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='need_invoice':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='needInvoice':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='batch_num':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='batchNum':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='customer_remark':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='customerRemark':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='delivery_method':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='deliverymode':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='picked_id':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='selfFetchPointId':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='picked_store_id':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='selfFetchMerchantId':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='get_at':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='gettime':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='get_by':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='getperson':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='send_at':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='sendtime':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='send_by':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='sender':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='send_phone':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='senderphone':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='sign_at':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='signtime':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='sign_remark':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='signremark':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='rejection_remark':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='refuseBackRemark':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='send_time_start':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='minHours':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='send_time_end':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='maxHours':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='status':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='deletestatus':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='push_start_time':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='pushStartTime':
print(old_list[i])
print('\n')
for i in range(len(new_list)):
if new_list[i][0]=='push_end_time':
print(new_list[i])
for i in range(len(new_list)):
if old_list[i][0]=='pushEndTime':
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]=='arriveTime':
print(old_list[i])
print('\n')
def select_data(self):
'''新旧数据对比'''
self.run_sql()
all_list=[]
for res in (self.new_cursor2.fetchall(),self.old_cursor2.fetchall()):
all_list.append(res)
# print(all_list)
# print(len(all_list))
# print(json.dumps(all_list,cls=DecimalEncoder,indent=4,ensure_ascii=False,sort_keys=True))
for i in range(len(all_list[0])):
if all_list[0][i]==all_list[1][i]:
# print(all_list[0][i],'\n',all_list[1][i])
pass
else:
for j in range(len(all_list[0][0])):
if all_list[0][i][j]!=all_list[1][i][j]:
print(all_list[0][i][j],all_list[1][i][j])
print(all_list[0][i],'\n',all_list[1][i])
continue
def max_data(self):
'''大数据验证'''
self.run_sql()
all_list=[]
for res in (self.max_new_data.fetchall(),self.max_old_data.fetchall()):
all_list.append(res)
# print(json.dumps(all_list,cls=DecimalEncoder,indent=4,ensure_ascii=False,sort_keys=True))
for i in range(len(all_list[0])):
if all_list[0][i]==all_list[1][i]:
# print(all_list[0][i],all_list[1][i])
pass
else:
for j in range(len(all_list[0][0])):
if all_list[0][i][j]!=all_list[1][i][j]:
print(all_list[0][i][j],all_list[1][i][j])
print(all_list[0][i],'\n',all_list[1][i])
continue
def all_data(self):
'''新旧数据对比 sum crc32'''
print('平移数据')
self.run_sql()
new_data=self.new_all_data.fetchall()[0][0]
old_data=self.old_all_data.fetchall()[0][0]
print("new_data:",new_data)
print("old_data:",old_data)
if new_data==old_data:
print("新旧数据相等")
else:
print("新旧数据不等,请检查")
def Fields(self):
'''字段组合'''
print('字段组合对比验证:')
self.run_sql()
new_Fields=self.new_Fields.fetchall()
old_Fields=self.old_Fields.fetchall()
new_Field_data=[]
for Field_1 in new_Fields:
new_Field_data.append([str(Field_1[0]),str(Field_1[1]),str(Field_1[2]),str(Field_1[3]),str(Field_1[4])])
old_Field_data=[]
for Field_2 in old_Fields:
if len(str(Field_2[3]))>10 and len(str(Field_2[3]))<19:
str_s=str(Field_2[3]).split(' ')
s=str_s[0]+' '+'0'+str_s[1]
old_Field_data.append([str(Field_2[0]),Field_2[1],Field_2[2],s,str(Field_2[4])])
else:
old_Field_data.append([str(Field_2[0]),Field_2[1],Field_2[2],str(Field_2[3]),str(Field_2[4])])
print('新数据统计:',len(new_Field_data))
print('旧数据统计:',len(old_Field_data))
a=sorted(new_Field_data)
b=sorted(old_Field_data)
# os.system('pause')
s=[]
if len(new_Field_data)==len(old_Field_data):
print('新旧数据数量相等')
if a==b:
print('新旧数据对比相等')
else:
for i in range(len(a)):
if a[i]==b[i]:
continue
else:
s.append([a[i],b[i]])
print(a[i])
print(b[i])
print('\n')
print(len(s))
else:
print('新旧数据量不相等')
if __name__=='__main__':
s=RUNSQL()
# s.sql_test()
# s.select_data()
# s.max_data()
s.all_data()
s.Fields()
数据库迁移--字段组合对比
最新推荐文章于 2024-07-29 15:07:17 发布