Python数据库之间差异对比

参考资料&＃xff1a;

Python 集合&＃xff08;set&＃xff09;

此脚本用于两个数据库之间的表、列、栏位、索引的差异对比。

cat oracle_diff.py
#!/home/dba/.pyenv/versions/3.5.2/bin/python
#coding&＃61;utf-8
import cx_Oracle
import time
import difflib
import os

v_host&＃61;os.popen(&＃39;echo $HOSTNAME&＃39;)

class Oracle_Status_Output():
    def __init__(self,username,password,tns):
        try:
            self.db &＃61; cx_Oracle.connect(username,password,tns)
            self.cursor &＃61; self.db.cursor()
        except Exception as e:
            print(&＃39;Wrong&＃39;)
            print(e)
    def schemas_tables_count(self,sql,db):
        try:  
            self.cursor.execute(sql)
            v_result&＃61;self.cursor.fetchall()
            #print(v_result)
            count &＃61; 0
            for i in range(len(v_result)):
                #print(v_result[i][1],&＃39;--&＃39;,v_result[i][0])
                count &＃61; int(v_result[i][0]) &＃43; count
            print(db,&＃39;Count Tables&＃39;,&＃39;--&＃39;,count)
        except Exception as e:
            print(&＃39;Wrong--schemas_tables_count()&＃39;)
            print(e)
    def schemas_tables_list(self,sql):
        try:
            self.cursor.execute(sql)
            v_result&＃61;self.cursor.fetchall()
            #print(v_result)
            return v_result
        except Exception as e:
            print(&＃39;Wrong--schemas_tables_list()&＃39;)
            print(e)
    def schemas_tables_columns_list(self,sql,data):
        try:
            self.cursor.execute(sql,A&＃61;data)
            v_result&＃61;self.cursor.fetchall()
            return v_result
        except Exception as e:
            print(&＃39;schemas_tables_columns_list&＃39;)
            print(e)
    def schemas_tables_indexes_list(self,sql,data):
        try:
            self.cursor.execute(sql,A&＃61;data)
            v_result&＃61;self.cursor.fetchall()
            return v_result
        except Exception as e:
            print(&＃39;schemas_tables_indexes_list&＃39;)
            print(e)
    def close(self):
        self.db.close()

schemas_tables_count_sql &＃61; "SELECT COUNT(1),S.OWNER FROM DBA_TABLES S WHERE S.OWNER in (&＃39;PAY&＃39;,&＃39;BOSS&＃39;,&＃39;SETTLE&＃39;,&＃39;ISMP&＃39;,&＃39;TEMP_DSF&＃39;,&＃39;ACCOUNT&＃39;) GROUP BY S.OWNER"
schemas_tables_list_sql  &＃61; "SELECT S.OWNER||&＃39;.&＃39;||S.TABLE_NAME FROM DBA_TABLES S WHERE S.OWNER in (&＃39;PAY&＃39;,&＃39;BOSS&＃39;,&＃39;SETTLE&＃39;,&＃39;ISMP&＃39;,&＃39;TEMP_DSF&＃39;,&＃39;ACCOUNT&＃39;)"
schemas_tables_columns_sql &＃61; "select a.OWNER||&＃39;.&＃39;||a.TABLE_NAME||&＃39;.&＃39;||a.COLUMN_NAME||&＃39;.&＃39;||a.DATA_TYPE||&＃39;.&＃39;||a.DATA_LENGTH from dba_tab_columns a where a.OWNER||&＃39;.&＃39;||a.TABLE_NAME &＃61; :A"
schemas_tables_indexes_sql &＃61; "SELECT T.table_owner||&＃39;.&＃39;||T.table_name||&＃39;.&＃39;||T.index_name FROM DBA_INDEXES T WHERE T.table_owner||&＃39;.&＃39;||T.table_name &＃61; :A"

jx_db  &＃61; Oracle_Status_Output(&＃39;dbadmin&＃39;,&＃39;QazWsx12&＃39;,&＃39;106.15.109.134:1522/paydb&＃39;)
pro_db &＃61; Oracle_Status_Output(&＃39;dbadmin&＃39;,&＃39;QazWsx12&＃39;,&＃39;localhost:1521/paydb&＃39;)

jx_db.schemas_tables_count(schemas_tables_count_sql,&＃39;JX &＃39;)
pro_db.schemas_tables_count(schemas_tables_count_sql,&＃39;PRO&＃39;)

jx_schemas_tables  &＃61; jx_db.schemas_tables_list(schemas_tables_list_sql)
pro_schemas_tables &＃61; pro_db.schemas_tables_list(schemas_tables_list_sql)

#print(jx_schemas_tables)
#print(pro_schemas_tables)

def diff_jx_pro(listA,listB,listClass):
    if listA !&＃61;[] and listB !&＃61;[]:
        #listD  &＃61; list(set(listA).union(set(listB)))
        listC  &＃61; sorted(list(set(listA).intersection(set(listB))))
        listAC &＃61; sorted(list(set(listA).difference(set(listC))))
        listBC &＃61; sorted(list(set(listB).difference(set(listC))))
        #if sorted(listD) &＃61;&＃61; sorted(listC):
        #    print(&＃39;All Tables OK&＃39;)
        if listC &＃61;&＃61; []:
            #print(&＃39;JX &＃39;,listClass,&＃39;:&＃39;,listA)
            #print(&＃39;PRO &＃39;,listClass,&＃39;:&＃39;,listB)
            print(&＃39;Intersection>>&＃39;,&＃39;JX &＃39;,listClass,&＃39;:&＃39;,listAC,&＃39;--->&＃39;,&＃39;PRO&＃39;,listClass,&＃39;:&＃39;,listBC)
        elif listAC !&＃61; [] or listBC !&＃61; []:
            print(&＃39;Difference  >>&＃39;,&＃39;JX &＃39;,listClass,&＃39;:&＃39;,listAC,&＃39;--->&＃39;,&＃39;PRO&＃39;,listClass,&＃39;:&＃39;,listBC)
        else:
            pass
        return listC

if __name__ &＃61;&＃61; &＃39;__main__&＃39;:
    #diff_jx_pro(jx_schemas_tables,pro_schemas_tables)

tables_lists &＃61; diff_jx_pro(jx_schemas_tables,pro_schemas_tables,&＃39;Tables&＃39;)
    for i in range(len(tables_lists)):
        table_name &＃61; "".join(tuple(tables_lists[i]))
        #print(table_name)
        jx_schemas_tables_columns &＃61; jx_db.schemas_tables_columns_list(schemas_tables_columns_sql,table_name)
        pro_schemas_tables_columns &＃61; pro_db.schemas_tables_columns_list(schemas_tables_columns_sql,table_name)
        diff_jx_pro(jx_schemas_tables_columns,pro_schemas_tables_columns,&＃39;Columns&＃39;)

for i in range(len(tables_lists)):
        table_name &＃61; "".join(tuple(tables_lists[i]))
        #print(table_name)
        jx_schemas_tables_indexes &＃61; jx_db.schemas_tables_indexes_list(schemas_tables_indexes_sql,table_name)
        pro_schemas_tables_indexes &＃61; pro_db.schemas_tables_indexes_list(schemas_tables_indexes_sql,table_name)
        diff_jx_pro(jx_schemas_tables_indexes,pro_schemas_tables_indexes,&＃39;Indexes&＃39;)
    jx_db.close()
    pro_db.close()
    print(&＃39;------------Table, column and index check completed--------------&＃39;)