一砌左、說明
近期工作中需要對(duì)Excel維護(hù)的數(shù)據(jù)庫表表結(jié)構(gòu)進(jìn)行Oracle的DDL整理悬蔽，故編寫如下腳本奕筐。
該腳本識(shí)別固定Excel文件岛啸，讀取每個(gè)sheet頁的數(shù)據(jù)庫表結(jié)構(gòu)信息后拼接成可執(zhí)行的DDL語句泪酱。
注意：控制臺(tái)輸出的DDL語句派殷，需要對(duì)數(shù)據(jù)類型和最后一個(gè)字段的逗號(hào)做統(tǒng)一替換處理。
二墓阀、邏輯
1毡惜、EXCEL格式
image.png
2、Python腳本
import xlrd
data_file = xlrd.open_workbook('tablesDDL.xlsx')
# 查看工作表
data_file.sheet_names()
# print("sheets：" + str(data_file.sheet_names()))
#遍歷所有工作表斯撮，獲取對(duì)應(yīng)的下標(biāo)位置
for num in range(len(data_file.sheet_names())):
#按照下標(biāo)讀取對(duì)應(yīng)工作表
sheet = data_file.sheet_by_index(num)
# 拼接建表語句
print("CREATE TABLE "+sheet.cell_value(1,0)+"."+sheet.cell_value(1,1)+"(")
count = 0
for i in range(sheet.nrows):
if count >= 1:
# row = sheet.row_values(i)
column_name = sheet.cell_value(i, 4)
data_type = sheet.cell_value(i,6)
data_type_length = sheet.cell_value(i,7)
print("\t"+column_name+" "+data_type+"("+str(data_type_length)[0:-2]+"),")
count += 1
print(");")
count2 = 0
#拼接字段注釋和表注釋
for j in range(sheet.nrows):
if count2 >= 1:
# row = sheet.row_values(i)
user_name = sheet.cell_value(1, 0)
table_name = sheet.cell_value(1, 1)
column_name = sheet.cell_value(j, 4)
column_comment = sheet.cell_value(j, 5)
print("COMMENT ON COLUMN "+user_name+"."+table_name+"."+column_name+" IS "+"'"+column_comment+"';")
count2 += 1
table_comment = sheet.cell_value(1, 2)
print("COMMENT ON TABLE "+user_name+"."+table_name+" IS "+"'"+table_comment+"';")
num += 1
即可在控制臺(tái)輸出對(duì)應(yīng)的DDL語句经伙。
