在开发中，查询操作是使用最多的，而查询列表是其中之一，查询列表可分为分页查询和不分页查询（它们之间多了一次总记录数查询），还可以分为单表查询和多表关联查询，返回的结构体根据前端使用的表单框架不同而有所区别。
我们先看看，对于列表分页查询，在接口中是如何处理的
1 @get('/api/product/') 2 def callback(): 3 """ 4 获取列表数据 5 """ 6 # 设置查询条件 7 wheres = '' 8 # 产品分类id 9 product_class_id = convert_helper.to_int0(web_helper.get_query('product_class_id', '产品分类id', is_check_null=False))10 if product_class_id > 0:11 wheres = 'where product_class_id=' + str(product_class_id)12 # 页面索引13 page_number = convert_helper.to_int1(web_helper.get_query('page', '', is_check_null=False))14 # 页面显示记录数量15 page_size = convert_helper.to_int0(web_helper.get_query('rows', '', is_check_null=False))16 # 排序字段17 sidx = web_helper.get_query('sidx', '', is_check_null=False)18 # 顺序还是倒序排序19 sord = web_helper.get_query('sord', '', is_check_null=False)20 # 初始化排序字段21 order_by = 'id desc'22 ### 设置排序 ###23 if sidx:24 order_by = sidx + ' ' + sord25 # 类型26 type = web_helper.get_query('type', '类型', is_check_null=False)27 # 判断是否是前台提交获取数据28 if type != 'backstage':29 # 判断是否已经存在查询条件了，是的话在原查询条件后面拼接30 if wheres:31 wheres = wheres + ' and is_enable=1'32 else:33 wheres = 'where is_enable=1'34 35 #############################################################36 # 初始化输出格式（前端使用jqgrid列表，需要指定输出格式）37 data = {38 'records': 0,39 'total': 0,40 'page': 1,41 'rows': [],42 }43 #############################################################44 # 执行sql，获取指定条件的记录总数量45 sql = 'select count(1) as records from product %(wheres)s' % {'wheres': wheres}46 result = db_helper.read(sql)47 # 如果查询失败或不存在指定条件记录，则直接返回初始值48 if not result or result[0]['records'] == 0:49 return data50 # 保存总记录数量51 data['records'] = result[0].get('records', 0)52 53 #############################################################54 ### 设置分页索引与页面大小 ###55 # 设置分页大小56 if page_size is None or page_size <= 0:57 page_size = 1058 # 计算总页数59 if data['records'] % page_size == 0:60 page_total = data['records'] // page_size61 else:62 page_total = data['records'] // page_size + 163 # 记录总页面数量64 data['total'] = page_total65 66 # 判断提交的页码是否超出范围67 if page_number < 1 or page_number > page_total:68 page_number = page_total69 # 记录当前页面索引值70 data['page'] = page_number71 72 # 计算当前页面要显示的记录起始位置73 record_number = (page_number - 1) * page_size74 # 设置查询分页条件75 paging = ' limit ' + str(page_size) + ' offset ' + str(record_number)76 77 #############################################################78 79 # 组合sql查询语句80 sql = "select * from product %(wheres)s order by %(orderby)s %(paging)s" % \81 {'wheres': wheres, 'orderby': order_by, 'paging': paging}82 # 读取记录83 result = db_helper.read(sql)84 if result:85 # 存储记录86 data['rows'] = result87 88 if data:89 # 直接输出json90 return web_helper.return_raise(json.dumps(data, cls=json_helper.CJsonEncoder))91 else:92 return web_helper.return_msg(-1, "查询失败")View Code
代码看起来很长，有点复杂，对于这种列表分页查询，如果不封装的话，开发时复制粘贴就很容易出错，所以我们需要重新处理才行。
从上面代码可以看到，具体功能分为几个部分：
第一部分（9到33行）是接收并组合查询条件，接收分页参数和排序参数
第二部分（37到42行）是初始化结果输出参数
第三部分（44到51行）是获取查询总记录数
第四部分（55到75行）是计算总页数，计算当前分页位置要显示的记录位置区间
第五部分（80到92行）是组合查询语句，查询并输出结果
除了产品列表这个接口，大家可以看看产品分类列表接口，会发现两个接口第二部分到第五部分都差不多，所以我们封装ORM时，可以将这些相似部分进行处理，将它们封装到ORM对应的方法里。
首先，我们对上面代码的分析，可以提炼出分页查询方法需要有下面参数：查询字段、查询条件、当前分页索引值、每页显示记录数量、排序。如果是多表查询时，我们的ORM是直接绑定当前表单的就不适用了，所以还需要有个设置表名的参数，好灵活处理各种需求，根据这些要求，我们可以创建好列表查询方法：
def get_list(self, column_name_list='', wheres='', page_number=None, page_size=None, orderby=None, table_name=None): """
获取指定条件的数据库记录集
:param column_name_list: 查询字段
:param wheres: 查询条件
:param page_number: 分页索引值
:param page_size: 分页大小， 存在值时才会执行分页
:param orderby: 排序规则
:param table_name: 查询数据表，多表查询时需要设置
:return: 返回记录集总数量与分页记录集
{'records': 0, 'total': 0, 'page': 0, 'rows': []} """在接收到这些参数以后，我们需要对相关参数进行初始化操作，方便后续代码的执行
1 # 初始化输出参数：总记录数量与列表集 2 data = { 3 'records': 0, # 总记录数 4 'total': 0, # 总页数 5 'page': 1, # 当前页面索引 6 'rows': [], # 查询结果（记录列表） 7 } 8 # 初始化查询数据表名称 9 if not table_name:10 table_name = self.__table_name11 # 初始化查询字段名12 if not column_name_list:13 column_name_list = self.__column_name_list14 # 初始化查询条件15 if wheres:16 # 如果是字符串，表示该查询条件已组装好了，直接可以使用17 if isinstance(wheres, str):18 wheres = 'where ' + wheres19 # 如果是list，则表示查询条件有多个，可以使用join将它们用and方式组合起来使用20 elif isinstance(wheres, list):21 wheres = 'where ' + ' and '.join(wheres)22 # 初始化排序23 if not orderby:24 orderby = self.__pk_name + ' desc'25 # 初始化分页查询的记录区间26 paging = ''这里是对传入的参数和后续需要用到的参数进行初始化操作
这里需要初始化查询结果输出参数结构，在进行记录数查询时，如果没有记录存在，就可以直接将结果返回出去了；
默认数据表为当前类实体指定的表名称，如果进行多表联合查询时，就需要设置多表联合查询的组合表名称，比如：product left join product_class on product.product_class_id = product_class.id
同时我们还需要设置查询字段内容，如果想查询出所有字段，直接使用*，如果只想要输出指定的几个字段值，则可以填写这几个字段值，比如：id,name,content
在查询时，有时不需要查询条件，这时我们可以不填写条件，如果有指定条件时，我们可以将它们组合好，也可以放到list中。它们的区别在于，有多个查询条件时，我们有时很难判断当前条件前需不需要添加and，这时我们就可以使用' and '.join(列表) 来进行合成了，当然用list方式条件之间只能是and的关系。对于复杂的条件，我们可以组合好以后提交进来直接使用；
在查询时，如果没有指定排序方式，我们默认使用主键倒序来进行排序
在分页列表操作时，我们通常需要获取总记录数返回给前端，所以在执行查询前，我们需要获取当前查询条件的总记录数
1 with db_helper.PgHelper(self.__db, self.__is_output_sql) as db: 2 ############################################################# 3 # 判断是否需要进行分页 4 if not page_size is None: 5 ### 执行sql，获取指定条件的记录总数量 6 sql = 'select count(1) as records from %(table_name)s %(wheres)s ' % \ 7 {'table_name': table_name, 'wheres': wheres} 8 result = db.execute(sql) 9 # 如果查询失败或不存在指定条件记录，则直接返回初始值10 if not result or result[0]['records'] == 0:11 return data12 13 # 设置记录总数量14 data['records'] = result[0].get('records')加上if not page_size is None判断，是因为有时候我们查询时，不需要分页操作，直接将所有记录输出了，这里加上判断可以减少不必要的记录总数量查询
当我们获取到总记录数量以后，我们需要根据前端页面显示的记录数进行计算，计算出总页面数量，排除页面索引值超出限制可能会带来的异常，还有需要计算当前页面查询时对应的记录起始位置，组合分页查询条件pagin
1 ######################################################### 2 ### 设置分页索引与页面大小 ### 3 if page_size <= 0: 4 page_size = 10 5 # 计算总分页数量：通过总记录数除于每页显示数量来计算总分页数量 6 if data['records'] % page_size == 0: 7 page_total = data['records'] // page_size 8 else: 9 page_total = data['records'] // page_size + 110 # 判断页码是否超出限制，超出限制查询时会出现异常，所以将页面索引设置为最后一页11 if page_number < 1 or page_number > page_total:12 page_number = page_total13 # 记录总页面数量14 data['total'] = page_total15 # 记录当前页面值16 data['page'] = page_number17 # 计算当前页面要显示的记录起始位置（limit指定的位置）18 record_number = (page_number - 1) * page_size19 # 设置查询分页条件20 paging = ' limit ' + str(page_size) + ' offset ' + str(record_number)21 #############################################################
最后，我们组合最终查询条件，查询并输出结果
1 ### 按条件查询数据库记录 2 sql = "select %(column_name_list)s from %(table_name)s %(wheres)s order by %(orderby)s %(paging)s" % \ 3 {'column_name_list': column_name_list, 4 'table_name': table_name, 5 'wheres': wheres, 6 'orderby': orderby, 7 'paging': paging} 8 result = db.execute(sql) 9 if result:10 data['rows'] = result11 # 不需要分页查询时，直接在这里设置总记录数12 if page_size is None:13 data['records'] = len(result)14 15 return data
完整代码
1 def get_list(self, column_name_list='', wheres='', page_number=None, page_size=None, orderby=None, table_name=None): 2 """ 3 获取指定条件的数据库记录集 4 :param column_name_list: 查询字段 5 :param wheres: 查询条件 6 :param page_number: 分页索引值 7 :param page_size: 分页大小， 存在值时才会执行分页 8 :param orderby: 排序规则 9 :param table_name: 查询数据表，多表查询时需要设置10 :return: 返回记录集总数量与分页记录集11 {'records': 0, 'total': 0, 'page': 0, 'rows': []}12 """13 # 初始化输出参数：总记录数量与列表集14 data = {15 'records': 0, # 总记录数16 'total': 0, # 总页数17 'page': 1, # 当前页面索引18 'rows': [], # 查询结果（记录列表）19 }20 # 初始化查询数据表名称21 if not table_name:22 table_name = self.__table_name23 # 初始化查询字段名24 if not column_name_list:25 column_name_list = self.__column_name_list26 # 初始化查询条件27 if wheres:28 # 如果是字符串，表示该查询条件已组装好了，直接可以使用29 if isinstance(wheres, str):30 wheres = 'where ' + wheres31 # 如果是list，则表示查询条件有多个，可以使用join将它们用and方式组合起来使用32 elif isinstance(wheres, list):33 wheres = 'where ' + ' and '.join(wheres)34 # 初始化排序35 if not orderby:36 orderby = self.__pk_name + ' desc'37 # 初始化分页查询的记录区间38 paging = ''39 40 with db_helper.PgHelper(self.__db, self.__is_output_sql) as db:41 #############################################################42 # 判断是否需要进行分页43 if not page_size is None:44 ### 执行sql，获取指定条件的记录总数量45 sql = 'select count(1) as records from %(table_name)s %(wheres)s ' % \46 {'table_name': table_name, 'wheres': wheres}47 result = db.execute(sql)48 # 如果查询失败或不存在指定条件记录，则直接返回初始值49 if not result or result[0]['records'] == 0:50 return data51 52 # 设置记录总数量53 data['records'] = result[0].get('records')54 55 #########################################################56 ### 设置分页索引与页面大小 ###57 if page_size <= 0:58 page_size = 1059 # 计算总分页数量：通过总记录数除于每页显示数量来计算总分页数量60 if data['records'] % page_size == 0:61 page_total = data['records'] // page_size62 else:63 page_total = data['records'] // page_size + 164 # 判断页码是否超出限制，超出限制查询时会出现异常，所以将页面索引设置为最后一页65 if page_number < 1 or page_number > page_total:66 page_number = page_total67 # 记录总页面数量68 data['total'] = page_total69 # 记录当前页面值70 data['page'] = page_number71 # 计算当前页面要显示的记录起始位置（limit指定的位置）72 record_number = (page_number - 1) * page_size73 # 设置查询分页条件74 paging = ' limit ' + str(page_size) + ' offset ' + str(record_number)75 #############################################################76 77 ### 按条件查询数据库记录78 sql = "select %(column_name_list)s from %(table_name)s %(wheres)s order by %(orderby)s %(paging)s" % \79 {'column_name_list': column_name_list,80 'table_name': table_name,81 'wheres': wheres,82 'orderby': orderby,83 'paging': paging}84 result = db.execute(sql)85 if result:86 data['rows'] = result87 # 不需要分页查询时，直接在这里设置总记录数88 if page_size is None:89 data['records'] = len(result)90 91 return data
我们在单元测试中跑一跑，看看结果吧
1 #!/usr/bin/evn python 2 # coding=utf-8 3 4 import unittest 5 from common.string_helper import string 6 from logic import product_logic 7 8 class DbHelperTest(unittest.TestCase): 9 """数据库操作包测试类"""10 11 def setUp(self):12 """初始化测试环境"""13 print('------ini------')14 15 def tearDown(self):16 """清理测试环境"""17 print('------clear------')18 19 def test(self):20 ##############################################21 # 只需要看这里，其他代码是测试用例的模板代码 #22 ##############################################23 # 实例化product表操作类ProductLogic24 _product_logic = product_logic.ProductLogic()25 result = _product_logic.get_list('*', '', 1, 2)26 print(result)27 28 ##############################################29 30 if __name__ == '__main__':31 unittest.main()输出结果
1 -- -- --ini-- -- -- 2 { 3 'records': 4, 4 'total': 1, 5 'page': 1, 6 'rows': [{ 7 'content': '', 8 'name': '名称', 9 'place_of_origin': '',10 'front_cover_img': '',11 'code': '201808031245678',12 'quality_guarantee_period': '',13 'product_class_id': 1,14 'standard': '',15 'add_time': datetime.datetime(2018, 8, 3, 16, 51, 3),16 'id': 15,17 'is_enable': 018 }, {19 'content': '',20 'name': '张三',21 'place_of_origin': '',22 'front_cover_img': '',23 'code': '201807251234568',24 'quality_guarantee_period': '',25 'product_class_id': 0,26 'standard': '',27 'add_time': datetime.datetime(2018, 8, 3, 0, 14, 14),28 'id': 14,29 'is_enable': 030 }]31 }32 -- -- --clear-- -- --
前面的接口我们也改造一下
1 @get('/api/product/') 2 def callback(): 3 """ 4 获取列表数据 5 """ 6 # 产品分类id 7 product_class_id = convert_helper.to_int0(web_helper.get_query('product_class_id', '产品分类id', is_check_null=False)) 8 # 类型 9 type = web_helper.get_query('type', '类型', is_check_null=False)10 # 页面索引11 page_number = convert_helper.to_int1(web_helper.get_query('page', '', is_check_null=False))12 # 页面显示记录数量13 page_size = convert_helper.to_int0(web_helper.get_query('rows', '', is_check_null=False))14 # 排序字段15 sidx = web_helper.get_query('sidx', '', is_check_null=False)16 # 顺序还是倒序排序17 sord = web_helper.get_query('sord', '', is_check_null=False)18 19 # 设置查询条件20 wheres = []21 if product_class_id > 0:22 wheres.append('product_class_id=' + str(product_class_id))23 # 判断是否是前台提交获取数据24 if type != 'backstage':25 wheres.append('is_enable=1')26 27 # 初始化排序字段28 orderby = None29 ### 设置排序 ###30 if sidx:31 orderby = sidx + ' ' + sord32 33 # 实例化product表操作类ProductLogic34 _product_logic = product_logic.ProductLogic()35 result = _product_logic.get_list('*', wheres, page_number, page_size, orderby)36 if result:37 return web_helper.return_raise(json.dumps(result, cls=json_helper.CJsonEncoder))38 else:39 return web_helper.return_msg(-1, "查询失败")
这样处理以后，代码看起来舒服多了