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.youkuaiyun.com/postedit/85156355

 

表关联分3种：

1. 一对一：models.OneToOneField(其他表)

2. 一对多：models.ForeignKey(其他表)

3. 多对多：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)

 

 

三、多对多

# -*-  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, common
import logging

# from proApp import models          # 导出Excel import
from django.http import HttpResponse
import xlwt
from io import BytesIO
import os                         # 导出Excel import

from openpyxl import Workbook,load_workbook  # 导入 excel
from openpyxl.utils import get_column_letter
from openpyxl.compatimport range    # 导入 excel
import xlrd     #excel读工具

@base.checkLogin
def index(request):
    return render(request,"book.html")

# 查
def getBook(request):
    if request.method == "GET":
        page = int(request.GET.get('page',0))-1
        rows = int(request.GET.get('rows',-1))
        list = models.Book.objects.all().order_by("-publish_date")
        allList = bookInfo(list)
        total = len(allList)
        if page < 0 or rows == 0:
            json_data_list = allList
        else:
            p = common.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", '')
        price = request.POST.get("price")
        publisher = request.POST.get("publisher")
        author = request.POST.get("author")
        dateFrom = request.POST.get("dateFrom")
        dateTo = request.POST.get("dateTo")

        # 定一个字典用于保存前端发送过来的查询条件
        search_dict = dict()

        if dateFrom:
            search_sql = models.Book.objects.filter(publish_date__gte=dateFrom)
        else:
            search_sql = models.Book.objects

        if dateTo:
            search_sql = search_sql.filter(publish_date__lte=dateTo)

        if publisher and int(publisher)>-1:
            search_dict['publisher_id'] = publisher

        if author and int(author)>-1:
            search_dict['author'] = author

        # 序列化
        if name!="全部":
            list = search_sql.filter(name__contains=name).filter(**search_dict).order_by("-publish_date")
        else:
            list = search_sql.filter(**search_dict).order_by("-publish_date")

        allList = bookInfo(list)
        total = len(allList)
        p = common.Datagrid()
        json_data_list = p.page(page, rows, total, allList)
        return HttpResponse(json.dumps(json_data_list), content_type='application/json; charset=utf-8')

#  因获取后台数据前端不能直接使用，进行重组， 接收参数list: 数据库所有符合条件的数据
def bookInfo(list):
    allList = []
    for li in list:
        book_list = json.loads(serializers.serialize("json", li.author.all(), ensure_ascii=False))
        author_name = ''
        author_id = []
        author = []
        for i,a in enumerate(book_list):
            author_id.append(a['pk'])
            author_name += a['fields']['name'] + '、'
            author.append({"id":a['pk'],"name":a['fields']['name']})

        publisher = json.loads(serializers.serialize("json", models.Publisher.objects.filter(id=li.publisher_id), ensure_ascii=False))
        allList.append({
            "id": li.id,
            "name": li.name,
            "price": li.price,
            "saleNum": li.saleNum,
            "publisher": li.publisher_id,
            "publisher_str": publisher[0]['fields']['name'],
            "author_id": author_id,
            "author_name": author_name,
            "author": author_name,
            "publish_date": json.loads(json.dumps(li.publish_date, cls=common.DateEncoder)),
        })
    return allList

# 增
def addBook(request):
    name = request.POST.get("name")
    price = request.POST.get("price")
    saleNum = request.POST.get("saleNum")
    publisher = models.Publisher.objects.get(pk=request.POST.get("publisher"))
    authors_list = request.POST.getlist("author[]")
    dic = {
        'name': name,
        'price': price,
        'saleNum': saleNum,
        "publisher": publisher,
        # "publish_date": json.loads(json.dumps(time.time(), cls=DateEncoder)),
    }
    b1 = models.Book(**dic)
    b1.save()                                     # 普通插入的数据和外键插入的数据需要先save()
    b1 = models.Book.objects.get(name=name)       # 查出书名对象,也就是获取要插入的多对多数据项
    if len(authors_list) == 1:
        b1.author.add(authors_list[0])  # 多对多使用add方法进行插入
        b1.save()
    else:
        b1.author.add(*[authors_list])                # 循环插入用户选中的多个作者
        # for person in authors_list:
        #     b1.author.add(person)        # 多对多使用add方法进行插入
        b1.save()
    ret = {
        'success': True,
        'retCode': 200,
        'retMsg': "修改成功！"
    }
    return HttpResponse(json.dumps(ret), content_type='application/json')

# 删
def delBook(request):
    id = request.POST.get("id")
    models.Book.objects.filter(id=id).delete()
    ret = {
        'success': True,
        'retCode': 0,
        'retMsg': "删除成功！"
    }
    return HttpResponse(json.dumps(ret), content_type='application/json')

# 改
def modifyBook(request):
    try:
        id = request.POST.get("modifyId")
        name = request.POST.get("name")
        price = request.POST.get("price")
        saleNum = request.POST.get("saleNum")
        publisher = request.POST.get("publisher")
        author_list = request.POST.getlist("author[]")

        models.Book.objects.filter(id=id).update(name=name, price=price, saleNum=saleNum, publisher=publisher)
        book_obj = models.Book.objects.get(id=id)
        author_obj = models.Author.objects.filter(id__in=author_list)
        book_obj.author.set(author_obj)
        book_obj.save()
        ret = {
            'success': True,
            'retCode': 200,
            'retMsg': "修改成功！"
        }
    except Exception as e:
        logger = logging.getLogger('django')
        logger.info('-------------------------')
        logger.error(str(e))
        logger.warn('warn')
        logger.debug('debug')
        ret = {
            'success': False,
            'retCode': 200,
            'retMsg': str(e)
        }

    return HttpResponse(json.dumps(ret), content_type='application/json')

多对多：新增数据逻辑优化  获取数据必须是通过主键获取，这样才能确保唯一性

class Book(models.Model):
    id = models.FloatField(primary_key=True)   # 手动设置主键

def addBook(request):
    name = request.POST.get("name")
    price = request.POST.get("price")
    saleNum = request.POST.get("saleNum")
    publisher = models.Publisher.objects.get(pk=request.POST.get("publisher"))
    authors_list = request.POST.getlist("author[]")

    book = models.Book.objects.all()
    if len(book) > 0:
        maxId = models.Book.objects.latest('id').id
    else:
        maxId = 0
    maxId += 1

    dic = {
        'id': maxId,
        'name': name,
        'price': price,
        'saleNum': saleNum,
        "publisher": publisher,
        # "publish_date": json.loads(json.dumps(time.time(), cls=DateEncoder)),
    }
    b1 = models.Book(**dic)
    b1.save()                                     # 普通插入的数据和外键插入的数据需要先save()
    b1 = models.Book.objects.get(id=maxId)       # 查出书名对象,也就是获取要插入的多对多数据项
    if len(authors_list) == 1:
        b1.author.add(authors_list[0])  # 多对多使用add方法进行插入
        b1.save()
    else:
        for person in authors_list:     # 循环插入用户选中的多个作者
            b1.author.add(person)        # 多对多使用add方法进行插入
        b1.save()

    ret = {
        'success': True,
        'retCode': 200,
        'retMsg': "添加成功！"
    }
    return HttpResponse(json.dumps(ret), content_type='application/json')