物理模型Excel数据逆向生成SQL – DDL – python脚本

现在有物理模型的Excel，将这些Excel转成创建表的DDL，然后反向生成PDM！

import pandas as pd

# 读取Excel文件
df = pd.read_excel('/Users/zanglikun/Desktop/反向生成数据.xlsx')

current_table_code = None
create_table_sql = ""
previous_table_code = None
previous_table_name = None

for index, row in df.iterrows():
    table_code = row['表代码']
    table_name = row['表名称']
    field_code = row['字段代码']
    field_name = row['字段名']
    field_comment = row['字段注释']
    data_type = row['数据类型']
    data_length = row['数据长度']
    is_primary_key = row['是否主键(是/否)']

    if current_table_code != table_code:
        # 如果遇到新表，先输出上一张表的创建表SQL
        if create_table_sql:
            # 输出删除表的SQL，使用上一个表的表名
            drop_table_sql = f"DROP TABLE IF EXISTS {previous_table_code};"
            print(drop_table_sql)
            print(create_table_sql.rstrip(",\n") + f"\n) COMMENT='{previous_table_name}';")

        # 开始新表的创建表SQL
        create_table_sql = f"CREATE TABLE {table_code} (\n"
        current_table_code = table_code
        previous_table_code = table_code
        previous_table_name = table_name

    # 处理字段长度为NaN的情况
    if pd.isnull(data_length):
        field_info = data_type
    else:
        field_info = f"{data_type}({data_length})"

    # 添加字段信息到创建表SQL
    create_table_sql += f"    {field_code} {field_info} COMMENT '{field_comment}'"
    if is_primary_key.lower() == '是':
        create_table_sql += " PRIMARY KEY"
    create_table_sql += ",\n"

# 输出最后一张表的创建表SQL
if create_table_sql:
    drop_table_sql = f"DROP TABLE IF EXISTS {previous_table_code};"
    print(drop_table_sql)
    print(create_table_sql.rstrip(",\n") + f"\n) COMMENT='{previous_table_name}';")

这个SQL可以直接导入PowerDesigner，逆向生成PDM文件！