使用Excel管理数据库表
使用Excel管理数据库表,并使用python生成对应的建表语句
Excel 数据库表模版
python 生成SQL脚本
#coding=utf-8
from openpyxl import load_workbook
from db.column import Column
#读取excel的数据
def read_excel():
#打开一个workbook
wb = load_workbook(filename='aa.xlsx')
#获取所有表格(worksheet)的名字
sheets = wb.get_sheet_names()
#遍历每一个sheet,并且拿到worksheet对象
for i in range(len(sheets)):
sheet = wb.get_sheet_by_name(sheets[i])
if sheet['b1'].value == None:
continue
table_name = sheet['b1'].value
table_name_comment = sheet['e1'].value
table_sql = 'CREATE TABLE "' + table_name + '" (\n'
common_sql = ''
for rowNum in range(5, sheet.max_row + 1):
if (sheet.cell(row=rowNum, column=1).value != None):
table_sql += '\n\t'
common_sql += '\nCOMMENT ON COLUMN "' + table_name + '".'
if (sheet.cell(row=rowNum, column=2).value != None):
table_sql += '"' + sheet.cell(row=rowNum, column=2).value + '" '
common_sql += '"' + sheet.cell(row=rowNum, column=2).value + '" IS '
if (sheet.cell(row=rowNum, column=3).value != None):
db_type = sheet.cell(row=rowNum, column=3).value
if db_type == 'VARCHAR':
db_type = 'VARCHAR2'
table_sql += db_type
if (sheet.cell(row=rowNum, column=4).value != None):
db_type = sheet.cell(row=rowNum, column=3).value
if db_type == 'DATE':
table_sql += ','
else:
table_sql += '(' + str(sheet.cell(row=rowNum, column=4).value) + '),'
if (sheet.cell(row=rowNum, column=1).value != None):
common_sql += "'" + sheet.cell(row=rowNum, column=1).value + "';"
table_sql += '\n\tPRIMARY KEY ("SID")\n);'
print(table_sql)
common_sql += '\nCOMMENT ON TABLE "' + table_name + '" IS \'' + table_name_comment + '\';'
print(common_sql)
if __name__ == '__main__':
read_excel()
python从数据库生成sql脚本, 后续会调整优化,直接生成java代码,整合进代码生成工具 https://github.com/warriorg/builder