美文网首页
Model 之Making queries

Model 之Making queries

作者: 低吟浅唱1990 | 来源:发表于2017-06-15 21:21 被阅读16次

一旦创建你了data models,Django可以通过抽象database API操作数据库的"增"、"删"、"改"、"查"。

from django.db import models
class Blog(models.Model):
    name = models.CharField(max_length=200)
    tagline = models.TextField()
    class Meta:
        db_table = 'doc_blog'
    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField()
    class Meta:
        db_table='doc_author'
    def __str__(self):
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    n_comments = models.IntegerField()
    n_pingbacks = models.IntegerField()
    rating = models.IntegerField()
    class Meta:
        db_table = 'doc_entry'
    def __str__(self):
        return self.headline

创建以上三个数据表,用以上表介绍database API

>>>import Blog,Author
>>>b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')#创建一个Blog实例,在数据库中表示一条记录
>>>b.save()  #将数据保存到表中

#如果在数据库中早已存在这条记录,那么以下两天代码表示更新该条记录的name字段
>>>b.name = 'Test Blog'
>>>b.save() 
#另外"增"
>>>Author.objects.create(name="Joe",email='zhang@qq.com')
#首先尝试获取,不存在就创建,可以防止重复
>>>Author.objects.get_or_create(name='Joe',email='zhang@qq.com')

>>>joe = Author()
>>>joe.name = 'Joe'
>>>joe.email='zhang@qq.com'
>>>joe.save()

在添加entry的记录中添加一个author的时候,数据库必须先要有一个entry的记录才能添加,不然会报错

>>>entry = Entry.objects.get(pk=1)
>>>entry.authors.add(joe)

获取对象的方法

#获取全部对象
>>>all_entries =Entry.objects.all()
>>>Entry.objects.all()[:10] #切片操作,获取10个人,不支持负索引
#查找特定的单个对象  LIMIT 10
>>>Entry.objects.get(pk=1)  #获取主键id=1的对象 使用get要保证有且有唯一一个
#过滤一部分对象
>>>Author.objects.filter(name='Joe') 
>>>Author.objects.filter(name__exact='Joe') #获取名字严格为Joe的人
>>>Author.objects.filter(name__iexact='Joe') #不区分大小写
>>>Author.objects.filter(name__contains='Joe') #名字中包含Joe的
>>>Author.objects.filter(name__icontains='Joe') #名字中包含Joe且不区分大小写
>>>Author.objects.exclude(name__contains='Joe')#排除包含名字为Joe的对象

>>>Chaining filters 过滤链
选择headline以What开头,并且pub_date大于等于2005-01-30但是排除今天在内
>>>Entry.objects.filter(
    headline__startswith='What'
).exclude(
    pub_date__gte=datetime.date.today()
).filter(
    pub_date__gte=datetime(2005,1,30)
)

QuerySets are lazy.懒查询

>>>q=Entry.objects.filter(headline__startswith='What')
>>>q=q.filter(pub_date__lte=datetime.date.today())
>>>q=q.exclude(body_text__icontains='food')
>>>print(q)
以上四行代码,只有在最后一行才真正访问数据库

//指定条件 相当于SQL的 WHERE字句
Field lookups 遵守这个范式:field__lookuptype=value
字段__查找类型 = 值 (两个下划线)

Entry.objects.filter(pub_date__lte='2006-01-01')
相当于
SELECT * FROM blog_entry WHERE pub_date <= '2006-01-01';

Relationships in lookups

>>>Entry.objects.filter(blog__name='Beatles Blog')#返回Blog名为Beatles Blog的Entry
>>>Blog.objects.filter(entry__headline__contains='Lennon') 返回Entry的headline中包含Lennon的所有Blog对象(记录)
>>>Blog.objects.filter(entry__authors__name='Lennon')   #在通过多重关系进行查找的时候,如果没有找到相应的记录,会返回一个empty而不是报错。 在上例中如果没有一个author与Entry关联,也不会报错。
>>>
Blog.objects.filter(entry__authors__name__isnull=True)  #isnull的处理 本例中返回没有author用空名字的和author本身为空的Blog

在复杂关系中 filter(),exclude()容易引起歧义的地方

>>>Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008)
>>>Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008)
>>>第一种表示返回:一个entry满足两个调节所关联的所有Blog
>>>第二种表示返回:返回所有的Blog,这个Blog关联的Entry能够满足条件,不要求同一个entry同时满足两个条件。因为Blog与Entry是一对多的关系。
>>>Blog.objects.exclude(entry__headline__contains='Lennon', entry__pub_date__year=2008,)
>>>不包括以上两种情况的Blog

>>>Blog.objects.exclude( entry__in=Entry.objects.filter(
headline__contains='Lennon',pub_date__year=2008, ))
>>>与第一种情况是相对的

F()

用于在同一条记录中不同字段之间的查找(同一个对象的不同属性之间的比较)

>>>from django.db.models import F
>>>Entry.objects.filter(n_comments__gt=F('n_pingbacks'))
>>>返回所有的n_comments>=n_pingbacks个数的Entry
可以在F()函数中加减乘除
>>>Entry.objects.filter(n_comments__gt=F('n_pingbacks')*2)
>>>Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks'))
>>>使用双下划线可以访问相关联的对象
>>>Entry.objects.filter(authors__name=F('blog__name'))#返回所有的author的name与blog的name相同的Entry
>>> from datetime import timedelta
>>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3))  #返回所有的mod_date在pub_date三天之后的Entry

Q objects

相当于SQL中的LIKE

>>>from django.db.models import Q
>>>Q(question__startswith='What')
Q objects可已用 & 和 | 组合
>>>Q(question__startswith='Who') | Q(question__startswith='What')
>>>相当于 
WHERE question LIKE 'Who%' OR question LIKE 'What%'
>>>Q(question__startswith='Who') | ~Q(pub_date__year=2005)
>>>相当于 where question LIKE 'Who%' or not (pub_date between 2005-01-01 and 2005-12-31)

Deleting objects

删除对象
先找出来,再删除

>>>e = Entry.objects.get(pk=1)
>>>e.delete()
>>>(1, {'weblog.Entry': 1})

Copying model instances

复制model实例,没有内置的方法直接复制一个model实例,但是可以通过设置pk=None来完成

>>>blog = Blog(name='Joe',tagline='It is easy')
>>>blog.save()
>>>blog.pk=None
>>>blog.save()
继承Blog 数据库中有两个表
class ThemeBlog(Blog):
    theme = models.CharField(max_length=200)

django_blog = ThemeBlog(name='Django',tagline='D is easy',theme='python')
django_blog.save()   #django_blog.pk ==3
复制
>>>django_blog.pk=None
>>>django.id = None
>>>django.save()    #django_blog.pk ==4
复制model实例的过程不会复制relations
entry =Entry.objects.all()[0]
authors = entry.authors.all()

entry.pk=None
entry.save()  #保存了Entry的一个实例,但是没有保存关系
entry.authors.set(authors)  #添加关系

批量更新 适用于 .all() .filter() .exclude() 等后面

>>>Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')

根据某个字段排序

Entry.objects.all().order_by(pub_date__year)

Aggregation 聚合

以以下model构建实例

class Publisher(models.Model):
    name = models.CharField(max_length=300)
    num_awards = models.IntegerField()
    class Meta:
        db_table='doc_publisher'
    def __str__(self):
        return self.name

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10,decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher)
    pubdate = models.DateField()

    class Meta:
        db_table = 'doc_book'

    def __str__(self):
        return self.name

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)
    registered_users = models.PositiveIntegerField()

    class Meta:
        db_table='doc_store'

    def __str__(self):
        return self.name

1.pic.jpg 2.pic.jpg
>>>Book.objects.count()  
>>>4
>>>Book.objects.filter(publisher__name='清华大学').count()
>>>1
>>>from django.db.models import Avg
>>>Book.objects.all().aggregate(Avg('price'))
>>>{'price__avg':108.75}

>>>from django.db.models import F,FloatField,Sum
>>>Book.objects.all().aggregate(price_per_page=Sum(F('price')/F('pages'),outputfield=FloatField()))
>>>{'price_per_page':0.764732}

>>>from django.db.models import Count
>>>pubs = Publisher.objects.annotate(num_books=Count('book'))
>>>pubs
>>><QuerySet [<Publisher:中国工信>,<Publisher:清华大学>,<Publisher:中国电力>,<Publisher:机械工业>]>
>>>pubs[0].num_books
>>>1
>>>SQL 相当于
>>>SELECT doc_publisher.id,doc_publisher.name,doc_publisher.num_awards,COUNT(doc_book.id) AS num_books FROM doc_publisher LEFT
 OUTER JOIN doc_book ON 
doc_publisher.id=doc_book.publisher_id GROUP BY 
doc_publisher.id

Book与Author是多对多的关系,现在需要找到每本书的作者书

>>>q= Book.objects.annotate(Count('authors'))
#Count('authors') 之后每个book都有一个extra属性被添加 authors.__count
>>>q[0].authors.count()  #q[0].authors.__count
>>>2
>>>Author.objects.aggregate(average_rating=Avg('book__rating'))
>>>book__rating__avg的属性

>>>定义输出数据的字段
>>> q = Book.objects.annotate(num_authors=Count('authors')) >>> q[0].num_authors
>>>2
>>> q[1].num_authors
>>>1

双下滑线 聚合相关model

>>>from django.db.models import Max,Min
>>>Store.objects.annotate(min_price=Min('books__price'),max_price=Max('books__price'))
>>>返回Store实例,联合Book(通过多对多关系),在Book的price字段上聚合,产生最大、小的价格值
>>>>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))  #联合了Book,Author两个model

Aggregations与filter结合使用

>>>from django.db.models import Count, Avg
>>>Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

相当于SQL  filter表示where部分 annotate表示外联部分

SELECT doc_book.id,doc_book.`name`,doc_book.pages,
doc_book.price,doc_book.rating,doc_book.publisher_id,doc_book.pubdate,COUNT(doc_book_authors.author_id) AS num_authors
 FROM doc_book LEFT OUTER JOIN doc_book_authors ON 
doc_book.id = doc_book_authors.author_id WHERE
 doc_book.`name` LIKE BINARY 'Django%' GROUP BY
 doc_book.id;

先聚合在过滤

>>>Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

SELECT doc_book.id,doc_book.`name`,doc_book.pages,
doc_book.price,doc_book.rating,doc_book.publisher_id,doc_book.pubdate,COUNT(doc_book_authors.author_id) AS num_authors
 FROM doc_book LEFT OUTER JOIN doc_book_authors ON 
doc_book.id = doc_book_authors.author_id GROUP BY
 doc_book.id HAVING COUNT(doc_book_authors.author_id)>1

>>>对于复杂的ORM还是通过 str(queryset.query)查看SQL语句在确定

order_by()

排序 Annotations can be used as a basis for ordering.可以在annotate的基础上排序

>>>Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
class Item(models.Model):
    name = models.CharField(max_length=10) 
    data =   models.IntegerField()
    class Meta:
        ordering=['name']
>>>这种方法也能够控制排序
>>>Item.objects.values("data").annotate(Count("id")) 当这么写的时候实际上是按照group by distinct (data, name)聚合的
正确的写法  清除排序的字段
>>>Item.objects.values("data").annotate(Count("id")).order_by()

values()

values()显示model返回的字段在一定的范围内

Author.objects.values('name')
>>>
<QuerySet [{'name':'Joe'},{'name':'John'}]>
>>>Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')
>>>返回的其中的一个{'name':'Joe','average_rating':1.0}

相关文章

网友评论

      本文标题:Model 之Making queries

      本文链接:https://www.haomeiwen.com/subject/ttdrqxtx.html