import mysql.connector
from mysql.connector import Error
import json
db1 = mysql.connector.connect(user='root', password='root',host='127.0.0.1', database='db1')
db2 = mysql.connector.connect(user='root', password='root',host='127.0.0.1', database='db2')
cursor1 = db1.cursor()
cursor2 = db2.cursor()
# 获取数据库1的表名列表
cursor1.execute("SHOW TABLES")
tables1 = [table[0] for table in cursor1.fetchall()]
# 获取数据库2的表名列表
cursor2.execute("SHOW TABLES")
tables2 = [table[0] for table in cursor2.fetchall()]
# 找出差异的表
diff_tables = list(set(tables1) ^ set(tables2))
print(f'Different tables: {diff_tables}')
set_log('diff_tables',f'Different tables: {diff_tables}')
# 对于每个相同的表,获取其列的信息
for table in set(tables1) & set(tables2):
cursor1.execute(f"SHOW COLUMNS FROM {table}")
columns1 = [column[0] for column in cursor1.fetchall()]
cursor2.execute(f"SHOW COLUMNS FROM {table}")
columns2 = [column[0] for column in cursor2.fetchall()]
# 找出差异的列
diff_columns = list(set(columns1) ^ set(columns2))
print(f'Different columns in table {table}: {diff_columns}')
# 找出两个数据库中都存在的表
common_tables = set(tables1) & set(tables2)
for table in common_tables:
# 获取表中的数据
cursor1.execute(f"SELECT * FROM {table}")
data1 = cursor1.fetchall()
cursor2.execute(f"SELECT * FROM {table}")
data2 = cursor2.fetchall()
# 找出在一个表中存在但在另一个表中不存在的数据
diff_data = list(set(data1) ^ set(data2))
print(f'Different data in table {table}: {diff_data}')
# 获取表的索引信息
cursor1.execute(f"SHOW INDEX FROM {table}")
indexes1 = cursor1.fetchall()
cursor2.execute(f"SHOW INDEX FROM {table}")
indexes2 = cursor2.fetchall()
# 找出在一个表中存在但在另一个表中不存在的索引
diff_indexes = list(set(indexes1) ^ set(indexes2))
print(f'Different indexes in table {table}: {diff_indexes}')
db1.close()
db2.close()
python实现两个数据表的所有表,表索引,表数据的差异对比
最新推荐文章于 2024-09-05 10:28:02 发布