python制作表格的语句_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';