Django里数据库sql函数
example model:
class Author(models.Model):
name = models.CharField(max_length=50)
age = models.PositiveIntegerField(null=True, blank=True)
alias = models.CharField(max_length=50, null=True, blank=True)
goes_by = models.CharField(max_length=50, null=True, blank=True)
Cast
返回一个指定的 输出类型
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Author.objects.create(age=25, name='Margaret Smith')
>>> author = Author.objects.annotate(
... age_as_float=Cast('age', output_field=FloatField()),
... ).get()
>>> print(author.age_as_float)
25.0
Coalesce
接收至少两个字段名 返回第一个 不为空的值
>>> # Get a screen name from least to most public
>>> from django.db.models import Sum, Value as V
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
# 'alias', 'goes_by', 'name' 返回第一个 不为空的值
>>> author = Author.objects.annotate(
... screen_name=Coalesce('alias', 'goes_by', 'name')).get()
>>> print(author.screen_name)
Maggie
>>> # Prevent an aggregate Sum() from returning None Sum()函数没值返回None
# Sum('age'), V(0) 返回第一个 不为空的值
>>> aggregated = Author.objects.aggregate(
... combined_age=Coalesce(Sum('age'), V(0)),
... combined_age_default=Sum('age'))
>>> print(aggregated['combined_age'])
0
>>> print(aggregated['combined_age_default'])
None
Greatest
接受至少两个字段名称或表达式的列表,并返回最大值。每个参数必须具有相似的类型,因此,将文本和数字混合会导致数据库错误
class Blog(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
class Comment(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body='Greatest is the best.')
>>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
>>> annotated_comment = comments.get()
Least
接受至少两个字段名称或表达式的列表,并返回最小值。每个参数必须具有相似的类型,因此,将文本和数字混合会导致数据库错误。
用法同上Greatest
Extract
将数据库时间转换成需要的格式
>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
... ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
... ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay,
... ExtractYear,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
... start_datetime=start_2015, start_date=start_2015.date(),
... end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
... year=ExtractYear('start_datetime'),
... isoyear=ExtractIsoYear('start_datetime'),
... quarter=ExtractQuarter('start_datetime'),
... month=ExtractMonth('start_datetime'),
... week=ExtractWeek('start_datetime'),
... day=ExtractDay('start_datetime'),
... weekday=ExtractWeekDay('start_datetime'),
... hour=ExtractHour('start_datetime'),
... minute=ExtractMinute('start_datetime'),
... second=ExtractSecond('start_datetime'),
... ).values(
... 'year', 'isoyear', 'month', 'week', 'day',
... 'weekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
'day': 15, 'weekday': 2, 'hour': 23, 'minute': 30, 'second': 1}
如果USE_TZ是True那么日期时间存储在UTC数据库。如果Django中的其他时区处于活动状态,则在提取值之前将datetime转换为该时区。下面的示例将转换为墨尔本时区(UTC +10:00),该时区将更改返回的星期几,星期几和小时数:
>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
... day=ExtractDay('start_datetime', tzinfo=melb),
... weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
... hour=ExtractHour('start_datetime', tzinfo=melb),
... ).values('day', 'weekday', 'hour').get(
... end_datetime__year=ExtractYear('start_datetime'),
... )
{'day': 16, 'weekday': 3, 'hour': 9}
Now
返回查询执行时数据库服务器的当前日期和时间,通常使用SQL CURRENT_TIMESTAMP
>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
<QuerySet [<Article: How to Django>]>
Trunc
截断日期直到最重要的部分
>>> from datetime import datetime
>>> from django.db.models import Count, DateTimeField
>>> from django.db.models.functions import Trunc
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
>>> experiments_per_day = Experiment.objects.annotate(
... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).values('start_day').annotate(experiments=Count('id'))
>>> for exp in experiments_per_day:
... print(exp['start_day'], exp['experiments'])
...
2015-06-15 00:00:00 2
2015-12-25 00:00:00 1
>>> experiments = Experiment.objects.annotate(
... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).filter(start_day=datetime(2015, 6, 15))
>>> for exp in experiments:
... print(exp.start_datetime)
...
2015-06-15 14:30:50.000321
2015-06-15 14:40:02.000123
Abs
>>> from django.db.models.functions import Abs
>>> Vector.objects.create(x=-0.5, y=1.1)
>>> vector = Vector.objects.annotate(x_abs=Abs('x'), y_abs=Abs('y')).get()
>>> vector.x_abs, vector.y_abs
(0.5, 1.1)
也可以将其注册为转换。例如:
>>> from django.db.models.functions import Abs
>>> FloatField.register_lookup(Abs)
>>> # Get vectors inside the unit cube
>>> vectors = Vector.objects.filter(x__abs__lt=1, y__abs__lt=1)
Ceil
返回大于或等于数字字段或表达式的最小整数
>>> from django.db.models.functions import Ceil
>>> Vector.objects.create(x=3.12, y=7.0)
>>> vector = Vector.objects.annotate(x_ceil=Ceil('x'), y_ceil=Ceil('y')).get()
>>> vector.x_ceil, vector.y_ceil
(4.0, 7.0)
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Ceil
>>> FloatField.register_lookup(Ceil)
>>> # Get vectors whose ceil is less than 10
>>> vectors = Vector.objects.filter(x__ceil__lt=10, y__ceil__lt=10)
Floor
返回不大于数字字段或表达式的最大整数值。
用法示例:
>>> from django.db.models.functions import Floor
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_floor=Floor('x'), y_floor=Floor('y')).get()
>>> vector.x_floor, vector.y_floor
(5.0, -3.0)
也可以将其注册为转换。例如:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Floor
>>> FloatField.register_lookup(Floor)
>>> # Get vectors whose floor() is greater than 10
>>> vectors = Vector.objects.filter(x__floor__gt=10, y__floor__gt=10)
Round
将数字字段或表达式四舍五入到最接近的整数。半值是向上取整还是向下取整取决于数据库。
用法示例:
>>> from django.db.models.functions import Round
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_r=Round('x'), y_r=Round('y')).get()
>>> vector.x_r, vector.y_r
(5.0, -2.0)
也可以将其注册为转换。例如:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Round
>>> FloatField.register_lookup(Round)
>>> # Get vectors whose round() is less than 20
>>> vectors = Vector.objects.filter(x__round__lt=20, y__round__lt=20)
Concat
接受至少两个文本字段或表达式的列表,并返回连接的文本。每个参数必须为文本或char类型。如果要将a TextField()与a 串联CharField(),则一定要告诉Django,output_fielda应该是a TextField()。如下面的示例所示,在output_field连接a时也需要指定一个 Value。
此函数永远不会有空结果。在使用null参数导致整个表达式为null的后端,Django将确保首先将每个null部分转换为空字符串。
用法示例:
>>> # Get the display name as "name (goes_by)"
>>> from django.db.models import CharField, Value as V
>>> from django.db.models.functions import Concat
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
... screen_name=Concat(
... 'name', V(' ('), 'goes_by', V(')'),
... output_field=CharField()
... )
... ).get()
>>> print(author.screen_name)
Margaret Smith (Maggie)
DJANGO 分组、聚合查询,字段
一、分组查询概念
‘’’
Book: id name price publish_date publish(publish_id)
- 聚合函数可以单独使用: 将整张表作为一个大的分组,查询字段只能是聚合结果
select max(price), group_concat(name) from book where id < 10;- 聚合函数在分组下使用,查询字段是聚合结果和分组字段
select publish_id, max(price) from book group by publish_id having max(price) > 50
‘’’
二、聚合查询
‘’’
聚合函数的使用场景
-- 单独使用:不分组,查询字段只能是聚合结果
-- 分组使用:按字段分组,查询字段是分组字段和聚合结果
导入聚合函数
from django.db.models import Avg, Max, Min, Count, Sum
‘’’
三、单独聚合查询:aggregate
‘’’
语法:
aggregate(别名=聚合函数(‘字段’))
规则:
1.可以同时对多个字段进行聚合处理:aggregate(别名1=聚合函数1('字段1'), ..., 别名n=聚合函数n('字段n'))
3.是QuerySet对象方法(在QuerySet类型的数据后直接用.调用)
2.方法返回值为dict类型
案例:所有书中最贵和最便宜的书的价格
dic = Book.objects.all().aggregate(high_price=Max('price'),low_price=Min('price))
print(dic)
如果在aggregate之前调用values操作,这个操作会被直接忽略
四、组聚合查询:annotate
语法:
values('分组字段').annotate(别名=聚合函数('字段')).filter(聚合字段别名条件判断).values('要取的分组字段', '要取的聚合字段别名')
规则:
1.values(...).annotate(...)为分组组合,values控制分组字段,annotate控制聚合字段,得到QuerySet类型
2.values可按多个字段分组values('分组字段1', ..., '分组字段n')
3.可以同时对多个字段进行聚合处理annotate(别名1=聚合函数1('字段1'), ..., 别名n=聚合函数n('字段n'))
4.分组后的的filter(...)代表having判断,只对聚合字段进行条件判断,可以省略(如果对非聚合字段或分组字段进行条件判断则代表where判断)
5.取字段值values(...)
①如果省略默认取所有分组字段与聚合字段
②没有省略,可以自主取个别分组字段及聚合字段(取字段的values中出现了非分组或非聚合字段,该字段自动成为分组字段)
#案例:每个出版社出版的最贵的书的价格高于50元的出版社名与最高价格
res=Book.objects.all().values('publish__name').annotate(high_price=Max('price')).filter(high_price__gt=50).values('publish__name', 'high_price')
print(res)
res=Publish.objects.values('name').annotate(high_price=Max('book__price')).filter(high_price__gt=50).values('name','high_price')
print(res)
'''
五、常用共有字段属性
‘’’
1. null:默认为False(字段不能为空,插入数据时该字段必须赋值),True表示字段可为null,空值将会被存储为NULL(插入数据时该字段可以为空)
2. blank:默认为False(字段不允许为空),True表示字段可为空
#null与blank区别:null纯粹是与数据库相关的,而blank则与验证相关,如果一个字段设为blank=True,表单验证时允许输入一个空值,而blank=False.则该项必须输入数据
3. choices:可选的,限制了该选项的字段值必须是所指定的choice中的一个:
choices为元组中套元组
①子元组的第一个值类型要与字段类型对应.
②第二个值可以代表第一个值的特殊含义
③通过get_tagName_display()获取特殊含义
#models中字段设置
-- choices=((0, '女'), (1, "男"))
-- sex = models.SmallIntegerField(choices=choices)
#取值
-- obj.sex #取到的值是0或1
-- obj.get_sex_display() #取到的值是女或男
4. db_column:自定义字段名,没必要这样做
5. db_index:如果为True的话,设置索引
6. default:字段默认值
7. editable:默认为True,若为False,则不会在/admin/界面显示
8. primary_key:若设置为True,则表示将该字段设置为主键。一般情况下django默认会设置一个自增长的id主键。
9. unique:若设置为True,该字段值不可重复,唯一键
‘’’
六、常用字段
‘’’
1. AutoField():默认自增主键(primary_key=True),django会默认建立id字段主键
2. BooleanField():布尔字段,对应数据库tinyint类型
3. CharField():字符类型
– 字段属性max_length=64,数据长度,必须明确
4. DateField():年月日时间类型
– 字段属性auto_now=True,数据被更新时就会更新时间
– 字段属性auto_now_add=True,数据第一次产生时产生
5. DateTimeField():年月日小时分钟秒时间类型
– 字段属性auto_now=True,数据被更新时就会更新时间
– 字段属性auto_now_add=True,数据第一次产生时产生
6. DecimalField():混合精度的小数类型
– 字段属性max_digits=3,限定数字的最大位数(包含小数位)
– 字段属性decimal_places=2,限制小数的最大位数
7. IntegerField():整型
‘’’
七、不常用字段
‘’’
1. BigAutoField():大整型自增
2. BigIntegerField():长整型
3. EmailField():邮箱字段,拥有/admin/验证
4. FloatField():浮点型小数
5. SmallIntegerField():小整型
6. TextField():大文本类型
7. FileField():文件字段
‘’’
八、关系字段
‘’’
1. ForeignKey():外键字段
– 字段属性to关联模型类
– 字段属性to_field关联字段,省略默认关联主键
– 字段属性on_delete (外键关联数据被删除时的操作)
– models.CASCADE 级联删除
– modles.PROTECT 抛出异常
– models.SET_NULL 设置空值
– models.SET_DEFAULT 设置默认值
– models.SET(value)自定义值
– 字段属性related_name自定义反向查询的字段名
– 字段属性db_constraint=False取消关联关系,但还可以使用连表查询
总结:models.ForeignKey(to=‘关联的类名’, null=True, on_delete=models.SET_NULL, db_constraint=False, related_name=“本类名小写”)
#null=True, on_delete=models.SET_NULL 当一个表中的数据被删除时,与之关联的表的数据的外健字段值会被删除,并设置默认值为null
#related_name 没有设置别名 Test1.objects.first().test2_set.first().name
设置别名 Test1.objects.first().别名.first().name
2、OneToOneField():一对一外键字段
– 字段同外键
3、ManyToManyField():多对多关系字段
– 字段属性to关联模型类
– 字段属性through关联关系类
– 字段属性through_fields关联关系表中(本身类名小写字段, 关联表类名小写字段)
‘’’
九、断开外键关联的ForeignKey使用
‘’’
#1、不使用ForeignKey方式断开关联,不再支持Django ORM连表查询语法
class Publish(models.Model):
name = models.CharField(max_length=20)
class Book(models.Model):
name = models.CharField(max_length=20)
# 字段需要写_id来表示相关表的字段信息
publish_id = models.IntegerField()
#查询的方法
b1 = Book.objects.first()
print(b1)
p1 = Publish.objects.filter(pk=b1.publish_id).first()
print(p1.name)
# *****
#2、使用ForeignKey方式用db_constraint=False字段属性断开关联,依然支持Django ORM连表查询语法,建议使用
class Publish(models.Model):
name = models.CharField(max_length=20)
class Book(models.Model):
name = models.CharField(max_length=20)
# 字段不需要写_id来表示相关表的字段信息,ORM会自动添加
publish = models.ForeignKey(to='Publish', null=True, on_delete=models.SET_NULL, db_constraint=False)
十、断开关联的多对多自动创建关系表
使用ManyToManyField方式用db_constraint=False字段属性断开关联,依然支持Django ORM连表查询语法,建议使用,使用后依旧拥有add|clear|remove|set操作
class MyBook(models.Model):
name = models.CharField(max_length=20)
my_author = models.ManyToManyField(to='MyAuthor', db_constraint=False)
class MyAuthor(models.Model):
name = models.CharField(max_length=20)
十一、断开关联的多对多手动创建关系表
#手动创建关系表可以让关系表拥有更多的自身的字段,同时通过关系表类名可以直接获取第三张表
#1、和自动建立关系表类似,依然支持Django ORM连表查询语法(多对多借助关系表连表查询)
class Book(models.Model):
name = models.CharField(max_length=20)
class Author(models.Model):
name = models.CharField(max_length=20)
class Book_Author(models.Model):
book = models.ForeignKey(to="Book", null=True, on_delete=models.SET_NULL, db_constraint=False)
author = models.ForeignKey(to='Author', null=True, on_delete=models.SET_NULL, db_constraint=False)
time = models.DateField()
#借助关系表连表查询
Book.objects.filter(pk=1).values('book_author__author__name')
2、手动创建关系表,在关系表中用ForeignKey方式支持基于外键关系表的ORM连表查询,同时明确ManyToManyField字段,所以也支持ORM正向方向连表查询
– db_constraint=False断开关联可以在ForeignKey或ManyToManyField任意一方完成
class Book(models.Model):
name = models.CharField(max_length=20)
# 明确through与through_fields,ManyToManyField才不会自动建立关系表
author = models.ManyToManyField(to='Author', through='Book_Author', through_fields=('book_id', 'author_id'))
class Author(models.Model):
name = models.CharField(max_length=20)
class Book_Author(models.Model):
book = models.ForeignKey(to="Book", null=True, on_delete=models.SET_NULL, db_constraint=False)
author = models.ForeignKey(to='Author', null=True, on_delete=models.SET_NULL, db_constraint=False)
time = models.DateField()
#查询
①可以用外健关系表的ORM连表查询
Book.objects.filter(pk=1).values(‘book_author__author__name’)
②也可以用ORM正向反向连表查询
Book.objects.filter(pk=1).values(‘author__name’)
‘’’
#总结:手动创建第三张表,第三张表的增删改就采用关系表类名衍生的create|delete|update,就不再拥有add|clear|remove|set(因为关系表拥有自己的字段,这些方法无法直接操作这些字段)