一个简单的Python MVC框架(2)

import pymysql
import 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 uuid
class 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参数，目的是为了扩展后面的事务处理。对于分布式的事务，这种方法是不适合的，但也只需要定义一个自己的事务类，这个事务包含参与事务的连接即可。