使用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