django实现后端接口增删改查以及文件上传和下载

业务逻辑接口文件  UnitCommunicationViews.py

# 单位交流后端接口
import base64
import json
import os
import time

from django.http import HttpResponse, HttpResponseRedirect, JsonResponse, QueryDict

from compo.db import crud

# 查询所有单位交流信息
from compo.utils import file, sqlUtils

RES_SUC = 200
RES_ERR = 500


# 查询，以及id查询
def query_unit_communication(request):
    try:
        if request.method == 'GET':
            if request.GET.get("id") is None:
                sql = "select * from unit_communication"
            else:
                sql = "select * from unit_communication where id =" + request.GET.get("id")
            unit_communication_list = list(crud.queryAll(sql))
            # ensure_ascii将默认的ascii码转成中文
            return JsonResponse(unit_communication_list, safe=False, json_dumps_params={'ensure_ascii': False})
        else:
            return HttpResponseRedirect("不支持的请求方法!")
    except:
        return HttpResponse("服务器异常")


# 查询，条件查询
def query_condition(request):
    if request.method == 'GET':
        # 拼接sql
        sql = "select * from unit_communication where 1=1"
        print("=====" + request.GET.get("start_time"))
        for key in request.GET:
            if key == "end_time" and len(request.GET.get(key)) > 0:
                sql = sql + " and communication_time < '" + request.GET.get("end_time") + "'"
            elif key == "start_time" and len(request.GET.get(key)) > 0:
                sql = sql + " and communication_time > '" + request.GET.get("start_time") + "'"
            elif key is not None and len(request.GET.get(key)) > 0:
                sql = sql + " and " + key + " like '%" + request.GET.get(key) + "%'"
        lst = list(crud.queryAll(sql))
        return JsonResponse(lst, safe=False, json_dumps_params={'ensure_ascii': False})
    else:
        return HttpResponse("不支持的请求方法！")


# 增加单位交流信息
def insert_unit_communication(request):
    try:
        if request.method == 'POST':
            # 保存图片和文件
            if request.FILES.get("image", None) is not None:
                image_name = file.update(request, "image")
            else:
                image_name = None
            if request.FILES.get("file", None) is not None:
                enclosure_name = file.update(request, "file")
            else:
                enclosure_name = None
            insert_sql(request, image_name, enclosure_name)
            return HttpResponse("新增数据成功！")
        else:
            return HttpResponse("不支持的请求方法！")
    except:
        return HttpResponse("服务器错误！")


# 增加sql拼接
def insert_sql(request, image_name, enclosure_name):
    sql = "insert into unit_communication(" \
          "other_unit_name,other_director_name,other_director_phone," \
          "unit_name,director_name,director_phone,communication_time," \
          "communication_place,communication_title,communication_summary," \
          "create_time,update_time,recoder,image_name,enclosure_name) " \
          "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    data = (request.POST.get('other_unit_name'), request.POST.get('other_director_name'), \
            request.POST.get('other_director_phone'), request.POST.get('unit_name'), \
            request.POST.get('director_name'), request.POST.get('director_phone'), \
            request.POST.get('communication_time'), request.POST.get('communication_place'), \
            request.POST.get('communication_title'), request.POST.get('communication_summary'), \
            create_time, update_time, request.POST.get('recoder'), image_name, enclosure_name)
    crud.execute(sql, data)


# 更新单位交流信息
def update_unit_communication(request):
    if request.method == "POST":
        sql_name = "select image_name,enclosure_name from unit_communication where id=" + request.POST.get("id")
        names = crud.queryAll(sql_name)
        results = update_file(request, names)
        update_sql(request, results['image_name'], results['enclosure_name'])
        return HttpResponse("成功更新单位交流信息")
    else:
        return HttpResponse("不支持的请求方法")


# 更新sql拼接
def update_sql(request, image_name, enclosure_name):
    sql = "UPDATE unit_communication SET " \
          "other_unit_name = %s, other_director_name = %s, other_director_phone = %s," \
          "unit_name = %s, director_name = %s, director_phone = %s, communication_time = %s," \
          "communication_place = %s, communication_title = %s, communication_summary = %s," \
          "update_time = %s, recoder = %s, image_name = %s, enclosure_name =%s WHERE id = %s"
    update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    data = (request.POST.get('other_unit_name'), request.POST.get('other_director_name'), \
            request.POST.get('other_director_phone'), request.POST.get('unit_name'), \
            request.POST.get('director_name'), request.POST.get('director_phone'), \
            request.POST.get('communication_time'), request.POST.get('communication_place'), \
            request.POST.get('communication_title'), request.POST.get('communication_summary'), \
            update_time, request.POST.get('recoder'), image_name, enclosure_name, request.POST.get('id'))
    crud.execute(sql, data)


# 更新数据文件处理
def update_file(request, names):
    image_name = names[0]['image_name']
    enclosure_name = names[0]['enclosure_name']
    # 保存图片和文件
    if names[0]['image_name'] is None:
        if request.FILES.get('image', None) is not None:
            image_name = file.update(request, 'image')
    else:
        if request.FILES.get('image', None) is not None:
            os.remove('..\\file\\' + names[0]["image_name"])
            image_name = file.update(request, 'image')
    if names[0]['enclosure_name'] is None:
        if request.FILES.get('file', None) is not None:
            enclosure_name = file.update(request, 'file')
    else:
        if request.FILES.get('file', None) is not None:
            os.remove('..\\file\\' + names[0]["enclosure_name"])
            enclosure_name = file.update(request, 'file')
    results = {'image_name': image_name, 'enclosure_name': enclosure_name}
    return results


# 删除单位交流信息
def delete_unit_communication(request):
    try:
        if request.method == 'GET':
            # 先删除存在的图片和文件
            sql_name = "select image_name,enclosure_name from unit_communication where id=" + request.GET.get("id")
            del_file(request, sql_name)
            sql = "delete from unit_communication where id = %s"
            crud.execute(sql, request.GET.get("id"))
            return HttpResponse("删除成功")
        else:
            return HttpResponse("不支持的请求方法")
    except:
        return HttpResponse("服务器出错")


# 删除之前的文件和图片（在修改和更新时都会触发）
def del_file(request, sql):
    # 如果文件已存在，则删除
    names = crud.queryAll(sql)
    print(names[0]["image_name"])
    if names[0]["image_name"] is not None:
        os.remove('..\\file\\' + names[0]["image_name"])
    if names[0]["enclosure_name"] is not None:
        os.remove('..\\file\\' + names[0]["enclosure_name"])


# 封面图片上传
def upload_image(request):
    if request.method == 'POST':
        # 如已存在封面，先删除
        sql_selectId = "select image_name from unit_communication where id = " + request.POST.get("id")
        unit_com = crud.queryAll(sql_selectId)
        if unit_com[0][0] is not None:
            os.remove('..\\file\\' + unit_com[0][0])
        file_name = file.update(request, "uploadFile")
        if file_name is None:
            return HttpResponse("没有上传图片！")
        # 向部门交流插入image名
        sql = "update unit_communication set image_name = %s where id = %s"
        data = file_name, request.POST.get("id")
        crud.execute(sql, data)
        return HttpResponse("上传图片成功")
    else:
        return HttpResponseRedirect("不支持的请求方法")


# 封面图片查看
def look_image(request):
    if request.method == "GET":
        sql = "select image_name from unit_communication where id = " + request.GET.get("id")
        unit_com = crud.queryAll(sql)
        if unit_com[0][0] is not None:
            with open("..\\file\\" + unit_com[0][0], 'rb') as f:
                img_byte = base64.b64encode(f.read())
            img_str = img_byte.decode('ascii')
            image = {'image': img_str}
            return JsonResponse(image, safe=False, json_dumps_params={'ensure_ascii': False})
        else:
            return HttpResponse("没有上传过图片，无法查看！")
    else:
        return HttpResponseRedirect("不支持的请求方式")


# 附件上传
def update_attachment(request):
    if request.method == 'POST':
        # 如已存在附件，先删除
        sql = "select enclosure_name from unit_communication where id = " + request.POST.get("id")
        unit_com = crud.queryAll(sql)
        if unit_com[0][0] is not None:
            os.remove('..\\file\\' + unit_com[0][0])
        file_name = file.update(request, "uploadFile")
        if file_name is None:
            return HttpResponse("没有上传文件！")
        # 附件名存在部门交流表中
        sql_update = "update unit_communication set enclosure_name = %s where id = %s"
        data = file_name, request.POST.get("id")
        crud.execute(sql_update, data)
        return HttpResponse("上传成功")
    else:
        return HttpResponseRedirect("不支持的请求方法")


# 附件下载
def download_attachment(request):
    if request.method == "GET":
        # 查询附件文件名
        sql = "select enclosure_name from unit_communication where id =" + request.GET.get("id")
        unit_comm = crud.queryAll(sql)
        if unit_comm[0][0] is not None:
            with open("..\\file\\" + unit_comm[0][0], 'rb') as f:
                file_byte = base64.b64encode(f.read())
            file_str = file_byte.decode('ascii')
            file = {'file': file_str}
            return JsonResponse(file, safe=False, json_dumps_params={'ensure_ascii': False})
        else:
            return HttpResponse("附件不存在")
    else:
        return HttpResponseRedirect("不支持的请求方法")


# 转化为json串
def to_json(result_list):
    results = []
    for lst in result_list:
        result = {'id': lst[0], 'other_unit_name': lst[1], 'other_director_name': lst[2],
                  'other_director_phone': lst[3], 'unit_name': lst[4], 'director_id': lst[5],
                  'director_name': lst[6],
                  'director_phone': lst[7], 'communication_time': lst[8], 'communication_place': lst[9],
                  'communication_title': lst[10], 'communication_summary': lst[11], 'unit_id': lst[12],
                  'enclosure_id': lst[13], 'create_time': lst[14], 'update_time': lst[15], 'recoder': lst[16],
                  }
        results.append(result)
    return results

数据库连接   crud.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from . import DbClient
import logging


def execute(sql, param=None):
    ret = None
    try:
        client = DbClient.DbClient("hsdj")
        print("执行sql:" + sql)
        if (param is None):
            ret = client.getCursor().execute(sql)
        else:
            ret = client.getCursor().execute(sql, param)
            client.commit()
    except Exception as excep:
        client.rollback()
        print(excep)
    finally:
        client.close()
    return ret


def queryAll(sql, param=None):
    try:
        client = DbClient.DbClient("hsdj")
        client.getCursor().execute(sql, param)
        print("执行sql:" + sql)
        result = client.getCursor().fetchall()
        return result
    except Exception as excep:
        print(excep)
    finally:
        client.close()


def test():
    sql = "SELECT * FROM polls_choice"
    data = queryAll("lm_test", sql)
    print(data)


def insert():
    sql = "insert into polls_choice(choice_text, votes, question_i) values('tttt',111,1)"
    data = execute("lm_test", sql)
    print(data)


if __name__ == '__main__':
    insert()

数据库连接池 DbClient.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from dbutils.pooled_db import PooledDB
import pymysql
# import logging


class DbClient:
    __instances = {}

    def __init__(self, dbName):
        if dbName not in self.__instances:
            self.__instances[dbName] = PooledDB(
                pymysql, 5, host="192.168.9.166", user='root',
                passwd="rootroot", database=dbName,
                cursorclass=pymysql.cursors.DictCursor)
            # self.__instances[dbName] = PooledDB(
            #     pymysql, 5, host="127.0.0.1", user='root',
            #     passwd="rootroot", database=dbName)
            print("构造数据库连接池！")

        self._conn = self.__instances[dbName].connection()
        self._cursor = self._conn.cursor()
        print("获取conn、cursor")

    def getConn(self):
        return self._conn

    def getCursor(self):
        return self._cursor

    def close(self):
        try:
            self._cursor.close()
            self._conn.close()
            print("关闭conn、cursor")
        except Exception as e:
            print(e)

    # def begin(self):
    #     self._conn.autocommit(0)
    #     print("开启事务")

    def commit(self):
        self._conn.commit()
        print("提交事务")

    def rollback(self):
        self._conn.rollback()
        print("回滚事务")

文件上传和查看 file.py

import base64
import os
import time

from django.http import HttpResponse


# 文件上传：保存到服务器的file文件夹中
def update(request, file):
    content = request.FILES.get(file, None)
    if not content:
        return None
    # 时间戳为文件名
    file_name = str(time.time()) + os.path.splitext(content.name)[1]
    path = '..\\file\\'
    position = os.path.join(path, file_name)
    # 是否存在file文件夹
    if not os.path.exists(path):
        os.makedirs(path)
    # 获取上传文件的文件名，并将其存储到指定位置
    storage = open(position, 'wb+', )
    # 分块写入文件
    for chunk in content.chunks():
        storage.write(chunk)
    storage.close()
    return file_name


# 图片或文件查看
def show(file_name):
    if file_name is not None:
        with open("..\\file\\" + file_name, 'rb') as f:
            file_byte = base64.b64encode(f.read())
        file_name = file_byte.decode('ascii')
    return file_name