Python根据EXCEL生成ORACLE建表SQL

---

写在前面

• 个人博客首页
• 注：学习交流使用！

---

正文

需求

最近转到新的项目组，开发经理根据需求创建了表的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
  

小结

代码很简单、主要是写拼接Sql麻烦，需要其他添加逻辑，可以拿去修改代码。

网盘链接: https://pan.baidu.com/s/1986BHeeafBLvfxeW6pvB0g
提取码: gmbp