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

需求

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

代码实现

import pymysql



def 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_names

    except 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复制到数据库客户端执行即可。