adodbapi orm对象查询(四)

Twisted 项目ORM 最终版本，

没有延迟加载，数据字段映射，简单就是美，查询出来的SQL就是对象直接传输到客户端，引用方便，

而且不会因为对象过大而无法传输到客户端

（开始的时候没有将数据分页保存，而是将ADO的recordset 放到一个对象里面保存，

结果客户端里PB反序列化出来直接是 一个字符串 “too big”类似的，

但是原先我用sqlobject 查询的时候 同样是3000用户却可以返回，

貌似PB对单个对象的大小有所限制，所以我在ResultObj将Recoerdset 分页保存了，

这里暂时没有具体去研究）

这个是项目中实际用到的

# -*- coding: gbk -*-
#!/bin/env python
# Name:         EtSafeFile.py
# Author:       许刚
# Created:      2012/7/20
from common import Ip2Int, LogErr,GBK,PrepareDir
import adodbapi, os
from twisted.spread import pb
from db import Cfg
AvailablePeopleNum=3000
########################################################################
#decorators
def Query(fn):
	"""查询装饰器"""
	def wrapper(*args, **kv):
		conn=connect()
		kv['conn']=conn
		try:
			return fn(*args, **kv)
		except:
			LogErr()
			raise
		finally:
			conn.close()
	return wrapper

def Execute(fn):
	"""增加 删除 修改 装饰器"""
	def wrapper(*args, **kv):
		conn=connect()
		kv['conn']=conn
		try:
			result= fn(*args, **kv)
			conn.commit()
			return result
		except:
			conn.rollback()
			LogErr()
			raise
		finally:
			conn.close()
	return wrapper





#try catch
def Try(fn):
	def wrapper(*args, **kv):
		try:
			return fn(*args, **kv)
		except:
			LogErr()
	return wrapper
########################################################################
#data row
class Row(pb.Copyable):
	def __init__(self,entries): 
		self.__dict__.update(entries)
#tran objects
########################################################################
class ResultObj(pb.Copyable):
	#字符串太长pb无法 传输，转成列表，原理跟FilePager一样
	def __init__(self,rs,PageNo=1,pageSize=300,filedDict=None):
		self.pageSize=pageSize	
		if rs.RecordCount:
			rs.AbsolutePage=PageNo
		self.pageNo=PageNo 
		rs.PageSize=self.pageSize
		self.pageCount=rs.PageCount
		if rs.RecordCount:
			self.rs=rs.GetRows(self.pageSize)
		self.field_num=rs.Fields.Count
		self.next=None
		self.count=self.pageSize

		if self.pageNo<self.pageCount:
			self.next=ResultObj(rs,PageNo+1,self.pageSize,filedDict)
		else:
			self.count=rs.RecordCount-(self.pageNo-1)*self.pageSize
		#查询结果的每一列 生成字典
		if filedDict is None:
			self.filedDict={}
			for n in xrange(self.field_num):
				self.filedDict[n]=rs.Fields.Item(n).Name.lower()
				#没有列明转成 c0 c1.....
				if not self.filedDict[n]:
					self.filedDict[n]='c'+str(n)
		else:
			self.filedDict=filedDict

	def __len__(self):
		return self.count

	def __getitem__(self,key):       	
		row={}
		for cur_col in xrange(self.field_num):
			row[self.filedDict[cur_col]]=self.rs[cur_col][key]
		return Row(row)					
		
	def __iter__(self):
		return iter(self.__next__())
	
	#遍历列表
	def __next__(self):
		fiedlcount=xrange(self.field_num)
		while self:
			for cur_row in xrange(self.count):
				row={}
				for cur_col in fiedlcount:
					row[self.filedDict[cur_col]]=self.rs[cur_col][cur_row]
				yield Row(row)					
			self=self.next
########################################################################
class ResultDict(ResultObj,pb.Copyable):
	def __init__(self,rs,PageNo=1,pageSize=300,filedDict=None):
		ResultObj.__init__(self,rs,PageNo=1,pageSize=300,filedDict=None)	
	def __getitem__(self,key):       	
		row={}
		for cur_col in xrange(self.field_num):
			row[self.filedDict[cur_col]]=self.rs[cur_col][key]
		return row		
	#遍历列表
	def __next__(self):
		fiedlcount=xrange(self.field_num)
		while self:
			for cur_row in xrange(self.count):
				row={}
				for cur_col in fiedlcount:
					row[self.filedDict[cur_col]]=self.rs[cur_col][cur_row]
				yield row					
			self=self.next
########################################################################
class ResultArray(ResultObj,pb.Copyable):
	def __init__(self,rs,PageNo=1,pageSize=300,filedDict=None):
		ResultObj.__init__(self,rs,PageNo=1,pageSize=300,filedDict=None)
	
	def __getitem__(self,key):       	
		row=[]
		for cur_col in xrange(self.field_num):
			row.append(self.rs[cur_col][key])
		return row		
	
	#遍历列表
	def __next__(self):
		fiedlcount=xrange(self.field_num)
		while self:
			for cur_row in xrange(self.count):
				row=[]
				for cur_col in fiedlcount:
					row.append(self.rs[cur_col][cur_row])
				yield tuple(row)					
			self=self.next
########################################################################
class RemoteFile(pb.Copyable):
	def __init__(self,entries): 
		self.__dict__.update(entries)
########################################################################
class Message(pb.Copyable):
	def __init__(self,sender,receiver,fileitem,level):
		self.sender=sender
		self.receiver=receiver
		self.fileitem=fileitem
		self.level=level
		self.option=False
		self.tid=sender.username+receiver.username+fileitem.path
########################################################################
class RemoteMessage(pb.Copyable):
	def __init__(self,entries): 
		self.__dict__.update(entries)

########################################################################
pb.setUnjellyableFactoryForClass('modles.Message',RemoteMessage)
pb.setUnjellyableFactoryForClass('modles.RemoteMessage',RemoteMessage)
pb.setUnjellyableFactoryForClass('modles.File',RemoteFile)
pb.setUnjellyableFactoryForClass('modles.RemoteFile',RemoteFile)	
pb.setUnjellyableFactoryForClass('modles.Row',Row)
########################################################################
#base query fucttion
def queryDict(conn,sql,*args):
	cur=conn.cursor()
	cur.execute(sql,args)
	return ResultDict(cur.rs)
########################################################################
def queryObj(conn,sql,*args):
	cur=conn.cursor()
	cur.execute(sql,args)
	return ResultObj(cur.rs)
########################################################################
def queryArray(conn,sql,*args):
	cur=conn.cursor()
	cur.execute(sql,args)
	return ResultArray(cur.rs)
########################################################################
#conect to db  can use pool here
def connect():
	return adodbapi.connect(Cfg.connectStr)
########################################################################
#where
def where(**kv):
	"""sql 条件拼接"""
	sqlkeys=''
	sqlparams=[]
	for colum in kv.keys():
		sqlkeys+= ' and ' if sqlkeys else ' where '
		param=GBK(kv[colum])
		if colum.endswith('_gt'):
			exp=colum.replace('_gt',' > ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_lt'):
			exp=colum.replace('_lt',' < ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_ge'):
			exp=colum.replace('_ge',' >= ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_le'):
			exp=colum.replace('_le',' <= ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_like'):
			exp=colum.replace('_like',' like ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_in'):
			assert len(param)>0
			exp=colum.replace('_in',' in ')
			exp+='('+','.join('?'*len(param))+')'
			sqlparams+=param
		elif colum.endswith('_ne'):
			#not equal
			exp=colum.replace('_ne',' != ')+'?'
			sqlparams.append(param)
		else:
			exp=colum+'= ? '
			sqlparams.append(param)
		sqlkeys+=exp
	return sqlkeys,sqlparams

##select
#not  suggest to use
def select(conn,tables,colums='*',**kv):
	sql='select '+colums+' from '+tables
	keys,params=where(**kv)
	sql+=keys
	return queryObj(conn,sql,*params)




#insert
def insert(conn,table,**kv):
	""" eg: insert(conn,'test',id=2,name=5) sql:insert inot test(id,name) values(2,5)"""
	sqlcolums=[]
	sqlparams=[]
	for k,v in kv.items():
		sqlcolums.append(k)
		sqlparams.append(GBK(v))
	sqlparams=tuple(sqlparams)
	sql=' insert into '+table+' ('+','.join(sqlcolums)+') '+' values ('+','.join('?'*len(sqlcolums))+') '

	cur=conn.cursor()
	cur.execute(sql,sqlparams)




#update
def update(conn,table,where,**kv):
	"""eg: update(conn,'test',id=2,name=5) sql update test set id=2,name=5"""
	sqlcolums=[]
	sqlparams=[]
	for k,v in kv.items():
		sqlcolums.append(k)
		sqlparams.append(GBK(v))
	sql=' update '+table+' set '+','.join([p+'=?' for p in sqlcolums])
	if where:
		where_sql,where_sqlparams=where
		sqlparams+=where_sqlparams
		sql+=where_sql
	print sql
	cur=conn.cursor()
	cur.execute(sql,sqlparams)

#delete 
def delete(conn,table,**kv):
	"""删除"""
	where_sql,sqlparams=where(**kv)
	sql=' delete from '+table + where_sql
	cur=conn.cursor()
	cur.execute(sql,sqlparams)

########################################################################
#proc and function
@Execute
def  procLogin(name,pwd,ip,mac,conn=None):
	cur=conn.cursor()
	uid,val,remark,opResult=0,0,'',''
	param=(name,pwd, AvailablePeopleNum,mac, Ip2Int(ip), ip,False,uid,val,remark,opResult)
	param=cur.callproc('Proc_login',param)
	uid,val,remark,opResult=param[-4],param[-3],param[-2],param[-1]
	user=None
	if val==2:user=findUserByID(conn,uid)
	return user,remark

def procFindAvailableUser(conn,uid):
	"""查询当前可用的用户"""
	return queryObj(conn,"EXEC dbo.Find_Available_Users @UID = ? ",uid)

def funcNeedApprove(conn,fromUid,toUid,types):
	arr= queryArray(conn,'SELECT dbo.NeedApprove(?,?,?)',fromUid,toUid,types)
	if len(arr):return arr[0][0]
########################################################################





########################################################################

########################################################################



if __name__=='__main__':
	@Execute
	def TestUpdate(conn=None):
		update(conn, 'Users',where(uid=0),pwd='123')
	@Query
	def TestQuery(conn=None):
		unitshare = queryObj(conn, 'SELECT  ID,DirName Name,UtID,Note FROM dbo.Doc_UnitFolder')
		print unitshare.dirname


	@Execute
	def TestDelete(conn=None):
		delete(conn,'Doc_OffLineMessage', uid=0, messageid=1)



	TestUpdate()

#7-28 更新where 添加or 支持

添加connection with支持

class Conner:
	def __init__(self):
		self.conn=connect()

	def __enter__(self):
		return self.conn

	def __exit__(self, type, value, traceback):
		if type and value and traceback:
			LogErr()
		self.conn.close()




class Executer:
	def __init__(self):
		self.conn=connect()

	def __enter__(self):
		return self.conn

	def __exit__(self, type, value, traceback):
		if type and value and traceback:
			LogErr()
			self.conn.rollback()
		else:
			self.conn.commit()
		self.conn.close()


#where
def where(**kv):
	"""sql 条件拼接"""
	sqlkeys=''
	sqlparams=[]
	for colum in kv.keys():
		param=GBK(kv[colum])
		if colum.startswith('or_'):
			colum=colum.lstrip ('or_').lstrip('_')
			sqlkeys+= ' or ' if sqlkeys else ' where '
		else:
			sqlkeys+= ' and ' if sqlkeys else ' where '

		if colum.endswith('_gt'):
			exp=colum.replace('_gt',' > ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_lt'):
			exp=colum.replace('_lt',' < ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_ge'):
			exp=colum.replace('_ge',' >= ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_le'):
			exp=colum.replace('_le',' <= ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_like'):
			exp=colum.replace('_like',' like ')+'?'
			sqlparams.append(param)
		elif colum.endswith('_in'):
			assert len(param)>0
			exp=colum.replace('_in',' in ')
			exp+='('+','.join('?'*len(param))+')'
			sqlparams+=param
		elif colum.endswith('_ne'):
			#not equal
			exp=colum.replace('_ne',' != ')+'?'
			sqlparams.append(param)
		else:
			exp=colum+'= ? '
			sqlparams.append(param)
		sqlkeys+=exp
	return sqlkeys,sqlparams

Test

if __name__=='__main__':

	def TestUpdate(conn=None):
		update(conn, 'Users',where(or_uid=0,or__uid=-1),pwd='123')

	@Query
	def TestQuery(conn=None):
		unitshare = queryObj(conn, 'SELECT  ID,DirName Name,UtID,Note FROM dbo.Doc_UnitFolder')
		print unitshare.dirname


	@Execute
	def TestDelete(conn=None):
		delete(conn,'Doc_OffLineMessage', uid=0, messageid=1)




	with Executer() as conn:
		update(conn, 'Users',where(or_uid=0,or__uid=-1),pwd='123')