django-mysql 表关联操作数据库，增删改查功能
相关网址：https://www.cnblogs.com/yangmv/p/5327477.html
相关网址：https://www.cnblogs.com/PythonHomePage/p/7634394.html
django-ajax相关网址：https://www.cnblogs.com/yjq520/p/9024767.html
刘江： http://www.liujiangblog.com/course/django/130
我得开发过程bug: https://mp.****.net/postedit/85156355
表关联分3种：
-
一对一：models.OneToOneField(其他表)
-
一对多：models.ForeignKey(其他表)
-
多对多：models.ManyToManyField(其他表)
一对多：一旦确定一对多的关系，在多的一方(book)创建关联字段publish_id
多对多：一旦确定多对多的关系，创建第三张表，比如Author2Book表，字段分别是id，Book_id, Author_id
一对一：两张表其实就是一张表，在任意一张表创建关联字段
一、一对一
二、一对多
增
models.UserInfo.objects.create(user='yangmv',pwd='123456')
或者
obj = models.UserInfo(user='yangmv',pwd='123456')
obj.save()
或者
dic = {'user':'yangmv','pwd':'123456'}
models.UserInfo.objects.create(**dic)
删
models.UserInfo.objects.filter(user='yangmv').delete()
改
models.UserInfo.objects.filter(user='yangmv').update(pwd='520')
models.Article.objects.filter(id=id).update(title=title, content=content) # 同时改多个
或者
obj = models.UserInfo.objects.get(user='yangmv')
obj.pwd = '520'
obj.save()
查
models.UserInfo.objects.all()
models.UserInfo.objects.all().values('user') #只取user列
models.UserInfo.objects.all().values_list('id','user') #取出id和user列，并生成一个列表
models.UserInfo.objects.get(id=1)
models.UserInfo.objects.get(user='yangmv')
#全匹配
publish_date = request.POST.get("publish_date")
update_time = request.POST.get("update_time")
search_dict = dict()
if publish_date:
search_dict['publish_date'] = publish_date
if update_time:
search_dict['update_time'] = update_time
search_sql = models.Article.objects.filter(**search_dict)
# 全匹配 + 模糊匹配
search_sql = models.Article.objects.filter(**search_dict).filter(title__contains=title).filter(content__contains=content)# 示例：查询a表中id大于4，以‘张’开头，或者价格大于10000的数据
from django.db.models import Q
a.objects.filter((Q(name__startwith='张'),Q(id__gt=4))|Q(price__gt=10000))案例：
# -*- coding:utf-8 -*-
from django.shortcuts import render
from django.http import HttpResponse
from django.core import serializers
import json
from proApp import models, base
from proApp.commonimport DateEncoder, Datagrid
@base.checkLogin
def index(request):
return render(request,"author.html")
# 查
def getAuthor(request):
if request.method == "GET":
page = int(request.GET.get('page',''))-1
rows = int(request.GET.get('rows',''))
list = models.Author.objects.all().order_by("-id")
allList = []
for li in list:
allList.append({
"id": li.id,
"name": li.name,
"email": li.email,
"sex": li.sex,
"depart": li.depart_id,
"phone": li.phone,
"account": li.account,
"publish_date": json.loads(json.dumps(li.publish_date, cls=DateEncoder)),
})
total = len(allList)
p = Datagrid()
json_data_list = p.page(page, rows, total, allList)
return HttpResponse(json.dumps(json_data_list), content_type='application/json; charset=utf-8')
if request.method == "POST":
page = int(request.POST.get('page', '')) - 1
rows = int(request.POST.get('rows', ''))
name = request.POST.get("name", '')
sex = request.POST.get("sex")
depart = request.POST.get("depart")
dateFrom = request.POST.get("dateFrom")
dateTo = request.POST.get("dateTo")
# 定一个字典用于保存前端发送过来的查询条件
search_dict = dict()
if dateFrom:
search_sql = models.Author.objects.filter(publish_date__gte=dateFrom)
else:
search_sql = models.Author.objects
if dateTo:
search_sql = search_sql.filter(publish_date__lte=dateTo)
if int(sex)>-1:
search_sql = search_sql.filter(sex__contains=sex)
if (depart and int(depart)>-1):
search_sql = search_sql.filter(depart=depart)
# 序列化
list = search_sql.filter(name__contains=name).order_by("-id")
allList = []
for li in list:
allList.append({
"id": li.id,
"name": li.name,
"email": li.email,
"sex": li.sex,
"depart": li.depart_id,
"phone": li.phone,
"account": li.account,
"publish_date": json.loads(json.dumps(li.publish_date, cls=DateEncoder)),
})
total = len(allList)
p = Datagrid()
json_data_list = p.page(page, rows, total, allList)
return HttpResponse(json.dumps(json_data_list), content_type='application/json; charset=utf-8')
# 增
def addAuthor(request):
name = request.POST.get("name")
email = request.POST.get("email")
sex = request.POST.get("sex")
depart = models.Department.objects.get(pk=request.POST.get("depart"))
phone = request.POST.get("phone")
account = request.POST.get("account")
dic = {
'name': name,
'email': email,
'sex': sex,
'depart': depart,
'phone': phone,
'account': account
}
models.Author.objects.create(**dic)
ret = {
'success': True,
'retCode': 0,
'retMsg': "Author添加成功！"
}
return HttpResponse(json.dumps(ret), content_type='application/json')
# 删
def delAuthor(request):
id = request.POST.get("id")
models.Author.objects.filter(id=id).delete()
ret = {
'success': True,
'retCode': 0,
'retMsg': "Author删除成功！"
}
return HttpResponse(json.dumps(ret), content_type='application/json')
# 改
def modifyAuthor(request):
id = request.POST.get("modifyId")
name = request.POST.get("name")
email = request.POST.get("email")
sex = request.POST.get("sex")
depart = models.Department.objects.get(pk=request.POST.get("depart"))
phone = request.POST.get("phone")
account = request.POST.get("account")
# publish_date = models.DateTimeField(u'发布时间', auto_now_add=True, editable=True, null=True, blank=True)
models.Author.objects.filter(id=id).update(name=name, email=email, sex=sex, depart=depart, phone=phone, account=account )
ret = {
'success': True,
'retCode': 0,
'retMsg': "Author修改成功！"
}
return HttpResponse(json.dumps(ret), content_type='application/json')分页功能：common.py
# 表格
class Datagrid():
def __init__(self):
self.rowPageList = []
self.json_data_list = {}
# 分页处理
def page(self, page, rows, total, allList): # page: 当前页码 rows：表格1页面表格行数 total：所有数据len, allList：所有数据
rowPageList = []
json_data_list = {}
try:
if (page == 0): # 第一页
page = 1
if (rows > len(allList)): # 所有数据未达到一页行数时
json_data_list = {
'ret': {
'success': True,
'retCode': 200,
'retMsg': "查询成功！"
},
'rows': allList,
'total': total
}
else: # 所有数据超过一页行数时
for s in range(page * rows):
rowPageList.append(allList[s])
json_data_list = {
'ret': {
'success': True,
'retCode': 200,
'retMsg': "查询成功！"
},
'rows': rowPageList,
'total': total
}
else: # 非第一页
ss = allList[page * rows:]
if (len(ss) < rows): # 当前页截取数据 低于 一页行数
json_data_list = {
'ret': {
'success': True,
'retCode': 200,
'retMsg': "查询成功！"
},
'rows': ss,
'total': total
}
else: # 当前页截取数据超过 一页行数
for i in range(page * rows):
rowPageList.append(ss[i])
json_data_list = {
'ret': {
'success': True,
'retCode': 200,
'retMsg': "查询成功！"
},
'rows': rowPageList,
'total': total
}
return json_data_list
except Exception as e:
print(e)