昨日内容回顾 模型层 单表操作 1.增 user_obj = models.User.objects.create() user_obj = models.User() user_obj.save() 2.改 models.User.objects.filter().update() user_obj = models.User.objects.filter().first() user_obj.username = 'jason' user_obj.save() 3.查 models.User.objects.filter() models.User.objects.all() models.User.objects.get() 4.删 models.User.objects.filter().delete() user_obj = models.User.objects.filter().first() user_obj.delete() 13条查询方法 all() filter() get() values() # 列表套字典 values_list() # 列表套元组 first() last() count() exclude() distinct() order_by() exists() reverse() 神奇的双下划綫查询 价格大于 小于 大于等于 小于等于 在什么之间 或好几个选择 price__gt price__lt price__gte price__lte price__in price__range 书籍名称中包含 title__contains='p' title__icontains='p' 忽略大小写 创建的年份 create_time__year 多表操作 外键字段的增删改查 一对多 publish_id = 1 publish = publish_obj 多对多 add() set() remove() # 上面三个都支持 传数字或者是对象 并且都可以传多个 # 但是set必须接受一个可迭代对象 clear() # 不需要传参数 直接清空所有关系 跨表查询 规律:只要是queryset对象 就可以无限制的点queryset的方法!!! 正方向概念 正向:关联字段在当前表中 反向:关联字段不再当前表 结论:正向查询按字段,反向查询按表名小写 """ 不要一次性把orm写完,写一点看一点 """ # 基于对象的跨表查询(子查询) # 一次只拿一个对象 然后利用对象点的操作 完成数据的查询 # 正向 book_obj.publish.name book_obj.authors # App01.Author.None book_obj.authors.all() author_obj.author_detail.phone # 反向 """反向表名小写什么时候需要加_set(当获取的是多个对象的饿时候)""" publish_obj.book_set # App01.Book.None publish_obj.book_set.all() author_obj.book_set # App01.Book.None author_obj.book_set.all() author_detail_obj.author.name # 基于双下划綫的跨表查询(连表查询) models.Book.objects.filter().values('publish__name') models.Publish.objects.filter(book__title='三').values('name') models.Book.objects.filter().values('authors__author_detail__phone') # 只要表中有外键字段 你可以通过__无限制的跨表 F与Q F查询 从数据库中获取字段对应的数据 库存数大于卖出数 Q查询 与 filter(Q(),Q()) filter(Q()&Q()) 或 filter(Q()|Q()) 非 filter(~Q()) 补充 q = Q() q.connector = 'or' q.children.append(('title','三')) q.children.append(('price',666)) models.Book.objects.filter(q)
models_数据库表中数据类型对应,数据库查询优化templates
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script> {% load static %} <link rel="stylesheet" href="{% static 'bootstrap-3.3.7-dist/css/bootstrap.min.css' %}"> <script src="{% static 'bootstrap-3.3.7-dist/js/bootstrap.min.js' %}"></script> <style> table { margin-top: 10px; } </style> </head> <body> <nav class="navbar navbar-inverse"> <div class="container-fluid"> <!-- Brand and toggle get grouped for better mobile display --> <div class="navbar-header"> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false"> <span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <a class="navbar-brand" href="#">图书管理系统</a> </div> <!-- Collect the nav links, forms, and other content for toggling --> <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1"> <ul class="nav navbar-nav"> <li class="active"><a href="#">图书<span class="sr-only">(current)</span></a></li> <li><a href="#">杂志</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">更多 <span class="caret"></span></a> <ul class="dropdown-menu"> <li><a href="#">Action</a></li> <li><a href="#">Another action</a></li> <li><a href="#">Something else here</a></li> <li role="separator" class="divider"></li> <li><a href="#">Separated link</a></li> <li role="separator" class="divider"></li> <li><a href="#">One more separated link</a></li> </ul> </li> </ul> <form class="navbar-form navbar-left"> <div class="form-group"> <input type="text" class="form-control" placeholder="Search"> </div> <button type="submit" class="btn btn-default">Submit</button> </form> <ul class="nav navbar-nav navbar-right"> <li><a href="#">welcome!</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">更多操作 <span class="caret"></span></a> <ul class="dropdown-menu"> <li><a href="#">Action</a></li> <li><a href="#">Another action</a></li> <li><a href="#">Something else here</a></li> <li role="separator" class="divider"></li> <li><a href="#">Separated link</a></li> </ul> </li> </ul> </div><!-- /.navbar-collapse --> </div><!-- /.container-fluid --> </nav> <div class="container-fluid"> <div class="row"> <div class="col-md-3"> <div class="list-group"> <a href="#" class="list-group-item active"> 首页 </a> <a href="{% url 'list' %}" class="list-group-item">图书列表</a> <a href="#" class="list-group-item">出版社列表</a> <a href="#" class="list-group-item">作者列表</a> <a href="#" class="list-group-item">其他</a> </div> </div> <div class="col-md-9"> <div class="panel panel-primary"> <div class="panel-heading"> <h3 class="panel-title">图书管理系统 <span class="glyphicon glyphicon-home pull-right"></span></h3> </div> <div class="panel-body"> {% block content %} <div class="jumbotron"> <h1>欢迎来到最牛逼的图书管理系统</h1> <p>...</p> <p><a class="btn btn-primary btn-lg" href="#" role="button">点击就送豪华大礼包</a></p> </div> {% endblock %} </div> </div> </div> </div> </div> </body> </html>
{% extends 'home.html' %}
{% block css %}
{% endblock %}
{% block content %}
<a href="{% url 'add' %}" class="btn btn-success">新增书籍</a>
<table>
<thead>
<tr>
<th>序号</th>
<th>书名</th>
<th>价格</th>
<th>出版日期</th>
<th>出版社</th>
<th>作者</th>
<th>操作</th>
</tr>
</thead>
<tbody>
{% for book_obj in book_queryset %}
<tr>
<td>{{ forloop.counter }}</td>
{# forloop.counter的值是一个整数，表示的是循环的次数，而这个整数是从1开始计算的，所以当进行第一次循环的是时候forloop.counter的值是1#}
<td>{{ book_obj.title }}</td>
<td>{{ book_obj.price }}</td>
<td>{{ book_obj.publish_date|date:'Y-m-d' }}</td>
<td>{{ book_obj.publish.name }}</td>
<td>
{% for author_obj in book_obj.authors.all %}
{% if forloop.last %}
{{ author_obj.name }}
{% else %}
{{ author_obj.name }}
{% endif %}
{% endfor %}
</td>
<td>
<a href="{% url 'edit' book_obj.pk %}" class="btn btn-primary btn-sm">编辑</a>
<a href="{% url 'delete' book_obj.pk %}" class="btn btn-danger btn-sm">删除</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
{% block js %}
{% endblock %}
{% extends 'home.html' %}
{% block css %}
{% endblock %}
{% block content %}
<h3 class="text-center">添加书籍</h3>
<form action="" method="post">
<p>书名:<input type="text" name="title" class="form-control"></p>
<p>售价:<input type="text" name="price" class="form-control"></p>
<p>出版日期:<input type="date" name="publish_date" class="form-control"></p>
<p>出版社:
<select name="publish" id="" class="form-control">
{% for publish in publish_list %}
<option value="{{ publish.pk }}">{{ publish.name }}</option>
{% endfor %}
</select>
</p>
<p>作者:
<select name="authors" id="" class="form-control" multiple>
{% for author in author_list %}
<option value="{{ author.pk }}">{{ author.name }}</option>
{% endfor %}
</select>
</p>
<input type="submit" class="btn btn-success pull-right">
</form>
{% endblock %}
{% block js %}
{% endblock %}
{% extends 'home.html' %}
{% block css %}
{% endblock %}
{% block content %}
<h3 class="text-center">编辑书籍</h3>
<form action="" method="post">
<p>书名:<input type="text" name="title" class="form-control" value="{{ edit_obj.title }}"></p>
<p>售价:<input type="text" name="price" class="form-control" value="{{ edit_obj.price }}"></p>
<p>出版日期:<input type="date" name="publish_date" class="form-control"
value="{{ edit_obj.publish_date|date:'Y-m-d' }}"></p>
<p>出版社:
<select name="publish" id="" class="form-control">
{% for publish in publish_list %}
{# 当前编辑对象的出版社信息与网站所有的出版社进行比对 如果对上了 就加selected#}
{% if edit_obj.publish == publish %}
<option value="{{ publish.pk }}" selected>{{ publish.name }}</option>
{% else %}
<option value="{{ publish.pk }}">{{ publish.name }}</option>
{% endif %}
{% endfor %}
</select>
</p>
<p>作者:
<select name="authors" id="" class="form-control" multiple>
{% for author in author_list %}
{% if author in edit_obj.authors.all %}
<option value="{{ author.pk }}">{{ author.name }}</option>
{% else %}
<option value="{{ author.pk }}">{{ author.name }}</option>
{% endif %}
{% endfor %}
</select>
</p>
<input type="submit" class="btn btn-danger pull-right">
</form>
{% endblock %}
{% block js %}
{% endblock %}
""" Django settings for day56 project. Generated by 'django-admin startproject' using Django 1.11.11. For more information on this file, see https://docs.djangoproject.com/en/1.11/topics/settings/ For the full list of settings and their values, see https://docs.djangoproject.com/en/1.11/ref/settings/ """ import os # Build paths inside the project like this: os.path.join(BASE_DIR, ...) BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) # Quick-start development settings - unsuitable for production # See https://docs.djangoproject.com/en/1.11/howto/deployment/checklist/ # SECURITY WARNING: keep the secret key used in production secret! SECRET_KEY = 'ju7=#^!63r5+!tfz4zdh71p5&(80zq*p*a*tetrp7!zsitefp5' # SECURITY WARNING: don't run with debug turned on in production! DEBUG = True ALLOWED_HOSTS = [] # Application definition INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'app01.apps.App01Config', ] MIDDLEWARE = [ 'django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', # 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', ] ROOT_URLCONF = 'day56.urls' TEMPLATES = [ { 'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [os.path.join(BASE_DIR, 'templates')] , 'APP_DIRS': True, 'OPTIONS': { 'context_processors': [ 'django.template.context_processors.debug', 'django.template.context_processors.request', 'django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', ], }, }, ] WSGI_APPLICATION = 'day56.wsgi.application' # Database # https://docs.djangoproject.com/en/1.11/ref/settings/#databases DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'day55_1', 'USER':'root', 'PASSWORD':'llx20190411', 'HOST':'127.0.0.1', 'PORT':3306, 'CHARSET':'utf8' } } # Password validation # https://docs.djangoproject.com/en/1.11/ref/settings/#auth-password-validators AUTH_PASSWORD_VALIDATORS = [ { 'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator', }, { 'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator', }, { 'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator', }, { 'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator', }, ] # Internationalization # https://docs.djangoproject.com/en/1.11/topics/i18n/ LANGUAGE_CODE = 'en-us' TIME_ZONE = 'UTC' USE_I18N = True USE_L10N = True USE_TZ = True # Static files (CSS, JavaScript, Images) # https://docs.djangoproject.com/en/1.11/howto/static-files/ STATIC_URL = '/static/' STATICFILES_DIRS = [ os.path.join(BASE_DIR,'static') ] LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'handlers': { 'console':{ 'level':'DEBUG', 'class':'logging.StreamHandler', }, }, 'loggers': { 'django.db.backends': { 'handlers': ['console'], 'propagate': True, 'level':'DEBUG', }, } }
import pymysql
pymysql.install_as_MySQLdb()
from django.db import models # Create your models here. """ django虽然没有给你提供定义char字段的方法 但是给你暴露一个接口 用户可以自定义char字段 """ class MyChar(models.Field): def __init__(self, max_length, *args, **kwargs): self.max_length = max_length super().__init__(max_length=max_length,*args,**kwargs) def db_type(self,connection): return f'char({self.max_length})' class Book(models.Model): title = models.CharField(max_length=255) price = models.DecimalField(max_digits=8, decimal_places=2) publish_date = models.DateField(auto_now_add=True) # desc = MyChar(max_length=64, null=True) # # 库存数 # kucun = models.IntegerField(null=True) # # 卖出数 # maichu = models.IntegerField(null=True) publish = models.ForeignKey(to='Publish') # 默认是跟publish的主键字段做的一对多外键关联 authors = models.ManyToManyField(to='Author') # 虚拟字段 1.自动创建第三张表 2.帮助orm跨表查询 def __str__(self): return self.title class Publish(models.Model): name = models.CharField(max_length=32) addr = models.CharField(max_length=32) # email = models.EmailField() # 就是varchar(254) def __str__(self): return self.name class Author(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() author_detail = models.OneToOneField(to='AuthorDetail') def __str__(self): return self.name class AuthorDetail(models.Model): phone = models.BigIntegerField() addr = models.CharField(max_length=64) """ models.py中的模型类__str__方法 必须返回一个字符串形式数据!!! """ def __str__(self): return self.addr
同步
python3 manage.py makemigrations
python3 manage.py migrate
"""day56 URL Configuration The `urlpatterns` list routes URLs to views. For more information please see: https://docs.djangoproject.com/en/1.11/topics/http/urls/ Examples: Function views 1. Add an import: from my_app import views 2. Add a URL to urlpatterns: url(r'^$', views.home, name='home') Class-based views 1. Add an import: from other_app.views import Home 2. Add a URL to urlpatterns: url(r'^$', Home.as_view(), name='home') Including another URLconf 1. Import the include() function: from django.conf.urls import url, include 2. Add a URL to urlpatterns: url(r'^blog/', include('blog.urls')) """ from django.conf.urls import url from django.contrib import admin from app01 import views urlpatterns = [ url(r'^admin/', admin.site.urls), url(r'^$', views.home), # 书籍展示页面 url(r'^booklist/', views.book_list,name='list'), # 书籍新增 url(r'^add_book/', views.add_book,name='add'), # 书籍的删除 url(r'^delete_book/(?P<delete_id>\d+)', views.delete_book,name='delete'), # 书籍的编辑 url(r'edit_book/(?P<edit_id>\d+)', views.edit_book,name='edit'), ]
from django.shortcuts import render,reverse,redirect,HttpResponse from app01 import models # Create your views here. def home(request): return render(request,'home.html') def book_list(request): # 查询书籍表中所有的书籍 book_queryset = models.Book.objects.all() return render(request,'booklist.html',locals()) def add_book(request): if request.method == 'POST': # 获取用户提交的数据 title = request.POST.get('title') price = request.POST.get('price') publish_date = request.POST.get('publish_date') publish_id = request.POST.get('publish') authors_id = request.POST.getlist('authors') # 将数据保存到数据库 book_obj = models.Book.objects.create(title=title,price=price,publish_date=publish_date,publish_id=publish_id) # 去书籍和作者的关系表中 绑定关系 book_obj.authors.add(*authors_id) # # add(1,2,3,4) add(author_obj1,author_obj2) # 重定向到书籍展示页 return redirect(reverse('list')) # 给用户返回一个可以输入的添加页面 # 将网站所有的出版社和作者信息 传递给前端 展示 publish_list = models.Publish.objects.all() author_list = models.Author.objects.all() return render(request, 'add_book.html', locals()) def delete_book(request,delete_id): # 根据有名分组传递过来的id值 确定删除的数据对象 # 1.简单粗暴 直接delete删除 # 第一种 # delete_obj = models.Book.objects.filter(pk=delete_id).first() # delete_obj.delete() # 第二种 models.Book.objects.filter(pk=delete_id).delete() return redirect(reverse('list')) # 2.不直接删 给用户再次确认一次 问他是否真的要删 def edit_book(request,edit_id): # 编辑是基于已经有了的数据 进行一个修改操作 # 获取用户想要编辑的数据对象 edit_obj = models.Book.objects.filter(pk=edit_id).first() if request.method == 'POST': # 获取用户修改完的数据 title = request.POST.get('title') price = request.POST.get('price') publish_date = request.POST.get('publish_date') publish_id = request.POST.get('publish') authors_id = request.POST.getlist('authors') models.Book.objects.filter(pk=edit_id).update(title=title, price=price,publish_date=publish_date, publish_id=publish_id) edit_obj.authors.set(authors_id) # 重定向到展示页面 return redirect(reverse('list')) # 将用户想要编辑的数据 展示到前端 供用户修改 # 还需要将网站所有的出版社和作者传递给前端展示 publish_list = models.Publish.objects.all() author_list = models.Author.objects.all() return render(request, 'edit_book.html', locals())
from django.test import TestCase # Create your tests here. import os if __name__ == "__main__": os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day56.settings") import django django.setup() """数据库查询优化""" # orm内所有的语句操作 都是惰性查询:只会在你真正需要数据的时候才会走数据库,如果你单单只写orm语句时不会走数据库的 # 这样设计的好处 在于 减轻数据库的压力 from app01 import models # res = models.Book.objects.all() # print(res) # res = models.Book.objects.values('title') # # print(res) # for r in res: # print(r.title) # res = models.Book.objects.only('title') # # print(res) # for r in res: # # print(r.title) # 只走一次数据库查询 # print(r.price) # 当你点击一个不是only括号内指定的字段的时候 不会报错 而是会频繁的走数据库查询 # res1 = models.Book.objects.defer('title') # defer与only是相反的 # for r in res1: # defer会将不是括号内的所有的字段信息 全部查询出来封装对象中 # # 一旦你点击了括号内的字段 那么会频繁的走数据库查询 # print(r.price) """select_related与prefetch_related""" # select_related帮你直接连表操作 查询数据 括号内只能放外键字段 # res = models.Book.objects.all().select_related('publish') # for r in res: # print(r.publish.name) # res = models.Book.objects.all().select_related('publish__xxx__yyy__ttt') # print(res) # res = models.Book.objects.all() """ select_related:会将括号内外键字段所关联的那张表 直接全部拿过来(可以一次性拿多张表)跟当前表拼接操作 从而降低你跨表查询 数据库的压力 注意select_related括号只能放外键字段(一对一和一对多) res = models.Book.objects.all().select_related('外键字段1__外键字段2__外键字段3__外键字段4') """ """ prefetch_related 不主动连表""" res = models.Book.objects.prefetch_related('publish') """ 不主动连表操作(但是内部给你的感觉像是连表操作了) 而是将book表中的publish全部拿出来 在取publish表中将id对应的所有的数据取出 res = models.Book.objects.prefetch_related('publish') 括号内有几个外键字段 就会走几次数据库查询操作 """ for r in res: print(r.publish.name) """数据库查询优化:面试重点""" """only""" #只走一次 # res = models.Book.objects.only('title') # for r in res: # # print(r.price) # 频繁走数据库 # print(r.title) # 只走一次 """defer""" # 频繁走数据库 res = models.Book.objects.defer('title') # 把它对应信息封装到一个表中, # for r in res: # # print(r.price) # 只查询一次 # print(r.title) # 重复查询数据库 """"select_related and drefetch_related""" """select_related() 跨表查询 只查询一次""" """内部原理: 会将括号内 主要针对: 一对一, 一对多关系 """ # res = models.Book.objects.all() # 重复查询数据库 # res = models.Book.objects.all().select_related() # res = models.Book.objects.all().select_related('publish') # 只查一次 # print(res) # for r in res: # print(r.publish.name) """prefetch_related 不主动连表(内部感觉像连表)""" """ 内部操作: 子查询 """ # res = models.Book.objects.prefetch_related('publish') # # print(res) # for r in res: # print(r.publish.name) """ACID事务""" """原子性,一致性,隔离性,持久性""" from django.db import transaction try: # 开启事务 with transaction.atomic(): models.Book.objects.create(title='资治通鉴') models.Publish.objects.create(name='西山出版社') except BaseException as e: # 事务结束 print(e)
