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. 实现效果
