Flask框架下python分页

分页功能必备的几个参数：

• current_page：1
• num_per_page: 10
• order_by：根据显示的项目而定
• order： ASC/DESC
• obscure：模糊搜索
• kwargs：[column] = xxx ：这里是指定返回符合要求的column，可指定多个

# 基础的查询语句
select_sql = "select * from xxx left join xxx on xxx.x==xxx.x"

# where条件
where_sql = " WHERE "
if kwargs:
    for arg in kwargs:
        where_str += arg + " = '" + kwargs[arg] + "' AND "
        where_str = where_str[:len(where_str) - 4]

if obscure:
	# obscure_search_fields 是支持模糊搜索的column
    obscure_search_fields = ['xxx.ip', 'xxx.name']
    where_sql = cls.get_where_sql(where_str=where_sql, obscure=obscure, args=obscure_search_fields)
    if not kwargs:
    # 有模糊搜索，但没有指定参数，删掉上一步的
        where_sql = where_sql .replace("AND", "")
elif not kwargs:
# 没有参数，且没有模糊搜索时删掉where条件
    where_sql = where_str.replace("WHERE", "")


# 排序（支持多个column）
order_sql = 'ORDER BY '
for o in order_by.split(','):
    order_sql = order_sql + '{0} {1}, '.format(o, order)
    order_str = order_str[:len(order_str) - 2]
# ORDER BY xxx ASC, xxx ASC, xxx ASC

# 限制每页的个数
limit_str = ''
if current_page and num_per_page:
    start_limit = (current_page - 1) * num_per_page
    end_limit = current_page * num_per_page
    limit_str += 'LIMIT {0}, {1}'.format(start_limit, end_limit)
# LIMIT 0, 10

# 最终的sql语句
sql = select_sql + where_sql + order_sql + limit_str 

def get_where_sql(cls, where_str, obscure, args):
    obscure_str = '%%' + obscure.replace('\\', '\\\\\\\\').replace('\'', '\'\'').replace('%', r'\%%') + '%%'
    tmp_where_str = ""
    for arg in args:
        tmp_where_str += arg + " LIKE '" + obscure_str + "' OR "
    if tmp_where_str:
        where_str += " AND (" + tmp_where_str[:len(tmp_where_str) - 4] + ")"
    return where_str