Python自動化運維開發----基礎（十六） 重寫Django框架中ListView查詢

1.? 說明

重寫Django框架中的ListView的get_queryset()，沒有使用Django的models去建表，使用MySQLdb去連接數據庫查詢數據

2. 安裝分頁插件

pip install django-pure-pagination

3. urls.py文件

from?django.urls?import?path,re_path
from?asset?import?views
app_name?=?'asset'
urlpatterns?=?[
????path('cabinetslist/',views.CabinetListView.as_view(),name="cabinets"),
]

4. views.py

#?導入view
from?django.views.generic?import?ListView
#?導入自定義模塊
from?pure_pagination.mixins?import?PaginationMixin

#?導入model中的sql查詢
from?.models?import?cabinet_list

class?CabinetListView(PaginationMixin,ListView):
????template_name?=?"asset/cabinet_list.html"
????context_object_name?=?"cabinetslist"
????paginate_by?=?5
????def?get_queryset(self):
????????cabinetslist?=?cabinet_list()
????????return?cabinetslist

5. model.py

#查詢機房列表
def?cabinet_list():
????sql?=?'SELECT?t.cabinet_id,t.cabinet_name,t.cabinet_site,t.cabinet_person,t.cabinet_phone,rack_cabinet_id,server_cabinet??from?(SELECT?a.cabinet_id,a.cabinet_name,a.cabinet_site,a.cabinet_person,a.cabinet_phone,COUNT(b.server_cabinet)?as?server_cabinet?FROM?cabinet?a?LEFT?JOIN?server??b?ON?a.cabinet_id?=?b.server_cabinet?GROUP?BY?a.cabinet_id)?as?t,(SELECT?c.cabinet_id,COUNT(d.rack_cabinet_id)?AS?rack_cabinet_id?FROM?cabinet?c?LEFT?JOIN?rack?d?ON?c.cabinet_id?=?d.rack_cabinet_id?GROUP?BY?c.cabinet_id)?as?p?WHERE?t.cabinet_id?=?p.cabinet_id'
????rt_cnt,?lines?=?MySQLUtil.dql(sql,?(),?True)
????cabinets?=?[]
????for?cabinet?in?lines:
????????cabinets.append({
????????????'cabinet_id':?cabinet[0],
????????????'cabinet_name':?cabinet[1],
????????????'cabinet_site':?cabinet[2],
????????????'cabinet_person':?cabinet[3],
????????????'cabinet_phone':?cabinet[4],
????????????'cabinet_rack_count':?cabinet[5],
????????????'cabinet_server_count':?cabinet[6]
????????})
????return?cabinets

6. mysqlutils.py

import?MySQLdb
MYSQL_HOST?=?'127.0.0.1'
MYSQL_PORT?=?3306
MYSQL_USER?=?'root'
MYSQL_PASSWORD?=?''
MYSQL_DB?=?'cmdb1'

class?MySQLUtil(object):

????@staticmethod
????def?dql(sql,?args,?fetch_all=True):
????????rt_cnt,?rt_tuple?=?0,?()
????????conn?=?MySQLdb.connect(host=MYSQL_HOST,?port=MYSQL_PORT,?user=MYSQL_USER,?passwd=MYSQL_PASSWORD,?db=MYSQL_DB,
???????????????????????????????charset='utf8mb4')
????????cursor?=?conn.cursor()
????????rt_cnt?=?cursor.execute(sql,?args)
????????if?fetch_all:
????????????rt_tuple?=?cursor.fetchall()
????????else:
????????????rt_tuple?=?cursor.fetchone()
????????cursor.close()
????????conn.close()
????????return?rt_cnt,?rt_tuple

????@staticmethod
????def?dml(sql,?args):
????????conn?=?MySQLdb.connect(host=MYSQL_HOST,?port=MYSQL_PORT,?user=MYSQL_USER,?passwd=MYSQL_PASSWORD,?db=MYSQL_DB,
???????????????????????????charset='utf8mb4')
????????cursor?=?conn.cursor()
????????rt_cnt?=?cursor.execute(sql,?args)
????????conn.commit()
????????cursor.close()
????????conn.close()
????????return?rt_cnt

7.? 實現效果