一旦创建你了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}
网友评论