业务逻辑接口文件 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