python读取excel表格生成sql语句 第一版
时间:2022-03-13 22:54
由于单位设计数据库表·,都用sql.不知道什么原因不用 powerdesign或者ermaster工具,建表很痛苦 作为程序猿当然要想办法解决,用Python写一个程序解决
需要用到 xlrd linux下 sudo pip install xlrd
主要是适用于db2数据库
excel 表结构 其中 number是不正确的字段类型 不知道同事为啥这么设置。这里程序里有纠错,这个程序就是将sql语句拼好。
__author__ = ‘c3t‘ # 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: 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) )
python读取excel表格生成sql语句 第一版,布布扣,bubuko.com