python读取ddl生成sql建表语句

发布时间 2023-03-24 09:14:34作者: 洺剑残虹
# 导入需要的库
import pandas as pd
import os

def read_ddl_create_tab_sql(file_path:str,table_name:str):
    df = pd.read_csv(file_path,sep='\|\@\|',index_col=0,header=None,encoding='utf-8',engine='python')

    inds,cols = df.shape
    # print(df.head())

    df = df.fillna(0)

    print(df.head())

    # 循环生成Oracle建表语句

 
    table_definition = ''
    col_commit = ''
    for ind in range(inds):
        col_name = df.iloc[ind,0]
        col_type = df.iloc[ind,1]
        col_length_0  = df.iloc[ind,2]
        col_length_1 = df.iloc[ind, 3]
        not_null = df.iloc[ind,4]
        is_pk = df.iloc[ind,5]
        col_comments = df.iloc[ind,6]

        if col_type == 'DATE':
            table_definition += col_name + " " + col_type + (' NOT NULL' if is_pk == 'Y' else '')  + ('' if ind == inds-1 else ",\n")

        if col_type == "VARCHAR2":
            table_definition += col_name + " " + col_type + '({})'.format(int(col_length_0)) + (' NOT NULL' if is_pk == 'Y' else '') + \
                                ('' if ind == inds - 1 else ",\n")

        if col_type == 'NUMBER':
            col_type_num = ''
            if col_length_0 >0 and col_length_1 >0:
                col_type_num = '({},{})'.format(int(col_length_0), int(col_length_1))
            elif col_length_0 > 0 and col_length_1 == 0:
                col_type_num = '({})'.format(col_length_0)
            else:
                col_type_num = ''

            table_definition += col_name + " " + col_type + \
                                col_type_num + \
                                (' NOT NULL ' if is_pk == 'Y' else '') + \
                                ('' if ind == inds-1 else ",\n")
        col_commit = col_commit +  "comment on column "+table_name+"." + col_name+ " is '{}'".format(col_comments) + ";\n"
    # 输出建表语句
    drop_tab_sql = 'DROP TABLE {} PURGE;'.format(table_name)
    create_sql_str = drop_tab_sql + '\n' + 'CREATE TABLE ' + table_name + ' (' + table_definition + ') TABLESPACE TSODSDAT;\n' + col_commit
    # print(create_sql_str)


    with open("./create_tab_sql/{}.sql".format(table_name), 'w',encoding='utf-8') as f:
        f.write(str_s)

    return create_sql_str


def del_file(dir):
    for f in os.listdir(dir):
        os.remove(os.path.join(dir, f))

# 读取数据文件
file_path = './ddl_data/'

del_file("./create_tab_sql/")
str_s = ''
for etm in os.listdir(file_path):
    if etm.split('.')[1] == 'ddl' and '_TX_' in etm:
        table_name,name_suffix = etm.split("_D_")
        str_s = str_s + read_ddl_create_tab_sql(file_path+etm,table_name) + '\n\n'


# os.remove("./create_tab_sql/create_table_sql.sql")
with open("./create_tab_sql/create_table_sql.sql",'w',encoding='utf-8') as f:
    f.write(str_s)