英文版http://books.agiliq.com/projects/django-orm-cookbook/en/latest/
中文版https://django-orm-cookbook-zh-cn.readthedocs.io/zh_CN/latest/query.html
查询和筛选
1. 如何查看Django ORM查询集的原生SQL?
>>> queryset = Event.objects.all()
>>> str(queryset.query)
SELECT "events_event"."id", "events_event"."epic_id",
"events_event"."details", "events_event"."years_ago"
FROM "events_event"
2. 如何在Django ORM中使用 OR 查询?
queryset = User.objects.filter(
first_name__startswith='R'
) | User.objects.filter(
last_name__startswith='D'
)
queryset
<QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>
from django.db.models import Q
qs = User.objects.filter(Q(first_name__startswith='R')|Q(last_name__startswith='D'))
3. 如何在Django ORM中使用 AND 查询?
queryset_1 = User.objects.filter(
first_name__startswith='R',
last_name__startswith='D'
)
queryset_2 = User.objects.filter(
first_name__startswith='R'
) & User.objects.filter(
last_name__startswith='D'
)
queryset_3 = User.objects.filter(
Q(first_name__startswith='R') &
Q(last_name__startswith='D')
)
4. 如何在Django ORM中使用 NOT 查询?
>>> from django.db.models import Q
>>> queryset = User.objects.filter(~Q(id__lt=5))
5. 如何从相同或不同的模型类中联合两个查询集结果?
相同的模型类
>>> q1 = User.objects.filter(id__gte=5)
>>> q1
<QuerySet [<User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]>
>>> q2 = User.objects.filter(id__lte=9)
>>> q2
<QuerySet [<User: yash>, <User: John>, <User: Ricky>, <User: sharukh>, <User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>]>
>>> q1.union(q2)
<QuerySet [<User: yash>, <User: John>, <User: Ricky>, <User: sharukh>, <User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]>
>>> q2.union(q1)
<QuerySet [<User: yash>, <User: John>, <User: Ricky>, <User: sharukh>, <User: Ritesh>, <User: Billy>, <User: Radha>, <User: sohan>, <User: Raghu>, <User: rishab>]>
不同的模型类
Hero.objects.all().values_list(
"name", "gender"
).union(
Villain.objects.all().values_list(
"name", "gender"
))
6. 如何选择同一查询集中的某些字段?
>>> queryset = User.objects.filter(
first_name__startswith='R'
).values('first_name', 'last_name')
>>> queryset
<QuerySet [{'first_name': 'Ricky', 'last_name': 'Dayal'}, {'first_name': 'Ritesh', 'last_name': 'Deshmukh'}, {'first_name': 'Radha', 'last_name': 'George'}, {'first_name': 'Raghu', 'last_name': 'Khan'}, {'first_name': 'Rishabh', 'last_name': 'Deol'}]
>>> queryset = User.objects.filter(
first_name__startswith='R'
).only("first_name", "last_name")
7. 如何在Django中使用子查询(内连接)?
>>> from django.db.models import Subquery
>>> users = User.objects.all()
>>> UserParent.objects.filter(user_id__in=Subquery(users.values('id')))
<QuerySet [<UserParent: UserParent object (2)>, <UserParent: UserParent object (5)>, <UserParent: UserParent object (8)>]>
8. 如何基于字段值比较标准来筛选字符集?
In [29]: User.objects.filter(last_name=F("first_name"))
Out[29]: <QuerySet [<User: Guido>]>
9. 如何筛选没有任何文件的FileField字段?
no_files_objects = MyModel.objects.filter(
Q(file='')|Q(file=None)
)
10. 如何在Django ORM中执行JOIN操作?
reporter__username=‘John’
>>> a1 = Article.objects.select_related('reporter') // Using select_related
>>> a1
<QuerySet [<Article: International News>, <Article: Local News>, <Article: Morning news>, <Article: Prime time>, <Article: Test Article>, <Article: Weather Report>]>
>>> print(a1.query)
SELECT "events_article"."id", "events_article"."headline", "events_article"."pub_date", "events_article"."reporter_id", "events_article"."slug", "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "events_article" INNER JOIN "auth_user" ON ("events_article"."reporter_id" = "auth_user"."id") ORDER BY "events_article"."headline" ASC
>>> a2 = Article.objects.filter(reporter__username='John')
>>> a2
<QuerySet [<Article: International News>, <Article: Local News>, <Article: Prime time>, <Article: Test Article>, <Article: Weather Report>]>
>>> print(a2.query)
SELECT "events_article"."id", "events_article"."headline", "events_article"."pub_date", "events_article"."reporter_id", "events_article"."slug" FROM "events_article" INNER JOIN "auth_user" ON ("events_article"."reporter_id" = "auth_user"."id") WHERE "auth_user"."username" = John ORDER BY "events_article"."headline" ASC
11. 如何使用Django ORM找到排行第二的记录?
>>> user = User.objects.order_by('-last_login')[1] // Second Highest record w.r.t 'last_login'
>>> user.first_name
'Raghu'
>>> user = User.objects.order_by('-last_login')[2] // Third Highest record w.r.t 'last_login'
>>> user.first_name
'Sohan'
12. 如何查找具有重复字段值的列?
>>> duplicates = User.objects.values(
'first_name'
).annotate(name_count=Count('first_name')).filter(name_count__gt=1)
>>> duplicates
<QuerySet [{'first_name': 'John', 'name_count': 3}]>
13. 如何从查询集中找到独一无二的字段值?
distinct = User.objects.values(
'first_name'
).annotate(
name_count=Count('first_name')
).filter(name_count=1)
records = User.objects.filter(first_name__in=[item['first_name'] for item in distinct])
这个和 User.objects.distinct(“first_name”).all() 不同, User.objects.distinct(“first_name”).all() 会获取遇到的不同的first_name时的第一条记录。
14. 如何使用Q对象进行复杂查询?
>>> queryset = User.objects.filter(
Q(first_name__startswith='R') & ~Q(last_name__startswith='Z')
)
15. 如何在Django ORM中使用聚合查询?
>>> from django.db.models import Avg, Max, Min, Sum, Count
>>> User.objects.all().aggregate(Avg('id'))
{'id__avg': 7.571428571428571}
>>> User.objects.all().aggregate(Max('id'))
{'id__max': 15}
>>> User.objects.all().aggregate(Min('id'))
{'id__min': 1}
>>> User.objects.all().aggregate(Sum('id'))
{'id__sum': 106}
16. 如何从模型中有效地选取一个随机对象?
def get_random():
return Category.objects.order_by("?").first()
In [8]: def get_random3():
...: max_id = Category.objects.all().aggregate(max_id=Max("id"))['max_id']
...: while True:
...: pk = random.randint(1, max_id)
...: category = Category.objects.filter(pk=pk).first()
...: if category:
...: return category
17. 如何在查询集中使用任意的数据库函数?(不懂)
from django.db.models import Func, F
Hero.objects.annotate(like_zeus=Func(F('name'), function='levenshtein', template="%(function)s(%(expressions)s, 'Zeus')"))
增删改
1. 如何一次创建多个对象?
>>> Category.objects.all().count()
2
>>> Category.objects.bulk_create(
[Category(name="God"),
Category(name="Demi God"),
Category(name="Mortal")]
)
[<Category: God>, <Category: Demi God>, <Category: Mortal>]
>>> Category.objects.all().count()
5
2. 如何复制一个现有的模型对象?
In [2]: Hero.objects.all().count()
Out[2]: 4
In [3]: hero = Hero.objects.first()
In [4]: hero.pk = None
In [5]: hero.save()
In [6]: Hero.objects.all().count()
Out[6]: 5
3. 如何确保只有一个对象被创建?
这常被用来做应用程序配置存储。
class Origin(models.Model):
name = models.CharField(max_length=100)
def save(self, *args, **kwargs):
if self.__class__.objects.count():
self.pk = self.__class__.objects.first().pk
super().save(*args, **kwargs)
4. 如何在保存时更新其他模型中的非规范化字段?
只介绍save用法,signals没有介绍。
class Hero(models.Model):
# ...
def save(self, *args, **kwargs):
if not self.pk:
Category.objects.filter(pk=self.category_id).update(hero_count=F('hero_count')+1)
super().save(*args, **kwargs)
class Villain(models.Model):
# ...
def save(self, *args, **kwargs):
if not self.pk:
Category.objects.filter(pk=self.category_id).update(villain_count=F('villain_count')+1)
super().save(*args, **kwargs)
5. 如何使用Django ORM执行类似truncate操作?(不懂)
class Category(models.Model):
# ...
@classmethod
def truncate(cls):
with connection.cursor() as cursor:
cursor.execute('TRUNCATE TABLE "{0}" CASCADE'.format(cls._meta.db_table))
6. Django在对象创建和更新时,会引发哪些信号?(不懂)
7. 如何将字符串转化为datetime并存入数据库?
>>> user = User.objects.get(id=1)
>>> date_str = "2018-03-11"
>>> from django.utils.dateparse import parse_date // Way 1
>>> temp_date = parse_date(date_str)
>>> a1 = Article(headline="String converted to date", pub_date=temp_date, reporter=user)
>>> a1.save()
>>> a1.pub_date
datetime.date(2018, 3, 11)
>>> from datetime import datetime // Way 2
>>> temp_date = datetime.strptime(date_str, "%Y-%m-%d").date()
>>> a2 = Article(headline="String converted to date way 2", pub_date=temp_date, reporter=user)
>>> a2.save()
>>> a2.pub_date
datetime.date(2018, 3, 11)
排序
1. 如果以升序或降序方式给查询集排序?
User.objects.all().order_by('date_joined', '-last_login')
2. 如何以不区分大小写的方式排序查询集?
>>> from django.db.models.functions import Lower
>>> User.objects.all().order_by(Lower('username')).values_list('username', flat=True)
<QuerySet ['Billy', 'John', 'johny', 'johny1', 'paul', 'Radha', 'Raghu', 'Ricky', 'rishab', 'Ritesh', 'sharukh', 'sohan', 'yash']>
3. 如何给两个字段排序?
User.objects.all().order_by(“is_active”, “-last_login”, “first_name”)
4. 如何给关联模型(使用外键)的一个字段排序?
class Category(models.Model):
name = models.CharField(max_length=100)
class Hero(models.Model):
# ...
name = models.CharField(max_length=100)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
Hero.objects.all().order_by(
'category__name', 'name'
)
5. 如何给注解字段排序?
Category.objects.annotate(
hero_count=Count("hero")
).order_by(
"-hero_count"
)
数据库模型
1. 如何建立一对一的关系模型?
from django.contrib.auth.models import User
class UserParent(models.Model):
user = models.OneToOneField(
User,
on_delete=models.CASCADE,
primary_key=True,
)
father_name = models.CharField(max_length=100)
mother_name = models.CharField(max_length=100)
>>> u1 = User.objects.get(first_name='Ritesh', last_name='Deshmukh')
>>> u2 = User.objects.get(first_name='Sohan', last_name='Upadhyay')
>>> p1 = UserParent(user=u1, father_name='Vilasrao Deshmukh', mother_name='Vaishali Deshmukh')
>>> p1.save()
>>> p1.user.first_name
'Ritesh'
>>> p2 = UserParent(user=u2, father_name='Mr R S Upadhyay', mother_name='Mrs S K Upadhyay')
>>> p2.save()
>>> p2.user.last_name
'Upadhyay'
2. 如何建立一对多的关系模型?
class Article(models.Model):
headline = models.CharField(max_length=100)
pub_date = models.DateField()
reporter = models.ForeignKey(User, on_delete=models.CASCADE, related_name='reporter')
def __str__(self):
return self.headline
class Meta:
ordering = ('headline',)
>>> u1 = User(username='johny1', first_name='Johny', last_name='Smith', email='johny@example.com')
>>> u1.save()
>>> u2 = User(username='alien', first_name='Alien', last_name='Mars', email='alien@example.com')
>>> u2.save()
>>> from datetime import date
>>> a1 = Article(headline="This is a test", pub_date=date(2018, 3, 6), reporter=u1)
>>> a1.save()
>>> a1.reporter.id
13
>>> a1.reporter
<User: johny1>
3. 如何建立多对多的模型关系?(不懂)
class User(AbstractUser):
tweet = models.ManyToManyField(Tweet, blank=True)
follower = models.ManyToManyField(settings.AUTH_USER_MODEL, blank=True)
pass
class Tweet(models.Model):
tweet = models.TextField()
favourite = models.ManyToManyField(settings.AUTH_USER_MODEL, blank=True, related_name='user_favourite')
def __unicode__(self):
return self.tweet
以上的模型能做些什么呢?
1)用户可以关注或取关其他用户。
2)用户可以他关注的用户的推文。
3)用户可以喜欢/不喜欢其他人的推文。
4. 如何在模型中包含自引用外键?
自引用外键可以有两种方法实现。
class Employee(models.Model):
manager = models.ForeignKey('self', on_delete=models.CASCADE)
# OR
class Employee(models.Model):
manager = models.ForeignKey("app.Employee", on_delete=models.CASCADE)
5. 如何转化已有的数据表到Django模型?
$ python manage.py inspectdb
$ python manage.py inspectdb > models.py
6. 如何为数据库视图添加模型?
create view temp_user as select id, first_name from auth_user;
class TempUser(models.Model):
first_name = models.CharField(max_length=100)
class Meta:
managed = False
db_table = "temp_user"
7. 如何创建可与任何实体相关联的通用模型(如类别或评论)?(不懂)
太难,略
8. 如何指定模型的表名?
class TempUser(models.Model):
first_name = models.CharField(max_length=100)
. . .
class Meta:
db_table = "temp_user"
9. 如何给模型字段指定列名?
class ColumnName(models.Model):
a = models.CharField(max_length=40,db_column='column1')
column2 = models.CharField(max_length=50)
def __str__(self):
return self.a
10. null=True 和 blank=True 的区别?
- null 和 blank 的默认值都是 False 。他们两者的值都在字段级别工作,即,我们是否要保持字段null还是blank。
2.null=True 将会设置字段的值为空,即没有数据。这基本上用于数据库列值。
date = models.DateTimeField(null=True)
3.blank=True 决定在表格中是否需要该字段。这包含了admin表单和你自己的表单。
title = models.CharField(blank=True) // title can be kept blank. In the database ("") will be stored.
4.null=True blank=True 这意味着这个字段在所有情况下是可选的。
epic = models.ForeignKey(null=True, blank=True) // The exception is CharFields() and TextFields(), which in Django are never saved as NULL. Blank values are stored in the DB as an empty string (‘’).
但也有个特殊例子,但你需要接受一个 BooleanField 的Null值时,请使用 NullBooleanField 。
11. 如何使用UUID替代ID作为主键?
import uuid
from django.db import models
class Event(models.Model):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
details = models.TextField()
years_ago = models.PositiveIntegerField()
>>> eventobject = Event.objects.all()
>>> eventobject.first().id
'3cd2b4b0c36f43488a93b3bb72029f46'
12. 如何使用Django的slug字段提高代码可读性?
from django.utils.text import slugify
class Article(models.Model):
headline = models.CharField(max_length=100)
. . .
slug = models.SlugField(unique=True)
def save(self, *args, **kwargs):
self.slug = slugify(self.headline)
super(Article, self).save(*args, **kwargs)
. . .
>>> u1 = User.objects.get(id=1)
>>> from datetime import date
>>> a1 = Article.objects.create(headline="todays market report", pub_date=date(2018, 3, 6), reporter=u1)
>>> a1.save()
// slug here is auto-generated, we haven't created it in the above create method.
>>> a1.slug
'todays-market-report'
13. 如果添加多个数据到Django应用?(难)
1.数据库配置相关的东西基本上在 settings.py 中被完成。所以,要给Django应用添加多个数据库,我们需要把他们添加到 DATABASES 的字典中。
DATABASE_ROUTERS = ['path.to.DemoRouter']
DATABASE_APPS_MAPPING = {'user_data': 'users_db',
'customer_data':'customers_db'}
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
},
'users_db': {
'NAME': 'user_data',
'ENGINE': 'django.db.backends.postgresql',
'USER': 'postgres_user',
'PASSWORD': 'password'
},
'customers_db': {
'NAME': 'customer_data',
'ENGINE': 'django.db.backends.mysql',
'USER': 'mysql_cust',
'PASSWORD': 'root'
}
}
2.对于多个数据库,我们最好聊聊 Database Router 。默认的路由方案如果一个数据库未指明,所有的查询都会返回到默认数据库。 Database Router 默认是 [] 。
class DemoRouter:
"""
A router to control all database operations on models in the
user application.
"""
def db_for_read(self, model, **hints):
"""
Attempts to read user models go to users_db.
"""
if model._meta.app_label == 'user_data':
return 'users_db'
return None
def db_for_write(self, model, **hints):
"""
Attempts to write user models go to users_db.
"""
if model._meta.app_label == 'user_data':
return 'users_db'
return None
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if a model in the user app is involved.
"""
if obj1._meta.app_label == 'user_data' or \
obj2._meta.app_label == 'user_data':
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure the auth app only appears in the 'users_db'
database.
"""
if app_label == 'user_data':
return db == 'users_db'
return None
3.各个模型将被修改为:
class User(models.Model):
username = models.Charfield(ax_length=100)
. . .
class Meta:
app_label = 'user_data'
class Customer(models.Model):
name = models.TextField(max_length=100)
. . .
class Meta:
app_label = 'customer_data'
4.使用多个数据库是的有用的命令:
$ ./manage.py migrate --database=users_db
测试
1. 如何断言一个使用了固定查询数量的函数?(没用过)
def test_number_of_queries(self):
User.objects.create(username='testuser1', first_name='Test', last_name='user1')
# Above ORM create will run only one query.
self.assertNumQueries(1)
User.objects.filter(username='testuser').update(username='test1user')
# One more query added.
self.assertNumQueries(2)
2. 如何通过在测试运行中重用数据库来加速测试?(没用过)
当我们执行命令 python manage.py test ,一个新的数据库每次会被创建。如果我们没有太多迁移,这并没有什么问题。 但是当我们有许多迁移时,这将耗费大量的时间在测试运行时来重新创建数据库。为了避免这种清况,我们可以重用旧数据库。
你可以通过添加 --keepdb 标识到测试命令后以防止数据库被破坏。这将保存在测试过程中保存测试数据库。如果数据库不存在,它首先会被创建。如果有任何迁移在最后一次测试运行后被添加,为保证同步,这些迁移会被应用。文章来源:https://www.toymoban.com/news/detail-427784.html
$ python manage.py test --keepdb
3. 如何从数据库中重载一个模型对象?(没用过)
通过使用 refresh_from_db() 方法,模型就可以从数据库中被从新加载。这在测试中非常有用。例如:文章来源地址https://www.toymoban.com/news/detail-427784.html
class TestORM(TestCase):
def test_update_result(self):
userobject = User.objects.create(username='testuser', first_name='Test', last_name='user')
User.objects.filter(username='testuser').update(username='test1user')
# At this point userobject.val is still testuser, but the value in the database
# was updated to test1user. The object's updated value needs to be reloaded
# from the database.
userobject.refresh_from_db()
self.assertEqual(userobject.username, 'test1user')
到了这里,关于【Django】Django ORM Cookbook--20230427的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!