首页 > 代码库 > python读取excel表格生成sql语句 第一版

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

2024-07-18 16:17:13   218人阅读

由于单位设计数据库表·，都用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)  )
 






























        

            
                
                  
                
                

                    联系

                    我们