Python根据EXCEL生成ORACLE建表SQL

写在前面

CSDN博客首页
注：学习交流使用！

正文

需求

最近转到新的项目组，开发经理根据需求创建了表的word版本，由于一个表字段就几十个，手动去写sql太麻烦了，就想着好久没用python了，写个脚本生成一下，给自己加点活儿！

具体实现

1. 公司建表规范SQL展示

declare
	TABLE_EXISTS EXCEPTION;
	PRAGMA EXCEPTION_INIT (TABLE_EXISTS , -955);
begin
	execute immediate '
CREATE TABLE FSP_EAS.XXX
(
	ID		NUMBER(15)	 NOT NULL,
	VERSION		NUMBER(15),
	CREATED_BY		NUMBER(15),
	CREATION_DATE		DATE,
	LAST_UPDATED_BY		NUMBER(15),
	LAST_UPDATED_DATE		DATE,
	ENABLED_FLAG		VARCHAR2(1),
	CONSTRAINT PK_DMS_DELIVERY_IMG PRIMARY KEY (ID)
	)
	';
	EXCEPTION WHEN TABLE_EXISTS THEN NULL;
end;
/
comment on column FSP_EAS.XXX.ID is '主键ID';
comment on column FSP_EAS.XXX.VERSION is '版本号';
comment on column FSP_EAS.XXX.CREATED_BY is '创建人';
comment on column FSP_EAS.XXX.CREATION_DATE is '创建时间';
comment on column FSP_EAS.XXX.LAST_UPDATED_BY is '最后更新人';
comment on column FSP_EAS.XXX.LAST_UPDATED_DATE is '最后更新时间';
comment on column FSP_EAS.XXX.ENABLED_FLAG is '是否启用';
BEGIN
	INSERT INTO FSP_EAS.SYS_ID_GEN (id, gen_name, gen_value)
	SELECT (SELECT (NVL(MAX(ID), 0) + 1) FROM FSP_EAS.SYS_ID_GEN),'seq_dms_delivery_img', 1
	FROM DUAL
	WHERE NOT EXISTS (SELECT 1 FROM FSP_EAS.SYS_ID_GEN  WHERE gen_name = 'seq_dms_delivery_img');
	commit;
END;
/

2. Excel设计

表名不长，可以以sheet名用来写表名，太长了就直接用第一行第二列作为表名填充位置。
主要分三列，字段名、字段类型、字段描述（需要可加是否必填等）

3. 生成过程

读取Excel，并解析分块生成，最后写入txt文件（需要用到openpyxl库）

def main():
    wb = openpyxl.load_workbook("in.xlsx")
    #获取workbook中所有的表格
    sheets = wb.get_sheet_names()
    
    print(sheets)
    
    #循环遍历所有sheet
    for i in range(len(sheets)):
        sheet= wb.get_sheet_by_name(sheets[i])
        coloumNames = []
        coloumTypes=[]
        coloumComments=[]
        table  = sheet.cell(1,2).value
        tableUser  = table.split('.')[0]
        tableName  = table.split('.')[1]
        print('\n\n第'+str(i+1)+'个sheet: ' + sheet.title+'->>>')
        #字段名称
        for r in range(3,sheet.max_row+1):
            coloumName = sheet.cell(row=r,column=1).value
            coloumNames.append(coloumName)
        #字段类型
        for r in range(3,sheet.max_row+1):
            coloumType = sheet.cell(row=r,column=2).value
            coloumTypes.append(coloumType)
        #字段描述
        for r in range(3,sheet.max_row+1):
            coloumComment = sheet.cell(row=r,column=3).value
            coloumComments.append(coloumComment)
        commentSql = initComment(table,coloumNames,coloumComments)
        tableSql  = initTable(table,coloumNames,coloumTypes)
        endSql = ("\nBEGIN\n\tINSERT INTO FSP_EAS.SYS_ID_GEN (id, gen_name, gen_value)\n\t"
            "SELECT (SELECT (NVL(MAX(ID), 0) + 1) FROM FSP_EAS.SYS_ID_GEN),'seq_" + tableName.lower()+ "', 1\n\t"
            "FROM DUAL\n\t"
            "WHERE NOT EXISTS (SELECT 1 FROM FSP_EAS.SYS_ID_GEN  WHERE gen_name = 'seq_"+ tableName.lower()+"');"
            "\n\tcommit;\nEND;\n\n/")
        totoleSql = tableSql + commentSql + endSql
        print(tableSql + commentSql)
        #此处生成sql文件会中文乱码，需手动更改文件类型
        with open(table+".txt",mode = "w") as f:
             f.write(totoleSql)

创建表格SQL生成

def initTable(table,coloumNames,coloumTypes):
    #分解 形成 table sql 
    columnCommentDic=dict(zip(coloumNames,coloumTypes))
    startSql = "declare\n\tTABLE_EXISTS EXCEPTION;\n\tPRAGMA EXCEPTION_INIT (TABLE_EXISTS , -955);\nbegin\n\texecute immediate '\nCREATE TABLE " + table +"\n(\n"
    midSql = "\t"
    for k,v in columnCommentDic.items():
        if(k == "ID"):
            midSql = midSql + k + "\t\t" + v +"\t NOT NULL," + "\n" +"\t"
        else:
            midSql = midSql + k + "\t\t" + v + "," + "\n" +"\t"
    		endSql = "CONSTRAINT PK_"+ table.split(".")[1] + " PRIMARY KEY 	(ID)\n\t"+")\n\t';\n"+"\tEXCEPTION WHEN TABLE_EXISTS THEN NULL;\n" +"end;\n" + "/\n\n"
    	return startSql + midSql  + endSql

创建注释SQL生成

#分解 形成 comment sql 入参：表名，字段名，字段描述
def initComment(table,coloumNames,coloumComments):
    columnCommentDic=dict(zip(coloumNames,coloumComments))
    ourStr = ""
    for k,v in columnCommentDic.items():
        ourStr = ourStr + "comment on column " + table +"." + k + " is '" + v + "';" + "\n\n"
    return ourStr