8.Django之表单的查询
创建模型
from django.db import models # Create your models here. class Author(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() au = models.OneToOneField(to='AuthorDetail',to_field='id') class AuthorDetail(models.Model): id = models.AutoField(primary_key=True) address = models.CharField(max_length=32) tel = models.CharField(max_length=11) class Publish(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=32) addr = models.CharField(max_length=64) class Book(models.Model): id = models.AutoField(primary_key=True) title = models.CharField(max_length=32) publisher = models.ForeignKey(to='Publish',to_field='id',on_delete=models.CASCADE) authors = models.ManyToManyField(to='Author')
自己创建第三张表
lass Book(models.Model): title = models.CharField(max_length=32, verbose_name="书名") class Author(models.Model): name = models.CharField(max_length=32, verbose_name="作者姓名") # 自己创建第三张表，分别通过外键关联书和作者 class Author2Book(models.Model): author = models.ForeignKey(to="Author") book = models.ForeignKey(to="Book") class Meta: unique_together = ("author", "book")
ManytoManyField自动创建第三张表
class Book(models.Model): title = models.CharField(max_length=32, verbose_name="书名") # 通过ORM自带的ManyToManyField自动创建第三张表 class Author(models.Model): name = models.CharField(max_length=32, verbose_name="作者姓名") books = models.ManyToManyField(to="Book", related_name="authors") # related_name 反向操作时，使用的字段名，用于代替原反向查询时的'表名_set'。
表单的查询
def query(request): pub_obj = models.Publish.objects.filter(id=1)[0] print(pub_obj) # Publish object # 多对一插入数据方式1 models.Book.objects.create( title='红楼梦', publisher=pub_obj ) # 多对一插入数据方式2 models.Book.objects.create( title='三国演义', publisher_id=1 ) # 一对一插入数据方式 models.Author.objects.create( name='武松', age=18, au_id=1 ) # 多对多插入数据方式 book_obj = models.Book.objects.get(id=2) lzs = models.Author.objects.get(id=1) ws = models.Author.objects.get(id=2) book_obj.authors.add(*[1, 2]) book_obj.authors.add(lzs, ws) book_obj.authors.add(1, 2) # 一对一的正向查询 author_obj = models.Author.objects.get(name='武松') print(author_obj.au.address) # 景阳冈 print(author_obj.__dict__) author_detail_obj = models.AuthorDetail.objects.get(address='北京') print(author_detail_obj.author.name) # 武松 # 一对多正向查询 book_obj = models.Book.objects.get(title='红楼梦') print(book_obj.publisher.name) # 18期出版社 # 一对多反向查询 pub_obj = models.Publish.objects.get(name='18期出版社') print(pub_obj.book_set.all().values('title')) # <QuerySet [{'title': '红楼梦'}, {'title': '三国演义'}]> print(pub_obj.book_set) # app01.Book.None # # 多对多正向查询 book_obj = models.Book.objects.get(title='红楼梦') print(book_obj.authors.all().values('name')) # 多对多反向查询 author_obj = models.Author.objects.get(name='武松') print(author_obj.book_set.all().values('title')) book_list = models.Book.objects.all() print(book_list) # <QuerySet [<Book: Book object>, <Book: Book object>]> return HttpResponse('ok')
1. FBV、CBV 2. 数据库操作 class UserGroup(models.Model): """ 部门 3 """ title = models.CharField(max_length=32) class UserInfo(models.Model): """ 员工4 """ nid = models.BigAutoField(primary_key=True) user = models.CharField(max_length=32) password = models.CharField(max_length=64) age = models.IntegerField(default=1) # ug_id 1 ug = models.ForeignKey("UserGroup",null=True) - 跨表 正： 1. q = UserInfo.objects.all().first() q.ug.title 2. UserInfo.objects.values('nid','ug_id') UserInfo.objects.values('nid','ug_id','ug__title') 3. UserInfo.objects.values_list('nid','ug_id','ug__title') 反： 1. 小写的表名_set obj = UserGroup.objects.all().first() result = obj.userinfo_set.all() [userinfo对象,userinfo对象,] 2. 小写的表名 v = UserGroup.objects.values('id','title') v = UserGroup.objects.values('id','title','小写的表名称') v = UserGroup.objects.values('id','title','小写的表名称__age') 3. 小写的表名 v = UserGroup.objects.values_list('id','title') v = UserGroup.objects.values_list('id','title','小写的表名称') v = UserGroup.objects.values_list('id','title','小写的表名称__age') PS: 前面的所有数据都会显示 - 其他： UserInfo.objects.all() UserInfo.objects.filter(id=1,id=2) UserInfo.objects.all().first() UserInfo.objects.all().count() UserInfo.objects.all().update() UserInfo.objects.all().delete() UserInfo.objects.all()[1:19] 跨表： 正向： xxxx.filter(ut__title='超级用户').values('id','name','ut__title') 反向： xxxx.filter(表名称__title='超级用户').values('id','name','表名称__title') 3. 分页组件 - 内置 - 自定义 今日任务： 1. Django ORM操作 # 1.增删改查 # 2. 一般： # models.UserInfo.objects.filter(id__gt=1) # models.UserInfo.objects.filter(id__lt=1) # models.UserInfo.objects.filter(id__lte=1) # models.UserInfo.objects.filter(id__gte=1) # models.UserInfo.objects.filter(id__in=[1,2,3]) # models.UserInfo.objects.filter(id__range=[1,2]) # models.UserInfo.objects.filter(name__startswith='xxxx') # models.UserInfo.objects.filter(name__contains='xxxx') # models.UserInfo.objects.exclude(id=1) # 3. 排序 user_list = models.UserInfo.objects.all().order_by('-id','name') # 4. 分组 from django.db.models import Count,Sum,Max,Min # v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id')) # print(v.query) # v =models.UserInfo.objects.values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) # print(v.query) # v =models.UserInfo.objects.filter(id__gt=2).values('ut_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) # print(v.query) # 5. F，更新时用于获取原来的值 # from django.db.models import F,Q # models.UserInfo.objects.all().update(age=F("age")+1) # 6. Q，用于构造复杂查询条件 # 应用一： # models.UserInfo.objects.filter(Q(id__gt=1)) # models.UserInfo.objects.filter(Q(id=8) | Q(id=2)) # models.UserInfo.objects.filter(Q(id=8) & Q(id=2)) # 应用二： # q1 = Q() # q1.connector = 'OR' # q1.children.append(('id__gt', 1)) # q1.children.append(('id', 10)) # q1.children.append(('id', 9)) # # # q2 = Q() # q2.connector = 'OR' # q2.children.append(('c1', 1)) # q2.children.append(('c1', 10)) # q2.children.append(('c1', 9)) # # q3 = Q() # q3.connector = 'AND' # q3.children.append(('id', 1)) # q3.children.append(('id', 2)) # q2.add(q3,'OR') # # con = Q() # con.add(q1, 'AND') # con.add(q2, 'AND') # models.UserInfo.objects.filter(con) # 7. extra, 额外查询条件以及相关表，排序 models.UserInfo.objects.filter(id__gt=1) models.UserInfo.objects.all() # id name age ut_id models.UserInfo.objects.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None) # a. 映射 # select # select_params=None # select 此处 from 表 # b. 条件 # where=None # params=None, # select * from 表 where 此处 # c. 表 # tables # select * from 表,此处 # c. 排序 # order_by=None # select * from 表 order by 此处 models.UserInfo.objects.extra( select={'newid':'select count(1) from app01_usertype where id>%s'}, select_params=[1,], where = ['age>%s'], params=[18,], order_by=['-age'], tables=['app01_usertype'] ) """ select app01_userinfo.id, (select count(1) from app01_usertype where id>1) as newid from app01_userinfo,app01_usertype where app01_userinfo.age > 18 order by app01_userinfo.age desc """ result = models.UserInfo.objects.filter(id__gt=1).extra( where=['app01_userinfo.id < %s'], params=[100,], tables=['app01_usertype'], order_by=['-app01_userinfo.id'], select={'uid':1,'sw':"select count(1) from app01_userinfo"} ) print(result.query) # SELECT (1) AS "uid", (select count(1) from app01_userinfo) AS "sw", "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id" FROM "app01_userinfo" , "app01_usertype" WHERE ("app01_userinfo"."id" > 1 AND (app01_userinfo.id < 100)) ORDER BY ("app01_userinfo".id) DESC # 8. 原生SQL语句 from django.db import connection, connections cursor = connection.cursor() # connection=default数据 cursor = connections['db2'].cursor() cursor.execute("""SELECT * from auth_user where id = %s""", [1]) row = cursor.fetchone() row = cursor.fetchall() - extra - 原生SQL语句 - raw result = models.UserInfo.objects.raw('select * from userinfo') [obj(UserInfo),obj,] result = models.UserInfo.objects.raw('select id,1 as name,2 as age,4 as ut_id from usertype') [obj(UserInfo),obj,] v1 = models.UserInfo.objects.raw('SELECT id,title FROM app01_usertype',translations=name_map) # 9. 简单的操作 http://www.cnblogs.com/wupeiqi/articles/6216618.html 2. xss攻击 - 慎用 safe和mark_safe - 非要用，一定要过滤关键字 3. CSRF 4. 模板引擎 - 部分方法 - 自定义方法
