python读取excel表格生成sql语句 第一版

由于单位设计数据库表·，都用sql.不知道什么原因不用 powerdesign或者ermaster工具，建表很痛苦  作为程序猿当然要想办法解决，用Python写一个程序解决

 

需要用到 xlrd linux下 sudo pip install xlrd

主要是适用于db2数据库

excel 表结构 其中 number是不正确的字段类型 不知道同事为啥这么设置。这里程序里有纠错，这个程序就是将sql语句拼好。

 

__author__ = 'zhanglei'
# coding:utf-8

import xlrd
import re


data = 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.nrows
#print nrows
sql = "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:
            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)"

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)  
)