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()
数据库迁移--字段拆分对比
最新推荐文章于 2024-07-29 15:07:17 发布