阅读:40
本篇使用Python 的Web框架Django连接和操作MySQL数据库项目管理平台(SMS),主要包含对项目信息的增删改查功能,旨在快速入门Python Web,少走弯路。效果演示在项目实战最后一节。
File->New Project->Django
稍等片刻,项目的目录结构如下图
项目创建后确认是否已安装Django和pyMySql解释器,如何确认?file->Settings
如果没有请在Terminal终端输入以下命令完成安装
pip install django
pip install mysqlclient
2.创建应用(sims)
打开Pycharm的Terminal终端,输入以下命令创建sims应用
python manage.py startapp sms
应用创建后要在项目的settings.py文件里的INSTALLED_APPS下面添加smis完成应用注册
3.Django配置MySQL
在本地MySQL创建sms数据库,修改项目的settings连接信息由默认的sqlite修改为MySQL
在应用sms下models.py添加TestWeekly模型
Terminal终端输入以下两条命令,其作用第一条生成文件记录模型的变化;第二条是将模型变化同步至数据库,我们可以在数据库生成对应的表结构。
python manage.py makemigrations sms
python manage.py migrate sms
本质可以理解请求路径url和处理方法的映射配置,首先在项目djangologin的urls.py文件中添加sms的路由配置
然后在sms添加一个名为urls.py的文件,添加路由配置如下
from django.shortcuts import render
# Create your views here.
from django.shortcuts import render
# Create your views here.
import pymysql
from django.shortcuts import render, redirect
from sms import models
import logging
import xlwt
from django.http import HttpResponse
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage, InvalidPage
from django.db.models import Q
from datetime import datetime
# Create your views here.
# 信息列表处理函数
def index(request):
# conn = pymysql.connect(host="130.1.12.193", user="root", passwd="hrbb1234", db="hrbb_auto2", charset='utf8')
# with conn.cursor(pymysql.cursors.DictCursor) as cursor:
# cursor.execute("SELECT id,reqId,reqName,reqLeadBusinesUnit,reqDepart,reqPriority,reqGroup,reqTestManager,reqTester,reqDeveloper,receiveDate,releaseDateP,reqStage,testStage,progress,releaseDate,testCaseNum,bugNum,bugHitRate,qualityAssessment,IsPerformance,IsNew,IsOutSource,remark,created FROM hrbb_auto2.sms_testweekly")
# Weeklys = cursor.fetchall()
Weeklys = models.TestWeekly.objects.all().values().order_by('-receiveDate')
# 将数据按照规定每页显示 20 条, 进行分割
paginator = Paginator(Weeklys, 25)
if request.method == "GET":
# 获取 url 后面的 page 参数的值, 首页不显示 page 参数, 默认值是 1
page = request.GET.get('page')
try:
books = paginator.page(page)
# todo: 注意捕获异常
except PageNotAnInteger:
# 如果请求的页数不是整数, 返回第一页。
books = paginator.page(1)
except InvalidPage:
# 如果请求的页数不存在, 重定向页面
return HttpResponse('找不到页面的内容')
except EmptyPage:
# 如果请求的页数不在合法的页数范围内,返回结果的最后一页。
books = paginator.page(paginator.num_pages)
logging.info('获取列表信息成功',books)
return render(request, 'login/index2.html', {'books':books})
def index2(request):
# conn = pymysql.connect(host="130.1.12.193", user="root", passwd="hrbb1234", db="hrbb_auto2", charset='utf8')
# with conn.cursor(pymysql.cursors.DictCursor) as cursor:
# cursor.execute("SELECT id,reqId,reqName,reqLeadBusinesUnit,reqDepart,reqPriority,reqGroup,reqTestManager,reqTester,reqDeveloper,receiveDate,releaseDateP,reqStage,testStage,progress,releaseDate,testCaseNum,bugNum,bugHitRate,qualityAssessment,IsPerformance,IsNew,IsOutSource,remark,created FROM hrbb_auto2.sms_testweekly")
# Weeklys = cursor.fetchall()
Weeklys = models.TestWeekly.objects.all().values().order_by('-id')
# 将数据按照规定每页显示 20 条, 进行分割
paginator = Paginator(Weeklys, 20)
if request.method == "GET":
# 获取 url 后面的 page 参数的值, 首页不显示 page 参数, 默认值是 1
page = request.GET.get('page')
try:
books = paginator.page(page)
# todo: 注意捕获异常
except PageNotAnInteger:
# 如果请求的页数不是整数, 返回第一页。
books = paginator.page(1)
except InvalidPage:
# 如果请求的页数不存在, 重定向页面
return HttpResponse('找不到页面的内容')
except EmptyPage:
# 如果请求的页数不在合法的页数范围内,返回结果的最后一页。
books = paginator.page(paginator.num_pages)
logging.info('获取列表信息成功',books)
return render(request, 'login/index.html', {'books':books})
#需求编号搜索框
def search(request):
#获取base.html中input的name传的值,也就是输入框的值(name='api_reqId')
reqId = request.GET.get('api_reqId')
manage = request.GET.get("api_reqTestManager")
# error_msg = ''
# if not keyStr:
# error_msg = '请输入需求编号'
# return render(request,'sms/search_index.html',{'error_msg':error_msg})
#通过输入的值,匹配查找数据库的数据keyStr
post_list = models.TestWeekly.objects.filter(Q(reqId__icontains=reqId) | Q(reqName__icontains=reqId) | Q(reqStage__icontains=reqId), Q(reqTestManager__icontains=manage))
return render(request, 'sms/search_index.html', {'post_list': post_list})
# 信息新增处理函数
def add(request):
if request.method == 'GET':
return render(request, 'sms/add.html')
else:
reqId = request.POST['reqId']
reqName = request.POST['reqName']
reqLeadBusinesUnit = request.POST['reqLeadBusinesUnit']
reqDepart = request.POST['reqDepart']
reqPriority = request.POST['reqPriority']
reqGroup = request.POST['reqGroup']
reqTestManager = request.POST['reqTestManager']
reqTester = request.POST['reqTester']
reqDeveloper = request.POST['reqDeveloper']
receiveDate = request.POST['receiveDate']#receiveDate
#releaseDateP = request.POST['releaseDateP']#计划发布日期
reqStage = request.POST['reqStage']
testStage = request.POST['testStage']
progress = request.POST['progress']
#releaseDate = request.POST['releaseDate']#实际投产日期
testCaseNum = request.POST['testCaseNum']
bugNum = request.POST['bugNum']
bugHitRate = request.POST['bugHitRate']
qualityAssessment = request.POST['qualityAssessment']
IsPerformance = request.POST['IsPerformance']
IsNew = request.POST['IsNew']
IsOutSource = request.POST['IsOutSource']
remark = request.POST['remark']
created = request.POST['created']
conn = pymysql.connect(host="130.1.12.193", user="root", passwd="hrbb1234", db="hrbb_auto2", charset='utf8')
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
releaseDateP = request.POST.get("releaseDateP", "")
releaseDate = request.POST.get("releaseDate", "")
#对日期进行处理,输入字符串可以releaseDate可以添加到数据库
try:
releaseDateP = datetime.strptime(releaseDateP, '%Y-%m-%d')
except ValueError:
releaseDateP = None
try:
releaseDate = datetime.strptime(releaseDate, '%Y-%m-%d')
except ValueError:
releaseDate = None
#执行一个新增的操作
cursor.execute("INSERT INTO sms_testweekly (reqId,reqName,reqLeadBusinesUnit,reqDepart,reqPriority,reqGroup,reqTestManager,reqTester,reqDeveloper,receiveDate,releaseDateP,reqStage,testStage,progress,releaseDate,testCaseNum,bugNum,bugHitRate,qualityAssessment,IsPerformance,IsNew,IsOutSource,remark,created) "
"values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", [reqId,reqName,reqLeadBusinesUnit,reqDepart,reqPriority,reqGroup,reqTestManager,reqTester,reqDeveloper,receiveDate,releaseDateP,reqStage,testStage,progress,releaseDate,testCaseNum,bugNum,bugHitRate,qualityAssessment,IsPerformance,IsNew,IsOutSource,remark,created])
#result = cursor.fetchall()
conn.commit()
#关闭指针对象和连接对象
cursor.close()
conn.close()
return redirect('../')
# 信息修改处理函数
def edit(request):
#根据表单提交的方式判断是查询单个还是修改后提交数据库
if request.method == 'GET':
#获取要修改的对象的id查询单条数据
id = request.GET.get("id")
#根据查询的ID查询单条数据进行修改
weekly1= models.TestWeekly.objects.get(id=id)
#跳转到修改页面,并携带修改的对象信息
return render(request, 'sms/edit.html', {'weekly1': weekly1})
else:
releaseDate = request.POST.get("releaseDate", "")
releaseDateP = request.POST.get("releaseDateP", "")
#格式化实际投产日期
try:
releaseDate = datetime.strptime(releaseDate, '%Y-%m-%d')
except ValueError:
releaseDate = None
#格式化计划发布日期
try:
releaseDateP = datetime.strptime(releaseDateP, '%Y-%m-%d')
except ValueError:
releaseDateP = None
#获取要修改的对象信息
smsid = request.POST.get("id")
update_sms = models.TestWeekly.objects.get(id=smsid)
reqId = request.POST.get('reqId', '')
reqName = request.POST.get('reqName', '')
reqLeadBusinesUnit = request.POST.get('reqLeadBusinesUnit', '')
reqDepart = request.POST.get('reqDepart', '')
reqPriority = request.POST.get('reqPriority', '')
reqGroup = request.POST.get('reqGroup', '')
reqTestManager = request.POST.get('reqTestManager', '')
reqTester = request.POST.get('reqTester', '')
reqDeveloper = request.POST.get('reqDeveloper', '')
receiveDate = request.POST.get('receiveDate', '')
#releaseDateP = request.POST.get('releaseDateP', '')
reqStage = request.POST.get('reqStage', '')
testStage = request.POST.get('testStage', '')
progress = request.POST.get('progress', '')
#releaseDate = request.POST.get('releaseDate', '')
testCaseNum = request.POST.get('testCaseNum', '')
bugNum = request.POST.get('bugNum', '')
bugHitRate = request.POST.get('bugHitRate', '')
qualityAssessment = request.POST.get('qualityAssessment', '')
IsPerformance = request.POST.get('IsPerformance', '')
IsNew = request.POST.get('IsNew', '')
IsOutSource = request.POST.get('IsOutSource', '')
remark = request.POST.get('remark', '')
created = request.POST.get('created', '')
#修改对象信息
update_sms.reqId=reqId
update_sms.reqName=reqName
update_sms.reqLeadBusinesUnit=reqLeadBusinesUnit
update_sms.reqDepart=reqDepart
update_sms.reqPriority=reqPriority
update_sms.reqGroup=reqGroup
update_sms.reqTestManager=reqTestManager
update_sms.reqTester=reqTester
update_sms.reqDeveloper=reqDeveloper
update_sms.receiveDate=receiveDate
update_sms.releaseDateP=releaseDateP #计划发布日期
update_sms.reqStage=reqStage
update_sms.testStage=testStage
update_sms.progress=progress
update_sms.releaseDate=releaseDate #实际投产日期
update_sms.testCaseNum=testCaseNum
update_sms.bugNum=bugNum
update_sms.bugHitRate=bugHitRate
update_sms.qualityAssessment=qualityAssessment
update_sms.IsPerformance=IsPerformance
update_sms.IsNew=IsNew
update_sms.IsOutSource=IsOutSource
update_sms.remark=remark
update_sms.created=created
#保存对象到数据库
update_sms.save()
#重定向到首页,显示数据
return redirect('../')
# 信息修改处理函数
def searchedit(request):
#根据表单提交的方式判断是查询单个还是修改后提交数据库
if request.method == 'GET':
#获取要修改的对象的id查询单条数据
id = request.GET.get("id")
#根据查询的ID查询单条数据进行修改
weekly1= models.TestWeekly.objects.get(id=id)
#跳转到修改页面,并携带修改的对象信息
return render(request, 'sms/edit.html', {'weekly1': weekly1})
else:
releaseDate = request.POST.get("releaseDate", "")
releaseDateP = request.POST.get("releaseDateP", "")
#格式化实际投产日期
try:
releaseDate = datetime.strptime(releaseDate, '%Y-%m-%d')
except ValueError:
releaseDate = None
#格式化计划发布日期
try:
releaseDateP = datetime.strptime(releaseDateP, '%Y-%m-%d')
except ValueError:
releaseDateP = None
#获取要修改的对象信息
smsid = request.POST.get("id")
update_sms = models.TestWeekly.objects.get(id=smsid)
reqId = request.POST.get('reqId', '')
reqName = request.POST.get('reqName', '')
reqLeadBusinesUnit = request.POST.get('reqLeadBusinesUnit', '')
reqDepart = request.POST.get('reqDepart', '')
reqPriority = request.POST.get('reqPriority', '')
reqGroup = request.POST.get('reqGroup', '')
reqTestManager = request.POST.get('reqTestManager', '')
reqTester = request.POST.get('reqTester', '')
reqDeveloper = request.POST.get('reqDeveloper', '')
receiveDate = request.POST.get('receiveDate', '')
#releaseDateP = request.POST.get('releaseDateP', '')
reqStage = request.POST.get('reqStage', '')
testStage = request.POST.get('testStage', '')
progress = request.POST.get('progress', '')
#releaseDate = request.POST.get('releaseDate', '')
testCaseNum = request.POST.get('testCaseNum', '')
bugNum = request.POST.get('bugNum', '')
bugHitRate = request.POST.get('bugHitRate', '')
qualityAssessment = request.POST.get('qualityAssessment', '')
IsPerformance = request.POST.get('IsPerformance', '')
IsNew = request.POST.get('IsNew', '')
IsOutSource = request.POST.get('IsOutSource', '')
remark = request.POST.get('remark', '')
created = request.POST.get('created', '')
#修改对象信息
update_sms.reqId=reqId
update_sms.reqName=reqName
update_sms.reqLeadBusinesUnit=reqLeadBusinesUnit
update_sms.reqDepart=reqDepart
update_sms.reqPriority=reqPriority
update_sms.reqGroup=reqGroup
update_sms.reqTestManager=reqTestManager
update_sms.reqTester=reqTester
update_sms.reqDeveloper=reqDeveloper
update_sms.receiveDate=receiveDate
update_sms.releaseDateP=releaseDateP #计划发布日期
update_sms.reqStage=reqStage
update_sms.testStage=testStage
update_sms.progress=progress
update_sms.releaseDate=releaseDate #实际投产日期
update_sms.testCaseNum=testCaseNum
update_sms.bugNum=bugNum
update_sms.bugHitRate=bugHitRate
update_sms.qualityAssessment=qualityAssessment
update_sms.IsPerformance=IsPerformance
update_sms.IsNew=IsNew
update_sms.IsOutSource=IsOutSource
update_sms.remark=remark
update_sms.created=created
#保存对象到数据库
update_sms.save()
#重定向到首页,显示数据
return redirect('/sms')
# 信息删除处理函数
def delete(request):
#获取需求删除的数据
delete_id = request.GET.get("id")
models.TestWeekly.objects.get(id=delete_id).delete()
#删除后重定向到首页
return redirect('../')
#下载Excel函数
def export_users_xls(request):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="weekly.xls"'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('weekly')
# Sheet header, first row
row_num = 0
font_style = xlwt.XFStyle()
font_style.font.bold = True
columns = ['需求编号','需求名称','产品需求编号','业务牵头部门','需求紧急程度','所属测试组','测试经理','测试人员','开发负责人','接收日期','计划发布日期','需求阶段','测试阶段','测试进度','实际投产日期','执行案例数','提交缺陷数','bug命中率','质量评定','是否需要压测','是否新建系统','是否外包项目','备注','创建日期' ]
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num], font_style)
# Sheet body, remaining rows
font_style = xlwt.XFStyle()
cols = [
'reqId','reqName','reqLeadBusinesUnit','reqDepart','reqPriority','reqGroup','reqTestManager','reqTester','reqDeveloper','receiveDate','releaseDateP','reqStage','testStage','progress','releaseDate','testCaseNum','bugNum','bugHitRate','qualityAssessment','IsPerformance','IsNew','IsOutSource','remark','created'
]
date_cols = [
"receiveDate",'releaseDateP','releaseDate','created'
]
rows = models.TestWeekly.objects.all().values_list('reqId','reqName','reqLeadBusinesUnit','reqDepart','reqPriority','reqGroup','reqTestManager','reqTester','reqDeveloper','receiveDate','releaseDateP','reqStage','testStage','progress','releaseDate','testCaseNum','bugNum','bugHitRate','qualityAssessment','IsPerformance','IsNew','IsOutSource','remark','created')
for row in rows:
row_num += 1
for col_num in range(len(row)):
val = row[col_num]
if val is not None and cols[col_num] in date_cols:
val = val.strftime("%Y-%m-%d")
ws.write(row_num, col_num, val, font_style)
wb.save(response)
return response
Terminal终端输入以下命令启动web服务
python manage.py runserver
服务启动后,打开浏览器输入http://127.0.0.1:8000/sms/即可进入学生信息管理列表页
至此,基于Python+Django+MySQL环境搭建一个拥有增删改查功能的Python Web就完成了。希望能够真正帮到大家快速入门Python Web开发。如果在搭建过程中您有遇到什么问题,欢迎在下方留言,看到我会立即回复的!