python讀取excel表格產生sql語句 第一版

標籤：des   style   blog   http   color   os   資料   for   

由於單位設計資料庫表·，都用sql.不知道什麼原因不用 powerdesign或者ermaster工具，建表很痛苦  作為程式猿當然要想辦法解決，用Python寫一個程式解決

 

需要用到 xlrd linux下 sudo pip install xlrd

主要是適用於db2資料庫

excel 表結構 其中 number是不正確的欄位類型 不知道同事為啥這麼設定。這裡程式裡有錯誤修正，這個程式就是將sql語句拼好。

 

__author__ = ‘c3t‘# coding:utf-8import xlrdimport redata = xlrd.open_workbook("1.xlsx")table = data.sheets()[0]temp = table.row_values(0)[0]tableName = re.findall("[A-Z].*\w+", temp)[0]nrows = table.nrowsprint nrowssql = "create table " + tableName + "( \n"for rownum in range(2, nrows):    row = table.row_values(rownum)    if row and rownum != (nrows - 1):        if row[1] == "ID":            temp = float(row[3])            sql += row[1] + " " + row[2] + "(" + str(int(temp)) + ") " + "PRIMARY KEY,\n"        else:            sql += row[1] + " "            if re.search("DECI.*", row[2]):                sql += " " + row[2]            elif row[2] == "NUMBER" and row[3] == 8:                sql += " int "            elif row[2] == "NUMBER" and row[3] == 1:                sql += " smallint "            elif row[2] == "NUMBER" and row[3] > 10:                sql += "bigint"            elif row[2] == "DATETIME":                sql += " timestamp "            elif row[2] == "DATE":                sql += " date "            else:                temp = float(row[3])                sql += " " + row[2] + "(" + str(int(temp)) + ") "            if row[4] == "Y" and row[5] == "Y":                sql += " NOT NULL UNIQUE,\n"            elif row[4] == "Y" and row[5] != "Y":                sql += " NOT NULL,\n"            elif row[4] != "Y" and row[5] != "Y":                sql += ",\n"    else:        sql += row[1] + " "        if re.search("DECI.*", row[2]):            sql += " " + row[2]        else:            sql += " " + row[2] + "(" + str(row[3]) + ") "        if row[4] == "Y" and row[5] == "Y":            sql += " NOT NULL UNIQUE,\n"        elif row[4] == "Y" and row[5] != "Y":            sql += " NOT NULL,\n"        elif row[4] != "Y" and row[5] != "Y":            sql += " \n)"print sql

 

 

 

create table BH_Business( ID VARCHAR(64) PRIMARY KEY,BUSI_SERIAL_NO  VARCHAR(50)  NOT NULL UNIQUE,BUSI_CODE  VARCHAR(10)  NOT NULL,BRANCH_CODE  VARCHAR(10)  NOT NULL,TELLER_CODE  VARCHAR(10)  NOT NULL,AMT  DECIMAL(14,2) NOT NULL,CURRENCY  VARCHAR(6)  NOT NULL,CUSTOM_LVL  NUMBER(1)  NOT NULL,STATE  VARCHAR(2)  NOT NULL,REMARKS  VARCHAR(200) ,WEIGHT_VALUE  NUMBER(8)  NOT NULL,TMP_WEIGHT_VALUE  NUMBER(8)  NOT NULL,URGENT_FLAG  NUMBER(1)  NOT NULL,ACCP_TIME  timestamp  NOT NULL,CLOSE_TIME  timestamp  NOT NULL,WORK_FLOW_ID  VARCHAR(200) ,TMP_UNDO_FLAG  NUMBER(1)  NOT NULL,SYS_ID  VARCHAR(6)  NOT NULL,MEDIUM  VARCHAR(8)  NOT NULL,CRT_TELLER_ID  VARCHAR(50)  NOT NULL,CRT_TIME  timestamp  NOT NULL,CRT_IP  VARCHAR(50)  NOT NULL,UPD_TELLER_ID  VARCHAR(50) ,UPD_TIME  timestamp ,UPD_IP  VARCHAR(50.0)  )