python根据Excel自动生成创建表sql语句

1、Excel格式截图

 

 2、配置文件脚本

# coding:utf-8
# 表英文名
table_name = '表英文名'
# 表中文名
table_comments = '表中文名'
# Excel路径
input_data_path = './data'
# 导出sql路径
output_sql_path = './outputSql'
# 表空间
table_space = 'TSDAT'
table_space_comments = '''
tablespace {}
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 320K
    next 1M
    minextents 1
    maxextents unlimited
  );\n'''.format(table_space)

3、生成SQL脚本

# conding:utf-8
from typing import List

from configs.conf import *
import pandas as pd
import os

def reader_file_path(path:str):
    abs_path = os.path.abspath(path)
    in_path = os.listdir(path)
    return list(map(lambda p:os.path.join(abs_path,p),in_path))

data_path = reader_file_path(input_data_path)
print(data_path)

data = pd.DataFrame()

# 主体
sql = '''create table {} \n(\n'''.format(table_name)
# 表名中文名注释
sql_table_comments = '''comment on table ''' + table_name + ' is \'' + table_comments + '\';\n'
# 字段注释
sql_column_comments = 'comment on column {}.{} is \'{}\';\n'

if data_path.__len__()>0:
    data = pd.read_excel(data_path[0],header=0)
    n,m = data.shape
    for i in range(n):

        sql_table_comments += sql_column_comments.format(table_name, data.iloc[i, 2], data.iloc[i, 3])
        if i != (n-1):
            sql = sql + ' ' + data.iloc[i,2] + '    ' + data.iloc[i,4] + ',\n'
        else:
            sql = sql + ' ' + data.iloc[i, 2] + '    ' + data.iloc[i, 4] + '\n)'
else:
    print('==》没有数据文件！')
    exit(0)
print(data.head())

# 创建语句+ 表空间 + 表注释
sql = sql + table_space_comments + sql_table_comments

out_sql_name = table_name + '.sql'
with open(os.path.join(output_sql_path,out_sql_name),'w',encoding='utf-8') as f:
    f.write(sql)

4、生成的sql语句

create table T1E_ETL_CODE_MAP 
(
 COLUMN1    VARCHAR2(100),
 COLUMN2    VARCHAR2(101),
 COLUMN3    VARCHAR2(102),
 COLUMN4    VARCHAR2(103),
 COLUMN5    VARCHAR2(104),
 COLUMN6    VARCHAR2(105),
 COLUMN7    VARCHAR2(106),
 COLUMN8    VARCHAR2(107),
 COLUMN9    VARCHAR2(108),
 COLUMN10    VARCHAR2(109),
 COLUMN11    VARCHAR2(110),
 COLUMN12    DATE
)
tablespace TSDAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 320K
    next 1M
    minextents 1
    maxextents unlimited
  );
comment on table T1E_ETL_CODE_MAP is '交易码表';
comment on column T1E_ETL_CODE_MAP.COLUMN1 is '字段1';
comment on column T1E_ETL_CODE_MAP.COLUMN2 is '字段2';
comment on column T1E_ETL_CODE_MAP.COLUMN3 is '字段3';
comment on column T1E_ETL_CODE_MAP.COLUMN4 is '字段4';
comment on column T1E_ETL_CODE_MAP.COLUMN5 is '字段5';
comment on column T1E_ETL_CODE_MAP.COLUMN6 is '字段6';
comment on column T1E_ETL_CODE_MAP.COLUMN7 is '字段7';
comment on column T1E_ETL_CODE_MAP.COLUMN8 is '字段8';
comment on column T1E_ETL_CODE_MAP.COLUMN9 is '字段9';
comment on column T1E_ETL_CODE_MAP.COLUMN10 is '字段10';
comment on column T1E_ETL_CODE_MAP.COLUMN11 is '字段11';
comment on column T1E_ETL_CODE_MAP.COLUMN12 is '字段12';