基本思路:
①比较库中的表:SHOW TABLES;
②比较表结构:SHOW COLUMNS FROM table;(table置换成具体表名);
③比较表中数据SELECT * FROM table;
备注:数据量大的时候可以边查询边比较
# db_connect→由pymysql.connect()构建
def test_query1(self, db_connect):
# res→存放结果的字典,也是比较的依据
res = {}
# 列信息
t_col = []
# 表数据
t_val = []
sql = "SHOW tables"
result = self.sql_query(db_connect, sql)
res[0] = result
# 查询列信息
for i in result:
sql = f"SHOW COLUMNS FROM {i[0]}"
col = self.sql_query(db_connect, sql)
t_col.append(col)
# 查询表数据
for j in result:
sql = f"SELECT * FROM {j[0]};"
val = self.sql_query(db_connect, sql)
t_val.append(val)
res[1] = t_col
res[2] = t_val
db_connect.close()
return res
def sql_query(self, db_connect, sql):
# 创建游标
db_cursor = db_connect.cursor()
# 执行sql
db_cursor.execute(sql)
# 获取查询结果
result = db_cursor.fetchall()
# 关闭游标
db_cursor.close()
# 提交
db_connect.commit()
# 返回查询结果
return result
def db_compare(self, db_connect, db_connect1):
# 比较
if self.test_query1(db_connect) == self.test_query1(db_connect1):
print("数据库一致")