choices = ((1, ‘男‘), (2, ‘女‘), (3, ‘其他‘))
gender = models.IntegerField(choices=choices, default=2)

res = models.User.objects.filter(id=1).first()
print(res.gender)   # 2
print(res.get_gender_display())  # 女  获取编号对应的注释

class User(models.Model):
    name = models.CharField(max_length=32)
    password = models.CharField(max_length=32)
    register_time = models.DateTimeField(auto_now_add=True)
    ...
    
    def save(self):
        # 在对象调用save保存到数据库之前,在这里可以拦截进行相应的操作
        super().save()
    
    def delete(self):
        # 在对象调用delete删除数据之前,在这里可以拦截进行相应的操作
        super().delete()

import os

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day5.settings")
    import django
    django.setup()
    from app01 import models

新增数据
models.User.objects.create(name=‘pok‘, age=31, register_time=‘2018-01-21‘)  # create新增数据
user_obj = models.User(name=‘lkj‘, age=20, register_time=‘2018-12-03‘)  # 对象绑定方法 新增数据
user_obj.save()
from datetime import datetime
ctime = datetime.now()   # 相当于 DateField(auto_now_add=True)
models.User.objects.create(name=‘xsd‘, age=18, register_time=ctime)

修改数据
user_obj = models.User.objects.filter(name=‘qq‘).first()  # 基于对象
user_obj.age = 41
user_obj.save()
models.User.objects.filter(name=‘ee‘).update(age=88)  # 基于QuerySet

删除数据
models.User.objects.filter(**kwargs).delete()  # 基于QuerySet，批量操作
user_obj = models.User.objects.filter(name=‘pok‘).first()   # 基于对象
user_obj.delete()

查询数据13条
models.User.objects.all()  # 查询所有数据
models.User.objects.filter(name=‘pok‘, age=31)  # 多个条件and关系
models.User.objects.get(name=‘vsf‘)  # 数据不存在会报错 User matching query does not exist.
models.User.objects.exclude(name=‘qq‘)  # 除了qq以外的
models.User.objects.order_by(‘name‘)  # name字段升序
models.User.objects.order_by(‘-name‘)  # name字段降序
models.User.objects.order_by(‘age‘)  # age字段升序
models.User.objects.order_by(‘-age‘)  # age字段降序
models.User.objects.order_by(‘age‘).reverse()  # 排序好的数据，reverse反序
models.User.objects.count()  # user表共有6条数据
models.User.objects.all().count()
models.User.objects.all().first()  # 返回第一条数据
models.User.objects.all()[0]  # 不支持负数索引 Negative indexing is not supported.
models.User.objects.all().last()  # 最后一条记录
models.User.objects.all().exists()  # QuerySet是否有数据
models.User.objects.filter(name=‘ee‘, age=15).exists()
models.User.objects.values(‘name‘)  # 包含搜索字段键值对字典的QuerySet对象<QuerySet [{‘name‘: ‘qq‘}, {‘name‘: ‘ww‘}]>
models.User.objects.values(‘name‘, ‘age‘)  # <QuerySet [ {‘name‘: ‘ww‘, ‘age‘: 22}]>
models.User.objects.values_list(‘name‘)  # 包含搜索字段值组成元组的QuerySet对象<QuerySet [(‘qq‘,), (‘pok‘,)]>
models.User.objects.values_list(‘name‘, ‘age‘)  # <QuerySet [(‘qq‘, 41), (‘pok‘, 31)]>
models.User.objects.values(‘age‘).distinct()  # 查询结果去重 <QuerySet [ {‘age‘: 18}, {‘age‘: 31}]>

# only 与 defer
res = models.Product.objects.values(‘name‘)  # 拿到name字典的列表
res = models.Product.objects.only(‘name‘)  # 拿到name字段数据的对象，可以再查其他字段，price*4  name*1（执行sql语句次数）
res = models.Product.objects.defer(‘name‘)  # 拿到除了name以外字段的对象，可以再查name， price*1  name*4
print(res)
for i in res:
    print(i.name)
print(type(res))

# 神奇的双下划线查询
models.User.objects.filter(age__gt=31)   # 年龄>31岁<QuerySet [<User: 对象qq>, <User: 对象ee>]>
models.User.objects.filter(age__gte=31)  # 年龄>=31岁<QuerySet [<User: 对象qq>,...]>
models.User.objects.filter(age__lt=31)  # 年龄<31岁<QuerySet [<User: 对象ww>,...]>
models.User.objects.filter(age__lte=31)  # 年龄<31岁<QuerySet [<User: 对象ww>,...]>
models.User.objects.filter(age__in=[15, 18])  # 年龄15或18的人<QuerySet [<User: 对象xsd>]>
models.User.objects.filter(age__range=[20, 30])  # 20<=年龄<=30的人<QuerySet [<User: 对象ww>,...]>
models.User.objects.filter(name__range=[‘a‘, ‘l‘])  # ‘a‘<name首字母‘l‘的人<QuerySet [<User: 对象ee>,...]>
models.User.objects.filter(register_time__year=‘2015‘)  # 注册年份为2015的人<QuerySet [<User: 对象pojE>]>
models.User.objects.filter(name__contains=‘e‘)  # name包含‘e’的人<QuerySet [<User: 对象ee>,...]>
models.User.objects.filter(name__icontains=‘e‘)  # 无视大小写 <QuerySet [<User: 对象ee>, <User: 对象pojE>]>
models.User.objects.filter(name__startswith=‘l‘)  # name 以‘l’开头
models.User.objects.filter(name__endswith=‘k‘)  # name 以‘q’开头

# 增
models.Book.objects.create(publish_id=1)

publish_obj = models.Publish.objects.filter(pk=1).first()
models.Book.objects.create(publish=publish_obj)


# 查
book_obj = models.Book.objects.filter(id=12).first()
publish_obj = models.Publish.objects.all()


# 改
book_obj = models.Book.objects.filter(pk=1).first()
book_obj.publish = new_publish_obj

book_obj = models.Book.objects.filter(pk=1).update(publish_id=2)
book_obj = models.Book.objects.filter(pk=1).update(publish=publish_obj)


# 删
models.Book.objects.filter(id=12).delete()

book_obj = models.Book.objects.filter(title=‘红楼梦‘).first()
book_obj.delete()

# book & author 建立绑定关系
book_obj = models.Book.objects.filter(pk=11).first()
book_obj.authors.add(1)
book_obj.authors.add(3, 2)
author_obj = models.Author.objects.filter(pk=4).first()
author_obj1 = models.Author.objects.filter(pk=2).first()
book_obj.authors.add(author_obj)
book_obj.authors.add(author_obj, author_obj1)

# book & author 修改绑定关系
book_obj = models.Book.objects.filter(pk=5).first()
book_obj.authors.set((2,1))
author_list = models.Author.objects.all()
book_obj = models.Book.objects.filter(pk=6).first()
book_obj.authors.set(author_list)

# book & author 删除绑定关系
book_obj = models.Book.objects.filter(pk=6).first()
book_obj.authors.remove(1, 3)
author_obj = models.Author.objects.all().first()
author_list = models.Author.objects.exclude(pk=2)
book_obj.authors.remove(author_obj)
book_obj.authors.remove(*author_list)

# 清空当前表记录对应的绑定关系
book_obj = models.Book.objects.filter(pk=3).first()
book_obj.authors.clear()

book = models.Book.objects.filter(title=‘三国演义‘).first()    # 一对多：正向
email = book.publish.email

book = models.Book.objects.filter(title=‘西游记’).first()    # 多对多：正向
author = book.authors.all()

author = models.Author.objects.filter(name=‘jason‘).first()     # 一对一：正向
phone = author.authordetail.phone    

publish = models.Publish.objects.filter(name=‘东方出版社‘).first()    # 一对多：反向
books = publish.book_set.all()    

authordetail = models.AuthorDetail.objects.filter(phone=110).first()  # 一对一：反向    
author = authordetail.author.name    

author = models.Author.objects.filter(name=‘jason‘).first()      # 多对多：反向
books = author.book_set.all()    

# 正向
addr = models.Book.objects.filter(title=‘三国演义‘).values(‘publish__addr‘,‘title‘)
author = models.Book.objects.filter(title=‘西游记‘).values(‘authors__name‘,‘title‘)    
au_addr = models.Author.objects.filter(name=‘json‘).values(‘authordetail__addr‘)    

# 反向
books = models.Publish.objects.filter(name=‘南方出版社‘).values(‘book__title‘)    
books = models.Author.objects.filter(name=‘json‘).values(‘book__title‘)    
author = models.AuthorDetail.objects.filter(phone=120).values(author__name‘)    

# 正向
phone = models.Book.objects.filter(title=‘三国演义‘).values(‘authors__authordetail__phone‘)    

# 反向
models.Author.objects.filter(name=‘jason‘).values(‘authordetail__phone‘)    
models.AuthorDetail.objects.filter(author__name=‘jason‘).values(‘phone‘)    
    
models.Publish.objects.filter(name=‘东方出版社‘).values(‘book__title‘,‘book__price‘)    
models.Book.objects.filter(publish__name=‘东方出版社‘).values(‘title‘,‘price‘)    
    
models.Publish.objects.filter(name=‘东方出版社‘,book__price__gt=400).values(‘book__title‘,‘book__price‘)    
models.Book.objects.filter(publish__name=‘东方出版社‘).values(‘title‘,‘price‘)    

from django.db.models import Max,Min,Count,Avg,Count
models.Book.objects.annotate(count_num=Count(‘authors‘)).values(‘title‘,‘count_num‘)

    # 统计每个出版社出版的书的平均价格
res = models.Publish.objects.annotate(avg_price=Avg(‘book__price‘)).values(‘name‘,‘avg_price‘)
print(res)
    # 统计每一本书的作者个数
res = models.Book.objects.annotate(count_num=Count(‘authors‘)).values(‘title‘,‘count_num‘)
print(res)
    # 统计出每个出版社卖的最便宜的书的价格
res = models.Publish.objects.annotate(min_price=Min(‘book__price‘)).values(‘name‘,‘min_price‘)
print(res)
    # 查询每个作者出的书的总价格
res = models.Author.objects.annotate(sum_price=Sum(‘book__price‘)).values(‘name‘,‘sum_price‘)
print(res)

from django.db.models import Max,Min,Count,Sum,Avg
    # 查询所有书籍的作者个数
res = models.Book.objects.filter(pk=3).aggregate(count_num=Count(‘authors‘))
print(res)
    # 查询所有出版社出版的书的平均价格
res = models.Publish.objects.aggregate(avg_price=Avg(‘book__price‘))
print(res)  # 4498.636
    # 统计东方出版社出版的书籍的个数
res = models.Publish.objects.filter(name=‘东方出版社‘).aggregate(count_num=Count(‘book__id‘))
print(res)

LOGGING = {
    ‘version‘: 1,
    ‘disable_existing_loggers‘: False,
    ‘handlers‘: {
        ‘console‘:{
            ‘level‘:‘DEBUG‘,
            ‘class‘:‘logging.StreamHandler‘,
        },
    },
    ‘loggers‘: {
        ‘django.db.backends‘: {
            ‘handlers‘: [‘console‘],
            ‘propagate‘: True,
            ‘level‘:‘DEBUG‘,
        },
    }
}

# 查询出卖出数大于库存数的商品
from django.db.models import F
ret=models.Product.objects.filter(maichu__gt=F(‘kucun‘))
print(ret)

# 将每个商品的价格提高100块
models.Product.objects.update(price=F(‘price‘)+100)

# 符串的拼接，参数位置决定了拼接前后，Value里面是要新增的拼接值
from django.db.models.functions import Concat
from django.db.models import Value
ret3=models.Product.objects.update(name=Concat(F(‘name‘),Value(‘新款‘)))



# 卖出数大于100 或者 价格小于100块的
from django.db.models import Q
models.Product.objects.filter(Q(maichu__gt=100)|Q(price__lt=100))

# 查询 库存数是100 并且 卖出数不是0 的产品
models.Product.objects.filter(Q(kucun=100)&~Q(maichu=0))

# 查询产品名包含新款， 并且库存数大于60的
models.Product.objects.filter(Q(kucun__gt=60), name__contains="新款")

# 售出一件商品，同时减少一件库存商品
from django.db.models import F
from django.db import transaction
# 开启事务处理
try:
    with transaction.atomic():
        models.Order.objects.create(num="110110111", product_id=1, count=1)
        models.Product.objects.filter(id=1).update(kucun=F("kucun")-1, maichu=F("maichu")+1)
except Exception as e:
    print(e)