Python应用:自动生成数据库建表语句

一、普通生成

(一)、生成建表语句

• 使用Python批量自动生成Mysql中已有表的建表语句
• 从数据库的元数据中获取列、注释、索引等信息

import os

def generate_create_table_sql(cursor,file_path,table_name,schema):

    # 获取表的列信息
    cursor.execute(f"SHOW FULL COLUMNS FROM {
     table_name}")
    columns = cursor.fetchall()

    # 获取表的主键信息
    cursor.execute(f"SHOW KEYS FROM {
     table_name} WHERE Key_name = 'PRIMARY'")
    primary_keys = cursor.fetchall()

    #获取表的唯一约束信息
    cursor.execute(f"SHOW INDEX FROM {
     table_name} WHERE Non_unique = 0 AND Key_name != 'PRIMARY'")
    unique_constraints = cursor.fetchall()

    #获取表的索引信息
    cursor.execute(f"SHOW INDEX FROM {
     table_name} WHERE Key_name != 'PRIMARY' AND Non_unique != 0")
    indexes = cursor.fetchall()

    #获取表注释信息
    cursor.execute(f"SHOW TABLE STATUS LIKE '{
     table_name}'")
    table_status = cursor.fetchone()
    table_comment = table_status[17]

    # 生成建表语句
    create_table_sql = f"CREATE TABLE IF NOT EXISTS {
     schema}.{
     table_name} (\n"
    for column in columns:
        column_name = column[0]
        data_type = column[1]
        is_nullable = column[3]
        column_default = column[5]
        column_comment = column[8]

        # 判断是否为主键
        is_primary_key = column_name in [pk[4] for pk in primary_keys]

        # 构建列的字符串表示
        column_definition = f"    {
     column_name} {
     data_type}"

        if column_name.upper() == 'ID':
            column_definition += " AUTO_INCREMENT"

        # 添加是否为空约束
        if is_nullable == 'NO':
            column_definition += " NOT NULL"

        # 添加默认值
        if column_default is not None:
            column_definition += f" DEFAULT {
     column_default}"
            
        # 添加注释
        if column_comment:
            column_definition += f" COMMENT '{
     column_comment}'"

        # 添加主键约束
        if is_primary_key:
            column_definition += " PRIMARY KEY"

        create_table_sql += f"{
     column_definition},\n"

    #添加唯一约束,若唯一约束名相同,唯一约束应拼接在一起
    unique_constraints_dict = {
   }
    for constraint in unique_constraints:
        constraint_name = constraint[2]
        column_name = constraint[4]
        if constraint_name in unique_constraints_dict:
            unique_constraints_dict[constraint_name].append(column_name)
        else:
            unique_constraints_dict[constraint_name] = [column_name]

    for constraint_name,column_names in unique_constraints_dict.items():
        columns_str = ",".join(column_names)
        create_table_sql += f"    CONSTRAINT {
     constraint_name} unique ({
     columns_str}),\n"

    #添加索引
    indexes_dict = {