【工具】代碼生成器-python腳本

我覺得造輪子這件事情，是誰都可以做的。隻不過做得好或者不好而已，用心了做得就要優雅一點。

之前用過java的代碼生成器，什麼pojodobodbo都能生成，于是我也來自己造一個輪子。

造輪子的事情是沒必要做得，費神費心，還沒人家做得好，那麼我還是要做，就當是體驗一把了，看看細節是怎麼實作的。

前期準備：

1. 一台裝有python、mysql的機器和若幹待生成的表。
2. python版本：3.6.4
3. python安裝mysql子產品：pip install pymysql。（python2安裝：pip install mysql-python
4. 目智語言：java

待生成表格：為了實作各種資料類型，我們定義一個包含多種資料類型的實體表t_model，資料結構如下。

drop table if exists t_model;
create table t_model(
	f_id varchar(64) primary key not null, --varchar 主鍵
	f_number int null,
	f_datetime datetime,
	f_double double
)      

　　

目标格式：

package com.dyi.po;

import java.util.Date;
/**
 * 表t_model模型
 * @author WYB
 *
 */
public class Model {
	private String id;
	private int number;
	private Date date;
	private double dble;
	
	
	
	public Model() {
		super();
	}
	public Model(String id, int number, Date date, double dble) {
		super();
		this.id = id;
		this.number = number;
		this.date = date;
		this.dble = dble;
	}
	/**
	 * 
	 * @return
	 */
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	/**
	 * 
	 * @return
	 */
	public int getNumber() {
		return number;
	}
	public void setNumber(int number) {
		this.number = number;
	}
	/**
	 * 
	 * @return
	 */
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	/**
	 * 
	 * @return
	 */
	public double getDble() {
		return dble;
	}
	public void setDble(double dble) {
		this.dble = dble;
	}
	
}
      

開始編寫腳本

第一步：查詢表結構

sql = """
      select 
      column_name,data_type,character_maximum_length,column_key,column_comment
      from information_schema.`COLUMNS` 
      where  TABLE_NAME = "%s"
      """%tableName
    cursor.execute(sql)
    tableColumnList = cursor.fetchall()
      

第二步：分析列的類型

cursor.execute(sql)
    tableColumnList = cursor.fetchall()

    modelName = tableName
    modelName = modelName[modelName.find("_") + 1:]
    modelName = modelName[0].upper()+modelName[1:]
    fieldInfoList = []
    for col in tableColumnList:
        colName = col[0]
        colType = col[1].lower()
        colLen = col[2]
        priKey = col[3]
        comment = col[4]
      

第三步：拆分字段名，處理細節，生成代碼

import pymysql

##連接配接資料庫
db = pymysql.connect("localhost","root","root","stagebo")
cursor = db.cursor()

def log(str):
    print(str)

def getTableList():
    log("開始查詢所有資料表...")
    cursor.execute("show tables")
    tableList = cursor.fetchall()
    tList = []
    for t in tableList:
        tList.append(t[0])
    return tList



def getTableInfo(tableName):
    log("開始擷取表結構")
    sql = """
    select 
     column_name,data_type,character_maximum_length,column_key,column_comment
    from information_schema.`COLUMNS` 
    where  TABLE_NAME = "%s"
    """%tableName
    cursor.execute(sql)
    tableColumnList = cursor.fetchall()

    modelName = tableName
    modelName = modelName[modelName.find("_") + 1:]
    modelName = modelName[0].upper()+modelName[1:]
    fieldInfoList = []
    for col in tableColumnList:
        colName = col[0]
        colType = col[1].lower()
        colLen = col[2]
        priKey = col[3]
        comment = col[4]
        #字段去掉“f_”
        colName = colName[colName.find("_")+1:]
        #colName = colName[0].upper()+colName[1:]
        #判斷類型
        type = ""
        if colType in ["varchar","nvarchar"]:
            type = "String"
        elif colType == "int":
            type = "int"
        elif colType in ["double","float"]:
            type = "double"

        pk = False
        if priKey == "PRI":
            pk = True
        fieldInfoList.append([colName,type,pk])

    file = open("%s.java"%modelName, "w")
    code = """
    package com.dyi.po;

    import java.util.*;

    /**
     * 表%s模型
     *
     */

    """ %tableName

    code += "public class %s {"%modelName
    for item in fieldInfoList:
        code += """  
          
        private %s %s; """%(item[1],item[0])

    code +="""
    
        /*
         * 空構造函數
         */
        public %s(){
           super();
        }
            
    """%modelName

    code += """    
        /**
         *全參數構造函數
         */
        public %s("""%modelName
    for item in fieldInfoList:
        code += "%s %s, "%(item[1],item[0])
    code = code[:-1]
    code += """) {
            super();"""
    for item in fieldInfoList:
        code += """
            this.%s = %s;"""%(item[0],item[0])

    code += """
        }"""

    for item in fieldInfoList:
        t = item[1]
        n = item[0]
        nu = n[0].upper()+n[1:]
        code += """
        
            /**
             *
             * @return
             */
            public %s get%s(){
                return this.%s;
            }
            public void set%s(%s %s){
                this.%s = %s;
            }
        """%(t,nu,n,nu,t,n,n,n)
    code += "}"
    file.write(code)
    file.flush()
    file.close()

if __name__ == "__main__":
    #查詢表
    tableList = getTableList()

    #定義要導出的表
    tableToScript = ["t_model"]

    #開始周遊
    for tableName in tableToScript:
        if tableName not in tableList:
            continue
        print(tableName)
        getTableInfo(tableName)
      

　　結果展示

package com.dyi.po;

    import java.util.*;

    /**
     * 表t_model模型
     *
     */

    public class Model {  
          
        private String id;   
          
        private int number;   
          
        private  date;   
          
        private double dble; 
    
        /*
         * 空構造函數
         */
        public Model(){
           super();
        }
            
        
        /**
         *全參數構造函數
         */
        public Model(String id, int number,  date, double dble,) {
            super();
            this.id = id;
            this.number = number;
            this.date = date;
            this.dble = dble;
        }
        
            /**
             *
             * @return
             */
            public String getId(){
                return this.id;
            }
            public void setId(String id){
                this.id = id;
            }
        
        
            /**
             *
             * @return
             */
            public int getNumber(){
                return this.number;
            }
            public void setNumber(int number){
                this.number = number;
            }
        
        
            /**
             *
             * @return
             */
            public  getDate(){
                return this.date;
            }
            public void setDate( date){
                this.date = date;
            }
        
        
            /**
             *
             * @return
             */
            public double getDble(){
                return this.dble;
            }
            public void setDble(double dble){
                this.dble = dble;
            }
        }
      

　　然後流程就通了，一通百通，别的就可以照舊了~~~

黑夜給了我黑色的眼睛，我卻用它尋找光明