MYSQL 表结构同步

import pymysql
import configparser

class SchemaMysql:
    #初始化用户数据 isdrop参数为是否删除目标库多余的字段
    def __init__(self,src_info,des_info,isdrop):
        self.src_ip = src_info[0]
        self.src_db_user = src_info[1]
        self.src_db_pass = src_info[2]
        self.des_ip = des_info[0]
        self.des_db_user = des_info[1]
        self.des_db_pass = des_info[2]
        self.isDrop = isdrop


    #初始化不同的数据库
    def init(self,src_db,des_db):
        self.dbsrc = self.connect_db(self.src_ip,src_db,self.src_db_user,self.src_db_pass)
        self.dbdes = self.connect_db(self.des_ip,des_db,self.des_db_user,self.des_db_pass)
        self.cursorsrc = self.dbsrc.cursor()
        self.cursordes = self.dbdes.cursor()

    #手动关闭数据库
    def close_db(self):
        self.cursorsrc.close()
        self.cursordes.close()
        self.dbsrc.close()
        self.dbdes.close()

    #连接数据库
    def connect_db(self,ip,db,db_user,db_pass):
        db = pymysql.connect(ip,db_user,db_pass,db)
        return db

    #获取数据库中的表
    def getTable(self,cursor):
        cursor.execute("show tables")
        tablelist  = []
        for tb in cursor.fetchall():
            tablelist.append(tb[0])
        return tablelist

    #获取创表语句
    def getField(self,cursor,tb):
        sql = "show create table "+str(tb)
        cursor.execute(sql)
        return cursor.fetchone()

    #生成需要执行的sql语句
    def createSql(self,tb,mode,F):
        if mode == 'c':
            sql = "alter table " + str(tb) + " change " + F[0] + " " + F[0]
        elif mode == 'a':
            sql = "alter table " + str(tb) + " add " + F[0]
        elif mode == 'd':
            sql = "alter table " + str(tb) + " drop column " + F[0]
            return sql
        else:
            return None
        for d in range(1, len(F)):
            sql = sql + " " + F[d]

    #检测两个数据表字段是否一样
    def checkField(self,tb,sqlSrc,sqlDes):
        runList = []
        srcList = sqlSrc[1].replace(",","").split("\n")[1:-1]#去除不需要的数据
        desList = sqlDes[1].replace(",","").split("\n")[1:-1]
        for i in srcList:
            srcF = i.split()
            for j in desList:
                desF = j.split()
                if srcF[0] == desF[0]:
                    if len(srcF) == len(desF):
                        for c in range(1,len(srcF)):
                            
                            if srcF[c] != desF[c]:
                                runList.append(self.createSql(tb,"c",srcF))
                                print(runList)
                    else:
                        runList.append(self.createSql(tb, "c", srcF))
                    break;
            else:
                runList.append(self.createSql(tb, "a", srcF))
        if self.isDrop:
            srcList =  [x.split()[0] for x in srcList]
            desList = [x.split()[0] for x in desList]
            for desI in desList:
                if desI not in srcList:
                    runList.append(self.createSql(tb,"d",[desI]))
        return runList

    #执行 删除字段 增加字段 修改字段的sql语句
    def runExec(self,runlist):
        for run in runlist:
            print(run)
            self.cursordes.execute(run)

    #检测数据表是否一样如果没有则在目标主机上创建
    def checkTable(self,tbSrc,tbDes,cursrc,curdes):
        for tbA in tbSrc:
            dataSrc = self.getField(cursrc, tbA)
            if tbA in tbDes:
                runlist = self.checkField(tbA,dataSrc,self.getField(curdes,tbA))
                if not runlist is None:
                    self.runExec(runlist)
            else:
                curdes.execute(dataSrc[1].replace("\n",""))

    #开始运行
    def run(self):
        print("Run")
        srctable = self.getTable(self.cursorsrc)
        destable = self.getTable(self.cursordes)
        self.checkTable(srctable,destable,self.cursorsrc,self.cursordes)

"""
获取config文件信息
包括  ip  用户  密码   表
"""
def getConfDBList():
    src_list = []
    des_list = []
    config = configparser.ConfigParser()
    config.read("config.cfg")
    src_list.append(config.get('SRC', 'ip'))
    src_list.append(config.get('SRC','user'))
    src_list.append(config.get("SRC",'pass'))
    src_list.append(config.get("SRC", 'db'))

    des_list.append(config.get('DES', 'ip'))
    des_list.append(config.get('DES', 'user'))
    des_list.append(config.get("DES", 'pass'))
    des_list.append(config.get("DES", 'db'))
    return [src_list,des_list]



if __name__ == '__main__':
    list = getConfDBList()
    des_info = ['172.17.13.81','root','123456']
    src_info = ['172.17.13.51', 'root', '123456']
    src_db = ['test']
    des_db = ['test']
    print("Begin")
    schema = SchemaMysql(src_info,des_info,True)
    for i in range(len(src_db)):
        if src_db[i] and des_db[i]:
            print(src_db[i],des_db[i])
            schema.init(src_db[i],des_db[i])
            schema.run()
            schema.close_db()