一個簡單的Python MVC架構(2)

標籤：

2）資料庫訪問層
資料訪問層是與資料庫進行互動的介面，這裡我僅僅實現了mysql的提供者。下面是代碼：

import pymysqlimport os#用於測試class EmptyModel:      def __init__(self,name=None):            self.TableName=name#資料庫協助基類，相當於介面。其實對於python這種語言，這個類完全沒必要存在.#不過對於多種資料庫支援時，還是有點用.#注意：這裡的參數是格式化到字串，無法防止SQL注入。為了防止SQL注入，可以對SQL進行處理。#一般來說最好的辦法是真正參數化，但我看了一下pymysql,並沒有提供真正參數化的方法。class DbHelper:      def QueryByParam(self,sql,pms,EntityName,conn=None):          return []      def ExecuteCommand(self,sql,pms,conn=None):          return 0#Mysql的訪問類，基於pymysql.class DbHelperMySql(DbHelper):    def __init__(self,CharsetName=‘utf8‘):        self.CharsetName = CharsetName        if(self.CharsetName==‘‘):            self.CharsetName=‘utf8‘                  def ExecuteCommand(self,sql,pms,conn=None):        theNeedCloseConn = False        if(conn==None):            conn=self.GetMySqlDbConnDefault()            theNeedCloseConn = True        theCursor = conn.cursor()        try:            theCursor.execute("set NAMES "+self.CharsetName) #保證字元集正確.            theRet = theCursor.execute(sql,pms)        finally:            theCursor.close()            if theNeedCloseConn:                conn.close()        return theRet            @staticmethod    def GetMySqlDbConn(host,port,user,pwd,dbname):         return pymysql.connect(host=host, port=port,user=user, passwd=pwd, db=dbname)        @staticmethod    def GetMySqlDbConnDefault():        return DbHelperMySql.GetMySqlDbConn(‘127.0.0.1‘,3306,‘xxxxx‘,‘xxxxx‘,‘dbname‘)    #pms 為Dict類型.    def QueryByParam1(self,sql,pms,EntityName,conn=None):        theNeedCloseConn = False        if(conn==None):            conn=self.GetMySqlDbConnDefault()            theNeedCloseConn = True        theCursor = conn.cursor(pymysql.cursors.DictCursor)        try:            theCursor.execute("set NAMES "+self.CharsetName) #保證字元集正確.            theCursor.execute(sql,pms)            rows = theCursor.fetchall()            models=[]            for r in rows:                m=EmptyModel(EntityName)                for fld in r.items():                      setattr(m,fld[0],fld[1])                models.append(m)            return models        finally:            theCursor.close()            if theNeedCloseConn:                conn.close()        return []      #pms 為Dict類型.    def QueryByParam2(self,EntityType,sql,pms,conn=None):        theNeedCloseConn = False        if(conn==None):            conn=self.GetMySqlDbConnDefault()            theNeedCloseConn = True        theCursor = conn.cursor(pymysql.cursors.DictCursor)        try:            theCursor.execute("set NAMES "+self.CharsetName) #保證字元集正確.            theCursor.execute(sql,pms)            rows = theCursor.fetchall()            models=[]            for r in rows:                m=EntityType()                for fld in r.items():                      setattr(m,fld[0],fld[1])                      m.CurrFields.append(fld[0])                models.append(m)            return models        finally:            theCursor.close()            if theNeedCloseConn:                conn.close()        return []

沒有單獨做一個資料訪問層，實體訪問就放在業務基類中：
3）業務層基類：
主要提供實體類的增加，修改，刪除和查詢（單個和List),現在的應用模式，一般情況下不要緩衝實體。EF和Hibernate那種緩衝，其實都是災難的開始。給實體打狀態編輯，也僅僅是為了處理前面傳過來的實體集合可以分解成增加，修改和刪除。除此之外，實體狀態的維護也是沒必要的。

from DbOpts import *import uuidclass BusinessBase(object):    def __init__(self,EntityType):        self.EntityType = EntityType            def AddModel(self,model,conn=None):        theDb=DbHelperMySql()        theInsertSQL=model.GetInsertSQL()        theValues=[]        theFields = model.GetFields()        for theF in theFields:            theValues.append(model[theF])        theRet= theDb.ExecuteCommand(theInsertSQL,theValues,conn)        return theRet    def GetModels(self,sql,pms,conn=None):        theDb=DbHelperMySql()        return theDb.QueryByParam2(self.EntityType,sql,pms,conn)    def GetModel(self,pk,conn=None):        theTable = self.EntityType.TableName        theKeyField=self.EntityType.KeyField        theSQL = ‘SELECT * FROM ‘+theTable+‘ WHERE ‘+theKeyField+‘=‘+self.EntityType.FieldParams[theKeyField][‘DSFmt‘]        theParam=(pk)        theModels = self.GetModels(theSQL,theParam,conn)        if len(theModels)>0:            return theModels[0]        return None    def UpdateModel(self,model,conn=None):        theDb=DbHelperMySql()        theSQL=model.GetUpdateAllSQL()        theValues=[]        theFields = model.GetFields()        for theF in theFields:            theValues.append(model[theF])        theRet= theDb.ExecuteCommand(theSQL,theValues,conn)        return theRet    @staticmethod    def GetGuid():        return uuid.uuid1().__str__()    def GetDbTables(self,dbname,conn=None):        theSQL=‘select table_Comment as Script, table_Name as TableName,table_Type from INFORMATION_SCHEMA.tables where 1=1‘        if(dbname!=None and dbname !=‘‘):            theSQL += ‘ and table_schema=\‘‘+dbname+‘\‘‘        theDb=DbHelperMySql()        return theDb.QueryByParam1(theSQL,None,‘tables‘,conn)    def GetTabFields(self,dbname,tablename,conn=None):        theSQL =‘select a.table_name,a.column_name,a.data_type,a.is_nullable,           a.character_maximum_length as maxlengthb,a.character_octet_length as lengthb,           a.numeric_precision as precisionlen,a.numeric_scale as scalelen,            b.iskey            from information_schema.columns as a            left join ( select t.TABLE_NAME,c.COLUMN_NAME,1 as iskey from            INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,  INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c            WHERE  t.TABLE_NAME = c.TABLE_NAME  AND t.CONSTRAINT_TYPE = \‘PRIMARY KEY\‘ ) as b            on a.table_name =b.table_name and a.column_name=b.column_name            where 1=1‘        if(dbname!=None and dbname !=‘‘):            theSQL += ‘ and a.table_schema=\‘‘+dbname+‘\‘‘        if(tablename!=None and tablename !=‘‘):            theSQL += ‘ and a.table_name=\‘‘+tablename+‘\‘‘        theDb=DbHelperMySql()        return theDb.QueryByParam1(theSQL,None,‘columns‘,conn)

我在基本方法中增加了conn參數，目的是為了擴充後面的交易處理。對於分布式的事務，這種方法是不適合的，但也只需要定義一個自己的事務類，這個事務包含參與事務的串連即可。

一個簡單的Python MVC架構(2)