adodbapi orm对象查询(二)

转载注明出处 http://blog.youkuaiyun.com/xugangjava/article/details/7565420

接上篇，实现SQL关键字，以前搞java这些经常是数据库改了一个字段，

先改数据库，然后改SQL，还要改Javabean,最后还要去改前台页面

实现下面这些函数将会是代码更加简化

#where
def where(**kv):
	keys=''
	params=[]
	for key in kv.keys():
		if keys:keys+=' and '
		else: keys=' where '
		flag='='
		if key.endswith('_gt'):
			flag='>'
		if key.endswith('_lt'):
			flag='<'
		if key.endswith('_ge'):
			flag='>='
		if key.endswith('_le'):
			flag='<='
		keys+=key+flag+'?'
		param=kv[key]
		if isinstance(param,unicode):param=param.encode('gbk')
		params.append(param)
	return keys,tuple(params)
#select
def select(conn,colums,tables,**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)
	"""
	sql=' insert into '+table
	values =keys=''
	params=[]
	for colum in kv.keys():
		if keys:keys+=','
		if values:values+=','
		keys+=colum
		values+='?'
		param=kv[colum]
		if isinstance(param,unicode):
			param=param.encode('gbk')
		params.append(param)
	keys=' ('+keys+') '	
	values=' values ('+values+') '
	params=tuple(params)
	sql=sql+keys+values
	cur=conn.cursor()
	cur.execute(sql,params)
#update
def update(conn,table,**kv):
	"""eg: update(conn,'test',id=2,name=5)
		sql update test set id=2,name=5
	"""
	sql=' update '+table+' set '
	params=[]
	keys=''
	for colum in kv.keys():
		if keys:keys+=' , '
		keys+=colum+'=?'
		param=kv[colum]
		if isinstance(param,unicode):
			param=param.encode('gbk')
		params.append(kv[colum])
	if where:sql+=where
	cur=conn.cursor()
	cur.execute(sql,params)
#delete 
def delete(conn,table,**kv):
	sql=' delete from '+table
	keys,params=where(**kv)
	sql+=keys
	cur=conn.cursor()
	cur.execute(sql,params)

上面代码可能有一些局限性，但是已经大幅度简化我的代码来了，可能有些在项目的下一步在进一步完善

怎么用呢，像下面这样，那个insert我就省了不少代码

########################################################################
if __name__=='__main__':
	conn=connect()
	#select
	print select(conn,'username','Users',uid=1)[0].username
	print select(conn,'username','Users',uid_gt=1)[0].username
	insert(conn, 'Doc_File',
		filename=message.fileitem.name,
		filepath=message.newpath,
		filesize=message.fileitem.isize,
		uploaderid=message.uploader.uid,
		uploadername=FullUserName(message.uploader),
		senderid=message.sender.uid,
		sendername=FullUserName(message.sender),
		senderunitid=message.sender.unitid,
		senderunitname=message.sender.unitname,
		unitfolderid=message.unitfolder.id if message.unitfolder  else 0,
		unitfoldername=message.unitfolder.name if message.unitfolder  else 0,
		ownerid=message.receiver.id if message.receiver else 0,
		ownername=FullUserName(message.receiver) if message.receiver else '',
		approvername=FullUserName(message.approver) if message.approver else '',
		approverid=message.approver.uid if message.approver else 0,
		filestatus=0 if failed else 1,
		needapporve=1 if message.approver else 0,
		lastmodifytime=message.fileitem.modifyTime,
		note=message.note)
	#delete
	delete(conn,'Doc_File',id=107)

最后在下篇文章将再次简化我们的查询逻辑

将下面写到要吐的代码交给装饰器把

connect.open()

try:

do some thing

finally:

connect.close()