基于python自动批量生成表字段

需求

在业务开发之前，首先需要建表，很多系统要求表字段中加入create_time，update_time字段，如果表比较多的情况下，手动加入这些字段是一件令人头皮发麻的事情。

代码实现

import pymysqldef get_table_names(host, port, user, password, database):try:# 连接数据库connection = pymysql.connect(host=host, port=port, user=user, password=password, database=database)# 创建游标对象cursor = connection.cursor()# 获取所有表名cursor.execute("SHOW TABLES")# 提取表名列表tables = cursor.fetchall()table_names = [table[0] for table in tables]return table_namesexcept pymysql.Error as e:print(f"Error while connecting to MySQL: {e}")return []finally:# 关闭游标和数据库连接if cursor:cursor.close()if connection:connection.close()def generate_alter_table_sql(table_name, alterations):# table_name: 要修改的表名# alterations: 包含要进行的修改操作的列表，每个操作为一个字符串 SQL 语句sql_statements = []for alteration in alterations:sql = f"ALTER TABLE {table_name} {alteration};"sql_statements.append(sql)return sql_statements# MySQL数据库连接配置
host = 'XXXXX'
port = XXXX
user = 'XXXX'
password = 'XXXX'
database = 'XXXX'# 获取所有表名
table_names = get_table_names(host, port, user, password, database)
print("Tables in the database:", table_names)# 假设要给每个表添加相同的字段
alterations = ["ADD create_user_id VARCHAR(64) NULL COMMENT '创建人ID'","ADD create_time DATETIME DEFAULT CURRENT_TIMESTAMP NULL COMMENT '创建时间'","ADD update_user_id VARCHAR(64) NULL COMMENT '更新人'","ADD update_time DATETIME DEFAULT CURRENT_TIMESTAMP NULL ON update CURRENT_TIMESTAMP COMMENT '更新时间'"
]# 生成修改语句
print('-- -----')
for table_name in table_names:alter_sqls = generate_alter_table_sql(table_name, alterations)for sql in alter_sqls:print(sql)print('-- -----')

使用说明

# MySQL数据库连接配置
host = 'XXXXX'
port = XXXX
user = 'XXXX'
password = 'XXXX'
database = 'XXXX'

修改数据库配置为你自己的数据，执行脚本会在控制台打印出每张表的修改语句。把sql复制到数据库客户端执行即可。