利用正则表达式对比数据库

不是直接对比数据库，而是把数据库创建的sql导出来，对比sql，其实就是对比两个文本文件。

import re

def getPowerFile():
    f = open("C:\\Users\\Administrator\\Desktop\\sql对比\\Z_T.sql","r")
    return f

def get主干数据库File():
    f = open("C:\\Users\\Administrator\\Desktop\\sql对比\\I_T.sql","r")
    return f

def getDict(file,pattern,offset):
    match = pattern.findall(file.read().lower())
    retDict = {}
    if match:
        for index in range(len(match)):
            #print(match[index])
            wordList = match[index].replace('\n',' ').replace(';',' ').split(' ')
            retDict[wordList[offset]] = match[index]
    return retDict

def dumpDict(tmpDict):
    for key in tmpDict:
        print("-------------------------------")
        print("key:",key)
        print("value:\n",tmpDict[key])
    #print("dict num : ",len(tmpDict))

def dumpList(tmpList):
    if not tmpList:
        print("空")
    tmpList.sort()
    for index in range(len(tmpList)):
        print(tmpList[index])
    #print("list num : ",len(tmpList))

def getNew(powerDict,主干数据库Dict):
    newList = []
    for key in powerDict:
        if key not in 主干数据库Dict:
            newList.append(key)
    return newList

def getChange(powerDict,主干数据库Dict):
    changelist = []
    for key in powerDict:
        if key in 主干数据库Dict:
            主干数据库tmp = re.sub('--.*?\n|\s+|,','',主干数据库Dict[key])
            主干数据库tmp = re.sub('/\*.*\*/',主干数据库tmp)
            powertmp = re.sub('--.*?\n|\s+|,powerDict[key])
            powertmp = re.sub('/\*.*\*/',powertmp)
            if 主干数据库tmp != powertmp:
                changelist.append(key)
    return changelist





#---------------------------generator----------------------------------
def handleGenerator():
    pattern = re.compile('create generator .*;\n.*;',re.M|re.I)
    powerDict = getDict(getPowerFile(),2)
    主干数据库Dict = getDict(get主干数据库File(),2)
    newList = getNew(powerDict,主干数据库Dict)
    changelist = getChange(powerDict,主干数据库Dict)
    print("目前Generator数目：",len(powerDict),"主干数据库目前Generator数目：",len(主干数据库Dict))
    print("新建Generator数目：",len(newList),"修改Generator数目：",len(changelist))
    print("新建Generator名列表：")
    dumpList(newList)
    print("\n修改Generator列表：")
    dumpList(changelist)
    print("\n修改Generator详细信息：")
    for index in range(len(changelist)):
        print("主干数据库:\n",主干数据库Dict[changelist[index]])
        print(":\n",powerDict[changelist[index]])
        print("\n")

#---------------------------procedure----------------------------------
def handleProcedure():
    pattern = re.compile('create or alter procedure .*?^end;',re.M|re.I|re.S)
    powerDict = getDict(getPowerFile(),4)
    主干数据库Dict = getDict(get主干数据库File(),4)
    newList = getNew(powerDict,主干数据库Dict)
    print("目前存储过程数目：","主干数据库目前存储过程数目：",len(主干数据库Dict))
    print("新建存储过程数目：","修改存储过程数目：",len(changelist))
    print("新建存储过程名列表：")
    dumpList(newList)
    print("\n修改存储过程列表：")
    dumpList(changelist)
    #for index in range(len(changelist)):
            #方便验证，不打印内容，只打印到returns，内容需要用beyondCampare比较
            #print("主干数据库:\n ",主干数据库match.group(0))
            #print(":\n ",powermatch.group(0)) 
            #print("主干数据库:\n ",主干数据库Dict[changelist[index]])
            #print(":\n ",powerDict[changelist[index]])
            #print("\n")
    print("\n")

#---------------------------trigger----------------------------------
def handleTrigger():
    pattern = re.compile('create or alter trigger .*?^end;',主干数据库Dict)
    print("目前触发器数目：","主干数据库目前触发器数目：",len(主干数据库Dict))
    print("新建触发器数目：","修改触发器数目：",len(changelist))
    print("新建触发器名列表：")
    dumpList(newList)
    print("\n修改触发器列表：")
    dumpList(changelist)
    print("\n修改触发器详细信息：")
    for index in range(len(changelist)):
        print("主干数据库:\n ",主干数据库Dict[changelist[index]])
        print(":\n ",powerDict[changelist[index]])
        print("\n")
    print("\n")

#---------------------------data 目前只处理 system_paramete 表新增数据----------------------------------
def handleData():
    pattern = re.compile('insert into system_parameter .*?;',9)
    主干数据库Dict = getDict(get主干数据库File(),9)
    newList = getNew(powerDict,主干数据库Dict)
    print("目前system_parameter数目：","主干数据库目前system_parameter数目：",len(主干数据库Dict))
    print("新建system_parameter数目：","修改system_parameter数目：",len(changelist))
    print("新建system_parameter列表：")
    for index in range(len(newList)):
        print(powerDict[newList[index]])

    print("\n修改system_parameter列表：")
    for index in range(len(changelist)):
        print("主干数据库:\n ",powerDict[changelist[index]])
        print("\n")
    print("\n")

#---------------------------table----------------------------------
def handleTable():
    pattern = re.compile('create table .*?;',2)
    tmpList = []
    for key in 主干数据库Dict:
        tmpList.append(key)
    #dumpList(tmpList)
    #dumpDict(主干数据库Dict)
    newList = getNew(powerDict,主干数据库Dict)
    #sql导出的表5张临时表没有统计，2张系统表多于
    print("目前表数目：",len(powerDict) + 5 - 2,"主干数据库目前表数目：",len(主干数据库Dict) + 5 - 2)
    print("新建表数目：","修改表数目：",len(changelist))
    print("新建表名列表：")
    dumpList(newList)
    print("\n修改表名明列表：")
    dumpList(changelist)
    print("\n")

#---------------------------table column----------------------------------
def getTableColumnDict(createTablesql):
    pattern = re.compile('^ .*?\n',re.M|re.I)
    match = pattern.findall(createTablesql.lower())
    retDict = {}
    if match:
        #print("match num:",len(match))
        for index in range(len(match)):
            #print(match[index])
            p = re.compile(r'\S+')
            wordList = p.findall(match[index])
            #print("size of dict:",len(wordList))
            retDict[wordList[0]] = match[index].rstrip(',')
    return retDict

def handleColumn():
    print("修改的表的详细信息：")
    pattern = re.compile('create table .*?;',2)
    for key in powerDict:
        if key in 主干数据库Dict and (powerDict[key] != 主干数据库Dict[key]):
            powerColumnDict = getTableColumnDict(powerDict[key])
            主干数据库ColumnDict = getTableColumnDict(主干数据库Dict[key])
            newList = getNew(powerColumnDict,主干数据库ColumnDict)
            changelist = getChange(powerColumnDict,主干数据库ColumnDict)
            print("修改的表名：",key)
            #dumpDict(powerColumnDict)
            print("主干数据库创建语句:\n",主干数据库Dict[key])
            print("表创建语句:\n",powerDict[key])
            print("新增","列:")
            dumpList(newList)
            #dumpDict(主干数据库ColumnDict)
            print("修改",len(changelist),"列:")
            dumpList(changelist)
            print("\n")
    print("\n")
    return

''' print("<html>") print("<body>") print("<p>") '''
handleTable()
handleColumn()
handleProcedure()
handleTrigger()
handleData()
handleGenerator()
''' print("</p>") print("</body>") print("</html>") '''