mysql迁移后数据对比,数据迁移对比

handleutils.py

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Date : 2018-01-05 16:12:27

# @Author : Your Name (you@example.org)

# @Link : http://example.org

# @Version : 3.6.1

import configparser

import os

import pymysql

import string

import random

class HandleMysqlByIni(object):

def __init__(self, config_file):

"""

config_file: configfile 配置文件名或者配置文件的相对路径

"""

# 判断 config_path 配置文件的路径是否是绝对路径

if not os.path.isabs(config_file):

# 获取当前工作目录

get_dir = os.getcwd()

config_file = get_dir + '/configfile/' + config_file

# 读取 configfile 配置文件

self.conf = configparser.ConfigParser()

# config_file 配置文件的路径必须是绝对路径

self.conf.read(config_file, encoding='utf-8')

def get_data(self, section, option):

data = self.conf.get(section, option)

return data

def get_db_connect(self, db_section="db", host="host", user="user",

password="password", port="port", db_option=""):

"""

获得游标对象

Args:

db_section: config_file配置文件中的节点

host: config_file配置文件中的位置(ip地址)

user：config_file配置文件中的位置(用户名)

password: config_file配置文件中的位置(密码)

port: config_file配置文件中的位置(端口)

db_option: config_file配置文件中的位置(数据库名称)

"""

conn = pymysql.connect(host=self.conf.get(db_section, host), user=self.conf.get(db_section, user),

passwd=self.conf.get(db_section, password), db=self.conf.get(db_section, db_option),

port=int(self.conf.get(db_section, port)), charset='utf8')

return conn

def execute_mysql(self, cursor_object, table_name, action, data_tuple=()):

"""

Args:

cursor_object: pymysql 游标对象

action: 插入insert、更新update、查询query、删除delete语句名称

table_name: 数据表名称

"""

if not isinstance(data_tuple, tuple):

raise Exception("data_tuple must be a tuple")

# 在 configfile 配置文件中, table_name为section节点, action为option位置

data_sql = self.conf.get(table_name, action)

if data_tuple == '':

# 执行 sql 语句(不带参数)

cursor_object.execute(data_sql)

else:

# 执行 sql 语句(带参数)

data = data_sql.format(*data_tuple)

cursor_object.execute(data)

class RandomValue(object):

def __init__(self):

pass

@staticmethod

def base_str(self):

return string.ascii_letters + string.digits

# 获取固定长度为KEY_LEN 的字符串，(随机混合的数和随机字母)

@staticmethod

def key_gen(self, key_len):

key_list = [random.choice(self.base_str()) for i in range(key_len) if i >= 0]

return "".join(key_list)

# 获取固定长度为KEY_LEN 的纯数字字符串

@staticmethod

def random_num(key_len):

key_list = [random.choice(string.digits) for i in range(key_len) if i >= 0]

return ''.join(key_list)

# 随机获取姓名

@staticmethod

def get_name():

pass

datasync.py

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Date : 2018-01-05 16:12:27

# @Author : Your Name (you@example.org)

# @Link : http://example.org

# @Version : 3.6.1

from handleutils import HandleMysqlByIni

class SyncYunnan(object):

"""云南混合云同步，测试脚本"""

def __init__(self):

self.file = 'yunnan_db.conf'

def sync_compare(self, data_table, data_sql='data_sql'):

"""

对比同步的数据是否一直

data_table: 进行对比的数据表

data_sql: sql查询语句

"""

# 连接同步前的数据库(私有云)

handle = HandleMysqlByIni(self.file)

conn = handle.get_db_connect(db_section="init_db", db_option="init_db")

cur = conn.cursor()

# 执行查询语句

handle.execute_mysql(cur, data_table, data_sql)

# 返回查询结果记录数

count_first = cur.rowcount

# 获得查询结果，不包含字段信息

data_first = cur.fetchall()

cur.close()

conn.close()

# 获取表的字段信息

field = cur.description

str_field = ''

for len_field in range(0, len(field)):

str_field = str_field + field[len_field][0] + ' | '

print('-'*300)

print('数据表：{0}'.format(data_table))

print(str_field)

# 连接同步后的数据库(公有云)

handle = HandleMysqlByIni(self.file)

conn = handle.get_db_connect(db_section="compare_db", db_option="compare_db")

cur = conn.cursor()

# 执行查询语句

handle.execute_mysql(cur, data_table, data_sql)

count_second = cur.rowcount

data_second = cur.fetchall()

# 判断数据表行数是否一致

if count_first == count_second:

count = count_first

# 数据表的所有数据进行对比

num = 0

field_info = ''

for num_id in range(0, count):

field_count = 0

for num_field in range(0, len(field)):

# 同步前的字段数据

init_field = data_first[num_id][num_field]

# 同步后的字段数据

compare_field = data_second[num_id][num_field]

# 判断每个字段数据是否相等

if init_field != compare_field:

field_count += 1

field_data = field[num_field][0]

if field_data not in field_info:

field_info = field_info + field[num_field][0] + ','

print('数据不一致的字段：{0}'.format(field_data))

# 找出不一致的数据

if field_count != 0:

num += 1

print(data_first[num_id])

# print()在输出后默认换行, end值为空，将会消除换行

print(data_second[num_id], end='')

print('\n')

if num == 0:

print('数据表：{0}, 数据一致'.format(data_table))

print('\n')

else:

print('数据表：{0}'.format(data_table))

print('数据不一致的所有字段：{0}'.format(field_info))

print('数据不一致的总数： {0}'.format(num))

print('\n')

else:

# 找出过多的数据

if count_first < count_second:

for data in data_second:

if data not in data_first:

print(data)

else:

for data in data_first:

if data not in data_second:

print(data)

print('数据表：{0}, 数据不一致'.format(data_table))

print('同步前, 数据表行数：{0}'.format(count_first))

print('同步后, 数据表行数：{0}'.format(count_second))

print('\n')

def test():

table_list = ['c_order_info', 'c_order_codebag', 'c_upload_print_batch', 'c_upload_activate',

'c_upload_activate_batch', 'c_order_materials', 'c_order_materials_section', 'c_company_info',

'c_upload_print_codebag']

for table in table_list:

SyncYunnan().sync_compare(table)

if __name__ == '__main__':

test()