1.1 数据库迁移
-
创建数据库
create database db_name;
-
__init__.py
配置# Django低版本兼容性 import pymysql pymysql.version_info = (1, 4, 13, "final", 0) pymysql.install_as_MySQLdb()
-
setting.py
配置# 添加app INSTALLED_APPS = [ pass 'app01.apps.AppHomeConfig', ] # 连接数据库 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'db_name', # 要连接的数据库,连接前需要创建好 'USER': 'root', # 连接数据库的用户名 'PASSWORD': '******', # 连接数据库的密码 'HOST': '127.0.0.1', # 连接主机,默认本级 'PORT': 3306 # 端口 默认3306 } } # sql语句日志 LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'handlers': { 'console': { 'level': 'DEBUG', 'class': 'logging.StreamHandler', }, }, 'loggers': { 'django.db.backends': { 'handlers': ['console'], 'propagate': True, 'level': 'DEBUG', }, } }
-
app01/models.py
初始化表结构from django.db import models # ORM:class => pymysql => 数据库 class Book(models.Model): id = models.AutoField(primary_key=True) title = models.CharField(min_length=2, max_length=20, unique=True) # 字符串 gender = models.BooleanField() # 布尔 date = models.DateField() # 日期 price = models.DecimalField(max_digits=8, decimal_places=2) # 888888.88 # 一对多,自动生成字段:pub_id pub = models.ForeignKey(to='Publish', to_field='id', on_delete=models.CASCADE, null=True) # 允许该字段为空 # 多对多,自动生成第三张关系表:book_auth auth = models.ManyToManyField(to='Author') class Author(models.Model): # 一对一,自动生成字段:auth_detail_id detail = models.OneToOneField(to='Detail', to_field='id', on_delete=models.CASCADE) class Publish(models.Model): pass class Detail(models.Model): pass
-
终端指令(数据库迁移)
python3 manage.py makemigrations python3 manage.py migrate
1.2 记录-增
-
app01/views.py
from app01.models import * # 方法一 obj = Book(title='西游记', gender=True, date='2020-10-10', price=100, pub_id=10) obj.save() # 方法二(常用) obj = Book.objects.create(title='西游记', gender=True, date='2020-10-10', price=100, pub_id=10)
-
ForeignKey(一对多)
# 方法一:对pub_id直接赋值 obj = Book.objects.create(title='西游记', gender=True, date='2020-10-10', price=100, pub_id=1) # model对象 # 方法二 pub_obj = Publish.objects.filter(id=1).first() obj = Book.objects.create(title='西游记', gender=True, date='2020-10-10', price=100, pub=pub_obj) # model对象 # 扩展 print(obj.pub_id) # 通过Book表调用pub_id print(obj.pub) # model对象 print(obj.pub.id) # 通过Book表调用Publish表的id属性
-
M2M(多对多)
obj = Book.objects.create(title='西游记', gender=True, date='2020-10-10', price=100, pub_id=1) auth_obj1 = Author.objects.filter(name="a").first() auth_obj2 = Author.objects.filter(name="b").first() auth_obj = Author.objects.all() # 绑定方法一: obj.auth.add(auth_obj1, auth_obj1) obj.auth.add(*auth_obj) # 绑定方法二: obj.auth.add(1,2,3) obj.auth.add(*[1,2,3]) # 解绑 obj.auth.remove(*[1,2,3]) # 解绑所有 obj.auth.clear() # 更新修改 obj.auth.clear() obj.auth.add(*[1,2,3]) # 扩展 print(obj.auth.all()) # QuerySet类型 print(obj.auth.all().values("name"))
-
One2One(一对一)
# 参考一对多
1.3 记录-查
-
app01/views.py
from app01.models import * """ QuerySet类型(类似列表,内容是model对象) 支持for循环:for obj in obj_list: 支持索引:obj_list[1] """ # all obj_list = Book.objects.all() # 所有记录 # first、last obj = Book.objects.all().first() # 第一个 obj = Book.objects.all().last() # 最后一个 # filter obj_list = Book.objects.filter(title='西游记') # 过滤记录 obj_list = Book.objects.filter(title='西游记', price=100) # 两个条件同时满足 # get obj = Book.objects.get(title='西游记') # 有且只有一个结果时才有意义,其他情况报错 # exclude obj_list = Book.objects.filter(title='西游记') # 排除过滤 # order_by obj_list = Book.objects.all().order_by("price", "-id") # 价格正序排,价格相同,按id倒序排 # reverse obj_list = Book.objects.all() # 查询结果反响排序 # count n = Book.objects.all().count() # 表内记录计数,数字类型 # exist ret = Book.objects.all().exist() # 判断表内是否有数据,布尔类型 # values ret = Book.objects.all().values("title") # QuerySet类型,内容是多个字典{'title':'西游记'} ret = Book.objects.values("title") # 效果一样,返回所有记录"title"属性键值对 # value_list ret = Book.objects.all().values_list("title") # QuerySet类型,内容是多个元组('西游记') # distinct ret = Book.objects.all().values("title").distinct() # QuerySet类型,去除重复 ret = Book.objects.all().values_list("title").distinct() # QuerySet类型,去除重复
-
模糊查询(双下划线)
# __gt、__lt obj_list = Book.objects.filter(price__gt=10, price__lt=100) # 10<price<100 # __startswith obj_list = Book.objects.filter(title__startswith='西') # __contains、__icontains obj_list = Book.objects.filter(title__contains='游') obj_list = Book.objects.filter(title__icontains='游') # 不区分大小写 # __in obj_list = Book.objects.filter(price__in=[10,20,30]) # 多选
-
基于对象的跨表查询(子查询)
# 一对多 book_obj.pub # 正向按字段,model对象 pub_obj.book_set.all() # 反向按表名,QuerySet类型 # 多对多 book_obj.auth.all() # 正向按字段,QuerySet类型 auth_obj.book_set.all() # 反向按表名,QuerySet类型 # 一对一 auth_obj.detail # 正向按字段,model对象 detail_obj.auth # 反向按表名,model对象
-
基于双下划线的跨表查询(JOIN)
# 一对多 Book.objects.filter(title="西游记").values("pub__name") # 正向按字段,QuerySet类型 Publish.objects.filter(book__title="西游记").values("name") # 反向按表名,QuerySet类型 # 多对多 Book.objects.filter(title="西游记").values("auth__name") # 正向按字段,QuerySet类型 Author.objects.filter(book__title="西游记").values("name") # 反向按表名,QuerySet类型 # 一对一 Author.objects.filter(name="小明").values("detail__tel") # 正向按字段,QuerySet类型 Detail.objects.filter(author__name="小明").values("tel") # 反向按表名,QuerySet类型 # 扩展:连续跨表 Book.objects.filter(auth__detail__tel="110").values("title","pub__name") # QuerySet类型 Auth.objects.filter(detail__tel="110").values("book__title","book__pub__name")
-
聚合查询
from django.db.models import Avg,Max,Min,Count # aggregate ret = Book.objects.all().aggregate(Avg("price")) # 字典{'price_avg':100} ret = Book.objects.all().aggregate(avg_price=Avg("price")) # 字典{'avg_price':100} ret = Book.objects.all().aggregate(Max("price"), Min("price"))
-
分组查询
# 单表 # annotate Book.objects.values("pub_id").annotate(avg_price=Avg("price")) # 以出版社分组,QuerySet类型 Publish.objects.values("id").annocate(Count("name")) # 无意义 # 多表 Publish.objects.values("id").annocate(c=Count("book__title")) # 连表后,分组,QuerySet类型 Publish.objects.values("id").annocate(c=Count("book__title")).values("name","c") # 最终通过values取值
-
F查询、Q查询
from django.db.models import F, Q Book.objects.all().update(price=F("price")+10) Book.objects.filter(~Q(title="西游记")|Q(price=100))
-
去重复
user.roles.filter(permissions__isnull=False).values(permissions__url).distinct()
1.4 记录-删
-
app01/views.py
from app01.models import * # QuerySet类型.delete ret = Book.objects.filter(title='西游记').delete() # 返回元组 # model对象.delete ret = Book.objects.filter(title='西游记').first().delete()
1.5 记录-改
-
app01/views.py
from app01.models import * # QuerySet类型.update ret = Book.objects.filter(title='西游记').update(xx='xx') # 返回元组