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

• 问题描述
• 解决方案
• mysql-utilities
• 参考文献

开发过程中有多个测试环境，测试环境 A 加了字段，测试环境 B 忘了加，字段名对不上，同一项目就报错了

创建数据库和表

CREATE DATABASE `a` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `b` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

USE `a`;
CREATE TABLE `student` (
  `id` int(11) AUTO_INCREMENT,
  `class_id` int(11) DEFAULT NULL,
  `name` varchar(255),
  `birthday` date,
  `chinese` int(11),
  `math` int(11),
  `english` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

USE `b`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `chinese` int(11) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  `english` int(11) DEFAULT NULL,
  `physics` int(11) DEFAULT NULL,
  `chemistry` int(11) DEFAULT NULL,
  `biology` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_class_id` (`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

安装

pip install SQLAlchemy
pip install pymysql

代码

from collections import defaultdict

import sqlalchemy
from sqlalchemy.engine.reflection import Inspector


def get_table_column_map(inspector: Inspector):
    """获取数据库中所有表对应的字段"""
    table_column_map = defaultdict(set)
    table_names = inspector.get_table_names()
    for table_name in table_names:
        columns = inspector.get_columns(table_name)  # 字段信息
        # indexes = inspect.get_indexes(table_name)  # 索引信息
        for column in columns:
            table_column_map[table_name].add(column['name'])
    return table_column_map


def compare_table_column_difference(a, b):
    """对比同名表字段的差异"""
    table_names = set(a) & set(b)  # 同名表
    for table_name in table_names:
        columns_a = a[table_name]
        columns_b = b[table_name]
        difference = columns_a ^ columns_b
        if difference:
            print(table_name, difference)


engine = sqlalchemy.create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/a')
engine1 = sqlalchemy.create_engine('mysql+pymysql://root:123456@127.0.0.1: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 中的 mysqldiff

文档

mysqldiff --help

命令

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

例子

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

效果

--- a.student
+++ b.student
@@ -3,8 +3,13 @@
   `class_id` int(11) DEFAULT NULL,
   `name` varchar(255) DEFAULT NULL,
   `birthday` date DEFAULT NULL,
+  `gender` tinyint(4) DEFAULT NULL,
   `chinese` int(11) DEFAULT NULL,
   `math` int(11) DEFAULT NULL,
   `english` int(11) DEFAULT NULL,
-  PRIMARY KEY (`id`)
+  `physics` int(11) DEFAULT NULL,
+  `chemistry` int(11) DEFAULT NULL,
+  `biology` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `idx_class_id` (`class_id`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

优点：比较内容更详尽

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

1. 将数据表定义DDL转peewee的Model定义
2. Python根据数据表定义自动生成代码
3. Python内置类型集合set和frozenset
4. mysql-utilities GitHub
5. mysql表结构对比工具mysqldiff