[jqgrid] 返回 json 格式的 python 函数

需要的库:
python: 2.5+
web.py:0.35+
sqlalchemy:0.7 #编写这个的时候用的是0.7其他没有测试过~

主体代码：

# -*- coding: utf-8 -*-
import random,string
import json

'''
把每一条记录的值排列成元组,即: ['a','b','c'] 形式
'''
def row2Tuple(row):
    d=[]
    for columnName in row.__table__.columns.keys():
        d.append(getattr(row,columnName))
    return tuple(d)

###查询条件转换orm查询值
def get_filter(field, op, string):
    if op=='eq':
        return "%s='%s'" % (field,string)
    if op=='ne':
        return "%s<>'%s'" % (field,string)
    if op=='lt':
        return "%s<'%s'" % (field,string)
    if op=='gt':
        return "%s>'%s'" % (field,string)
    if op=='le':
        return "%s<='%s'" % (field,string)
    if op=='ge':
        return "%s>='%s'" % (field,string)
    if op=='bw':
        return "%s like '%s%%'" % (field,string)
    if op=='bn':
        return "%s not like '%s%%'" % (field,string)
    if op=='ew':
        return "%s like '%%%s'" % (field,string)
    if op=='en':
        return "%s not like '%%%s'" % (field,string)
    if op=='in':
        splitchar = ' '
        if '|' in string:
            splitchar = '|'
        wordlist = "','".join(string.split(splitchar))
        return "%s in ('%s')" % (field,wordlist)
    if op=='ni':
        splitchar = ' '
        if '|' in string:
            splitchar = '|'
        wordlist = "','".join(string.split(splitchar))
        return "%s not in ('%s')" % (field,wordlist)
    if op=='cn':
        return "%s like '%%%s%%'" % (field,string)
    if op=='nc':
        return "%s not like '%%%s%%'" % (field,string)

 

def getjson(web_obj,models):
        i      = web_obj.input(rows=10,page=1,sidx='id',sord='',_search='false',searchField=None,searchOper=None,searchString=None)
        page   = int(i.page)                                  #当前页码
        limit  = int(i.rows)                                 #返回每页的记录数
        sidx   = i.sidx                                       #主键
        sord   = i.sord                                       #排序方式
        search = i._search                                  #获取查询命令
        offset = (page-1)*limit                             #获取分页

        query         = web_obj.ctx.orm.query(models)               #返回查询实体
        db            = query.all()                                    #返回所有记录
        table_columns = [x for x in [row.__table__.columns.keys() for row in db][0]]    #返回所有字段
       
       
        if sidx in table_columns and sord=='asc':
            query = query.order_by(sidx)
        if sidx in table_columns and sord=='desc':
            query = query.order_by(desc(sidx))
        if search=='true':                                  #是否有查询条件
            d = json.loads(i.filters)                       #将json查询条件转为字典
            _filter=''                                      #筛选的结果字符串
            _filter_ls=[]                                   #记录多条筛选条件,如果是单条件侧不起作用
            _rules_len=len(d["rules"])                      #获取需要筛选条件数
            searchgroupOp = ' '+d["groupOp"]+' '   
            if _rules_len>0:                                #多条件
                for i in xrange(_rules_len):
                    _filter_ls.append(get_filter(d["rules"][i]["field"], d["rules"][i]["op"], d["rules"][i]["data"]))
                    _filter=searchgroupOp.join(_filter_ls)
            else:                                           #单条件
                searchField = d["rules"][0]["field"]
                searchOper = d["rules"][0]["op"]
                searchString = d["rules"][0]["data"]
                _filter = get_filter(searchField, searchOper, searchString)

            query = query.filter(_filter)                   #传入查询条件

       
        count = query.count()                               #统计符合条件所有记录数
        query = query.offset(offset)                        #按记录数分页返回当前页码
       
        return json.dumps({"page":page,"total":int((count-1)/limit)+1,"records":count,"rows":[{'id':row.id,'cell':row2Tuple(row)} for row in query.limit(limit)]

 

sqlalchemy实体类:

class T(Base):
    __tablename__='T'

    id       = Column(Integer,primary_key=True)
    username = Column(String)

    def __init__(self,username,fullname):
        self.username = username
        self.fullname = fullname

    def __repr__(self):
        return "<T('%s','%s')>" % (self.username,self.fullname)

 

 

调用代码:

import web
class Test:
    def GET(self):
        return jqgrid.getjson(web,T)  #这里的T就是上面的sqlalchemy实体类了