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参数，目的是为了扩展后面的事务处理。对于分布式的事务，这种方法是不适合的，但也只需要定义一个自己的事务类，这个事务包含参与事务的连接即可。
原文：http://blog.csdn.net/hawksoft/article/details/44702553