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

 

转载于:https://www.cnblogs.com/or2-/p/3926048.html

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值