Python+Django+js+mysql实现简单项目管理平台

     阅读:40


前言

本篇使用Python 的Web框架Django连接和操作MySQL数据库项目管理平台(SMS),主要包含对项目信息的增删改查功能,旨在快速入门Python Web,少走弯路。效果演示在项目实战最后一节。


一、开发环境

  • 开发工具:Pycharm 2028.3
  • 开发语言:Python 3.7.0
  • Web框架:Django 3.0.6
  • 数据库:MySQL5.7
  • 操作系统:Mac

二、使用步骤

1.创建项目

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

4.数据模型创建(M)

在应用sms下models.py添加TestWeekly模型 

5.数据模型迁移

Terminal终端输入以下两条命令,其作用第一条生成文件记录模型的变化;第二条是将模型变化同步至数据库,我们可以在数据库生成对应的表结构。

python manage.py makemigrations sms

python manage.py migrate sms

 6.路由配置

本质可以理解请求路径url和处理方法的映射配置,首先在项目djangologin的urls.py文件中添加sms的路由配置

然后在sms添加一个名为urls.py的文件,添加路由配置如下 

 

7.处理函数(V)

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

 8.模板页面(T)

9.启动web服务测试

Terminal终端输入以下命令启动web服务

python manage.py runserver

 

服务启动后,打开浏览器输入http://127.0.0.1:8000/sms/即可进入学生信息管理列表页 

10.功能界面

总结

至此,基于Python+Django+MySQL环境搭建一个拥有增删改查功能的Python Web就完成了。希望能够真正帮到大家快速入门Python Web开发。如果在搭建过程中您有遇到什么问题,欢迎在下方留言,看到我会立即回复的!