Python read Excel table Generate SQL statement first version

Because the unit designs the database tables, all use SQL. I don't know what the reason is not to powerdesign or Ermaster tools, to build a table is painful as a program ape of course to find a solution, write a program with Python to solve

Need to use XLRD Linux under sudo pip install xlrd

Mainly applicable to DB2 database

The Excel table structure where number is an incorrect field type does not know why colleagues set this up. There is error correction in the program, this program is to spell out the SQL statements.

__author__='c3t'#Coding:utf-8ImportxlrdImportRedata= 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.nrowsPrintNrowssql="CREATE TABLE"+ TableName +"(\ n" forRowNuminchRange (2, nrows): Row=table.row_values (rownum)ifRow andRowNum! = (nrows-1):        ifROW[1] = ="ID": Temp= Float (row[3]) SQL+ = Row[1] +" "+ row[2] +"("+ str (int (temp)) +") "+"PRIMARY key,\n"        Else: SQL+ = Row[1] +" "            ifRe.search ("deci.*", row[2]): SQL+=" "+ row[2]            elifROW[2] = =" Number"  andROW[3] = = 8: SQL+="int"            elifROW[2] = =" Number"  andROW[3] = = 1: SQL+="smallint"            elifROW[2] = =" Number"  andROW[3] > 10: SQL+="bigint"            elifROW[2] = ="DATETIME": SQL+="timestamp"            elifROW[2] = ="DATE": SQL+="Date"            Else: Temp= Float (row[3]) SQL+=" "+ row[2] +"("+ str (int (temp)) +") "            ifROW[4] = ="Y"  andROW[5] = ="Y": SQL+="Not NULL unique,\n"            elifROW[4] = ="Y"  andROW[5]! ="Y": SQL+="Not null,\n"            elifROW[4]! ="Y"  andROW[5]! ="Y": SQL+=", \ n"    Else: SQL+ = Row[1] +" "        ifRe.search ("deci.*", row[2]): SQL+=" "+ row[2]        Else: SQL+=" "+ row[2] +"("+ str (row[3]) +") "        ifROW[4] = ="Y"  andROW[5] = ="Y": SQL+="Not NULL unique,\n"        elifROW[4] = ="Y"  andROW[5]! ="Y": SQL+="Not null,\n"        elifROW[4]! ="Y"  andROW[5]! ="Y": SQL+="\ n)"PrintSql

Create Tablebh_business (IDVARCHAR( -)PRIMARY KEY, Busi_serial_noVARCHAR( -) not NULL UNIQUE, Busi_codeVARCHAR(Ten) not NULL, Branch_codeVARCHAR(Ten) not NULL, Teller_codeVARCHAR(Ten) not NULL, AMTDECIMAL( -,2) not NULL, CURRENCYVARCHAR(6) not NULL, Custom_lvl Number(1) not NULL, StateVARCHAR(2) not NULL, REMARKSVARCHAR( $), Weight_value Number(8) not NULL, Tmp_weight_value Number(8) not NULL, Urgent_flag Number(1) not NULL, Accp_timetimestamp   not NULL, Close_timetimestamp   not NULL, work_flow_idVARCHAR( $), Tmp_undo_flag Number(1) not NULL, sys_idVARCHAR(6) not NULL, MEDIUMVARCHAR(8) not NULL, crt_teller_idVARCHAR( -) not NULL, Crt_timetimestamp   not NULL, Crt_ipVARCHAR( -) not NULL, upd_teller_idVARCHAR( -), Upd_timetimestamp, Upd_ipVARCHAR(50.0)  )