在開發中，查詢操作是使用最多的，而查詢列表是其中之一，查詢列表可分為分頁查詢和不分頁查詢（它們之間多了一次總記錄數查詢），還可以分為單表查詢和多表關聯查詢，返回的結構體根據前端使用的表單框架不同而有所區別。 我們先看看，對於列表分頁查詢，在介面中是如何處理的 1 @get('/api/product ...
在開發中，查詢操作是使用最多的，而查詢列表是其中之一，查詢列表可分為分頁查詢和不分頁查詢（它們之間多了一次總記錄數查詢），還可以分為單表查詢和多表關聯查詢，返回的結構體根據前端使用的表單框架不同而有所區別。
我們先看看，對於列表分頁查詢，在介面中是如何處理的
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 + ' ' + sord 25 # 類型 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 data 50 # 保存總記錄數量 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 = 10 58 # 計算總頁數 59 if data['records'] % page_size == 0: 60 page_total = data['records'] // page_size 61 else: 62 page_total = data['records'] // page_size + 1 63 # 記錄總頁面數量 64 data['total'] = page_total 65 66 # 判斷提交的頁碼是否超出範圍 67 if page_number < 1 or page_number > page_total: 68 page_number = page_total 69 # 記錄當前頁面索引值 70 data['page'] = page_number 71 72 # 計算當前頁面要顯示的記錄起始位置 73 record_number = (page_number - 1) * page_size 74 # 設置查詢分頁條件 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'] = result 87 88 if data: 89 # 直接輸出json 90 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_name 11 # 初始化查詢欄位名 12 if not column_name_list: 13 column_name_list = self.__column_name_list 14 # 初始化查詢條件 15 if wheres: 16 # 如果是字元串，表示該查詢條件已組裝好了，直接可以使用 17 if isinstance(wheres, str): 18 wheres = 'where ' + wheres 19 # 如果是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 data 12 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 + 1 10 # 判斷頁碼是否超出限制，超出限制查詢時會出現異常，所以將頁面索引設置為最後一頁 11 if page_number < 1 or page_number > page_total: 12 page_number = page_total 13 # 記錄總頁面數量 14 data['total'] = page_total 15 # 記錄當前頁面值 16 data['page'] = page_number 17 # 計算當前頁面要顯示的記錄起始位置（limit指定的位置） 18 record_number = (page_number - 1) * page_size 19 # 設置查詢分頁條件 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'] = result 11 # 不需要分頁查詢時，直接在這裡設置總記錄數 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_name 23 # 初始化查詢欄位名 24 if not column_name_list: 25 column_name_list = self.__column_name_list 26 # 初始化查詢條件 27 if wheres: 28 # 如果是字元串，表示該查詢條件已組裝好了，直接可以使用 29 if isinstance(wheres, str): 30 wheres = 'where ' + wheres 31 # 如果是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 data 51 52 # 設置記錄總數量 53 data['records'] = result[0].get('records') 54 55 ######################################################### 56 ### 設置分頁索引與頁面大小 ### 57 if page_size <= 0: 58 page_size = 10 59 # 計算總分頁數量：通過總記錄數除於每頁顯示數量來計算總分頁數量 60 if data['records'] % page_size == 0: 61 page_total = data['records'] // page_size 62 else: 63 page_total = data['records'] // page_size + 1 64 # 判斷頁碼是否超出限制，超出限制查詢時會出現異常，所以將頁面索引設置為最後一頁 65 if page_number < 1 or page_number > page_total: 66 page_number = page_total 67 # 記錄總頁面數量 68 data['total'] = page_total 69 # 記錄當前頁面值 70 data['page'] = page_number 71 # 計算當前頁面要顯示的記錄起始位置（limit指定的位置） 72 record_number = (page_number - 1) * page_size 73 # 設置查詢分頁條件 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'] = result 87 # 不需要分頁查詢時，直接在這裡設置總記錄數 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表操作類ProductLogic 24 _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': 0 18 }, { 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 '
