Python如何查看两个数据库的同名表的字段名差异

第Python如何查看两个数据库的同名表的字段名差异engine=sqlalchemy.create_engine('mysql+pymysql://root:123456@:3306/a')

engine1=sqlalchemy.create_engine('mysql+pymysql://root:123456@:3306/b')

inspector=sqlalchemy.inspect(engine)

inspector1=sqlalchemy.inspect(engine1)

table_column_map=get_table_column_map(inspector)

table_column_map1=get_table_column_map(inspector1)

compare_table_column_difference(table_column_map,table_column_map1)

#student{'gender','chemistry','physics','biology'}

mysql-utilities

也可以使用专门的工具mysql-utilities中的mysqldiff

文档

mysqldiff--help

命令

mysqldiff--server1=user:pass@host:port--server2=user:pass@host:portdb1.object1:db2.object1

例子

mysqldiff--server1=root:123456@:3306--server2=root:123456@:3306a.student:b.student

效果

---a.student

+++b.student

@@-3,8+3,13@@

`class_id`int(11)DEFAULTNULL,

`name`varchar(255)DEFAULTNULL,

`birthday`dateDEFAULTNULL,

+`gender`tinyint(4)DEFAULTNULL,

`chinese`int(11)DEFAULTNULL,

`math`int(11)DEFAULTNULL,

`english`int(11)DEFAULTNULL,

-PRIMARYKEY(`id`)

+`physics`int(11)DEFAULTNULL,

+`chemistry`int(11)DEFAULTNULL,

+`biology`int(11)DEFAULTNULL,

+PRIMARYKEY(`id`),

+KEY`idx_class_id`(`class_id`)USINGBTREE

)ENGINE=InnoDBDEFAULTCHARSET=utf8

优点：比较内容更详尽

缺点：无法批量，要自己指定比较对象（稍微改写即可克服）

Python数据库之间差异对比

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

catoracle_diff.py

#!/home/dba/.pyenv/versions/3.5.2/bin/python

#coding=utf-8

importcx_Oracle

importtime

importdifflib

importos

v_host=os.popen('echo$HOSTNAME')

classOracle_Status_Output():

def__init__(self,username,password,tns):

try:

self.db=cx_Oracle.connect(username,password,tns)

self.cursor=self.db.cursor()

exceptExceptionase:

print('Wrong')

print(e)

defschemas_tables_count(self,sql,db):

try:

self.cursor.execute(sql)

v_result=self.cursor.fetchall()

#print(v_result)

count=0

foriinrange(len(v_result)):

#print(v_result[i][1],'--',v_result[i][0])

count=int(v_result[i][0])+count

print(db,'CountTables','--',count)

exceptExceptionase:

print('Wrong--schemas_tables_count()')

print(e)

defschemas_tables_list(self,sql):

try:

self.cursor.execute(sql)

v_result=self.cursor.fetchall()

#print(v_result)

returnv_result

exceptExceptionase:

print('Wrong--schemas_tables_list()')

print(e)

defschemas_tables_columns_list(self,sql,data):

try:

self.cursor.execute(sql,A=data)

v_result=self.cursor.fetchall()

returnv_result

exceptExceptionase:

print('schemas_tables_columns_list')

print(e)

defschemas_tables_indexes_list(self,sql,data):

try:

self.cursor.execute(sql,A=data)

v_result=self.cursor.fetchall()

returnv_result

exceptExceptionase:

print('schemas_tables_indexes_list')

print(e)

defclose(self):

self.db.close()

schemas_tables_count_sql="SELECTCOUNT(1),S.OWNERFROMDBA_TABLESSWHERES.OWNERin('PAY','BOSS','SETTLE','ISMP','TEMP_DSF','ACCOUNT')GROUPBYS.OWNER"

schemas_tables_list_sql

="SELECTS.OWNER||'.'||S.TABLE_NAMEFROMDBA_TABLESSWHERES.OWNERin('PAY','BOSS','SETTLE','ISMP','TEMP_DSF','ACCOUNT')"

schemas_tables_columns_sql="selecta.OWNER||'.'||a.TABLE_NAME||'.'||a.COLUMN_NAME||'.'||a.DATA_TYPE||'.'||a.DATA_LENGTHfromdba_tab_columnsawherea.OWNER||'.'||a.TABLE_NAME=:A"

schemas_tables_indexes_sql="SELECTT.table_owner||'.'||T.table_name||'.'||T.index_nameFROMDBA_INDEXESTWHERET.table_owner||'.'||T.table_name=:A"

jx_db

=Oracle_Status_Output('dbadmin','QazWsx12','34:1522/paydb')

pro_db=Oracle_Status_Output('dbadmin','QazWsx12','localhost:1521/paydb')

jx_db.schemas_tables_count(schemas_tables_count_sql,'JX')

pro_db.schemas_tables_count(schemas_tables_count_sql,'PRO')

jx_schemas_tables

=jx_db.schemas_tables_list(schemas_tables_list_sql)

pro_schemas_tables=pro_db.schemas_tables_list(schemas_tables_list_sql)

#print(jx_schemas_tables)

#print(pro_schemas_tables)

defdiff_jx_pro(listA,listB,listClass):

iflistA!=[]andlistB!=[]:

#listD

=list(set(listA).union(set(listB)))

listC

=sorted(list(set(listA).intersection(set(listB))))

listAC=sorted(list(set(listA).difference(set(listC))))

listBC=sorted(list(set(listB).difference(set(listC))))

#ifsorted(listD)==sorted(listC):

#

print('AllTablesOK')

iflistC==[]:

#print('JX',listClass,':',listA)

#print('PRO',listClass,':',listB)

print('Intersection','JX',listClass,':',listAC,'---','PRO',listClass,':',listBC)

eliflistAC!=[]orlistBC!=[]:

print('Difference

','JX',listClass,':',listAC,'---','PRO',listClass,':',listBC)

else:

pass

returnlistC

if__name__=='__main__':

#diff_jx_pro(jx_schemas_tables,pro_schemas_tables)

tables_lists=diff_jx_pro(jx_schemas_tables,pro_schemas_tables,'Tables')

foriinrange(len(tables_lists)):

table_name="".join(tuple(tables_lists[i]))

#print(table_name)

jx_schemas_tables_columns=jx_db.schemas_tables_columns_list(schemas_tables_columns_sql,table_name)

pro_schemas_tables_columns=pro_db.schemas_tables_columns_list(schemas_tables_columns_sql,table_name)

diff_jx_pro(jx