python 处理oracle 分页查询

python 处理oracle 分页查询

# SQL语句
  sql = '''SELECT *
     FROM (SELECT B.*, ROWNUM AS rowno
     FROM (
         SELECT id, value, name from T_table
         WHERE name like '%{name }%'
         ORDER BY id desc) B
     WHERE ROWNUM <= {endnum}) table_alias
     WHERE table_alias.rowno >= {startnum}'''


# python 查询
def isNumber(num):
    '''
    判断是否为数字
    :param num:
    :return:
    '''
    pattern = re.compile(r'^[-+]?[-0-9]\d*\.\d*|[-+]?\.?[0-9]\d*$')
    result = pattern.match(str(num))
    if result:
        return True
    else:
        return False

def page_set(pageNum,pageSize):
    '''
    oracle 查询，分页处理
    :param pageNum:
    :param pageSize:
    :return:
    '''
    if not isNumber(pageNum):
        pageNum = 0
    if not isNumber(pageSize):
        pageSize = 0
    pageNum = int(pageNum)
    pageSize = int(pageSize)
    startnum = pageNum * pageSize
    if pageNum > 0:
        startnum = (pageNum - 1) * pageSize
    endnum = startnum + pageSize
    return startnum, endnum

def get_query(name,pageNum,pageSize):
    startnum, endnum = page_set(pageNum,pageSize)
    sql = sql.format(name=name,startnum=startnum,endnum=endnum)
    lines = db.search(sql)
    ..... # 处理查询结果

if __name__ == '__main__':
    get_query(name,pageNum,pageSize)