ORM基本增删改查操作:

1 # 增 2 models.Tb1.objects.create(c1='xx', c2='oo')  增加一条数据，可以接受字典类型数据 **kwargs 3 obj = models.Tb1(c1='xx', c2='oo') 4 obj.save() 5  6 # 查 7 models.Tb1.objects.get(id=123)         # 获取单条数据，不存在则报错（不建议） 8 models.Tb1.objects.all()               # 获取全部 9 models.Tb1.objects.filter(name='seven') # 获取指定条件的数据10 models.Tb1.objects.exclude(name='seven') # 获取指定条件的数据11 12 # 删13 models.Tb1.objects.filter(name='seven').delete() # 删除指定条件的数据14 15 # 改16 models.Tb1.objects.filter(name='seven').update(gender='0')  # 将指定条件的数据更新，均支持 **kwargs17 obj = models.Tb1.objects.get(id=1)18 obj.c1 = '111'19 obj.save()# 修改单条数据

ORM进阶操作:

# 获取个数models.Tb1.objects.filter(name='seven').count()# 大于，小于models.Tb1.objects.filter(id__gt=1)              # 获取id大于1的值models.Tb1.objects.filter(id__gte=1)              # 获取id大于等于1的值models.Tb1.objects.filter(id__lt=10)             # 获取id小于10的值models.Tb1.objects.filter(id__lte=10)             # 获取id小于10的值models.Tb1.objects.filter(id__lt=10, id__gt=1)   # 获取id大于1 且 小于10的值# inmodels.Tb1.objects.filter(id__in=[11, 22, 33])   # 获取id等于11、22、33的数据models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in# isnullmodels.Entry.objects.filter(pub_date__isnull=True)# contains()models.Tb1.objects.filter(name__contains="ven")models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感models.Tb1.objects.exclude(name__icontains="ven")# rangemodels.Tb1.objects.filter(id__range=[1, 2])   # 范围bettwen and# 其他类似#startswith，istartswith, endswith, iendswith,# order bymodels.Tb1.objects.filter(name='seven').order_by('id')    # ascmodels.Tb1.objects.filter(name='seven').order_by('-id')   # desc# group byfrom django.db.models import Count, Min, Max, Summodels.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))#SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"# limit 、offsetmodels.Tb1.objects.all()[10:20]# regex正则匹配，iregex 不区分大小写models.Entry.objects.get(title__regex=r'^(An?|The) +')models.Entry.objects.get(title__iregex=r'^(an?|the) +')# dateimport datetimemodels.Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))models.Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))# yearmodels.Entry.objects.filter(pub_date__year=2005)models.Entry.objects.filter(pub_date__year__gte=2005)# monthmodels.Entry.objects.filter(pub_date__month=12)models.Entry.objects.filter(pub_date__month__gte=6)# daymodels.Entry.objects.filter(pub_date__day=3)models.Entry.objects.filter(pub_date__day__gte=3)# week_daymodels.Entry.objects.filter(pub_date__week_day=2)models.Entry.objects.filter(pub_date__week_day__gte=2)# hourmodels.Event.objects.filter(timestamp__hour=23)models.Event.objects.filter(time__hour=5)models.Event.objects.filter(timestamp__hour__gte=12)# minutemodels.Event.objects.filter(timestamp__minute=29)models.Event.objects.filter(time__minute=46)models.Event.objects.filter(timestamp__minute__gte=29)# secondmodels.Event.objects.filter(timestamp__second=31)models.Event.objects.filter(time__second=2)models.Event.objects.filter(timestamp__second__gte=31)

ORM高级操作(Q,F,extra,原生SQL)

#extra#extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)models.Entry.objects.extra(select={
    'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))#select *,(select col from sometable where otherco >1) as new_id from Entrymodels.Entry.objects.extra(where=['headline=%s'], params=['Lennon'])#select * from Entry where headline='Lennon'models.Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])#select * from Entry where foo='a' or bar = 'a' and baz='a'models.Entry.objects.extra(select={
    'new_id': "select id from tb where id > %s"},                           select_params=(1,), order_by=['-nid'])#select *,(select id from tb where id > 1) from Entry order nid desc#Ffrom django.db.models import Fmodels.Tb1.objects.update(num=F('num')+1)#先引用(F('num')+1  用来将原始值取出并+1),再赋值(num=F('num')+1)相当于,num+1之后更新#Qfrom django.db.models import Q#方式一：Q(nid__gt=10)#where nid > 10Q(nid=8) | Q(nid__gt=10)#where nid =8 or nid >10Q(Q(nid=8) | Q(nid__gt=10)) & Q(caption='root')# where nid = 8 or nid>10 and caption='root'#方式二：con = Q()q1 = Q()q1.connector = 'OR'q1.children.append(('id', 1))q1.children.append(('id', 10))q1.children.append(('id', 9))# q1 : where id =1 or id=10 or id=9 q2 = Q()q2.connector = 'OR'q2.children.append(('c1', 1))q2.children.append(('c1', 10))q2.children.append(('c1', 9))#q2 :where c1=1 or c1=10 or c1=9con.add(q1, 'AND')con.add(q2, 'AND')#con: where (id =1 or id=10 or id=9 ) and (c1=1 or c1=10 or c1=9)models.Tb1.objects.filter(con)#select * from Tb1 where (id =1 or id=10 or id=9 ) and (c1=1 or c1=10 or c1=9)# 执行原生SQLfrom django.db import connection, connectionscursor = connection.cursor()  #拿到默认数据库default连接的游标cursors = connections['db1'].cursor()#拿到指定数据库db1的游标cursor.execute("""SELECT * from auth_user where id = %s""", [1])row = cursor.fetchone()

ORM其他操作(ORM对象方法拆解)

################################################################### PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET ###################################################################def all(self)    # 获取所有的数据对象def filter(self, *args, **kwargs)    # 条件查询    # 条件可以是：参数，字典，Qdef exclude(self, *args, **kwargs)    # 条件查询    # 条件可以是：参数，字典，Qdef select_related(self, *fields)     性能相关：表之间进行join连表操作，一次性获取关联的数据。     model.tb.objects.all().select_related()     model.tb.objects.all().select_related('外键字段')     model.tb.objects.all().select_related('外键字段__外键字段')def prefetch_related(self, *lookups)    性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作。            # 获取所有用户表            # 获取用户类型表where id in (用户表中的查到的所有用户ID)            models.UserInfo.objects.prefetch_related('外键字段')            from django.db.models import Count, Case, When, IntegerField            Article.objects.annotate(                numviews=Count(Case(                    When(readership__what_time__lt=treshold, then=1),                    output_field=CharField(),                ))            )            students = Student.objects.all().annotate(num_excused_absences=models.Sum(                models.Case(                    models.When(absence__type='Excused', then=1),                default=0,                output_field=models.IntegerField()            )))def annotate(self, *args, **kwargs)    # 用于实现聚合group by查询    from django.db.models import Count, Avg, Max, Min, Sum    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))    # SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)    # SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1    v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)    # SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1def distinct(self, *field_names)    # 用于distinct去重    models.UserInfo.objects.values('nid').distinct()    # select distinct nid from userinfo    注：只有在PostgreSQL中才能使用distinct进行去重def order_by(self, *field_names)    # 用于排序    models.UserInfo.objects.all().order_by('-id','age')def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)    # 构造额外的查询条件或者映射，如：子查询    Entry.objects.extra(select={
    'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))    Entry.objects.extra(where=['headline=%s'], params=['Lennon'])    Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])    Entry.objects.extra(select={
    'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid']) def reverse(self):    # 倒序    models.UserInfo.objects.all().order_by('-nid').reverse()    # 注：如果存在order_by，reverse则是倒序，如果多个排序则一一倒序 def defer(self, *fields):    models.UserInfo.objects.defer('username','id')    或    models.UserInfo.objects.filter(...).defer('username','id')    #映射中排除某列数据 def only(self, *fields):    #仅取某个表中的数据     models.UserInfo.objects.only('username','id')     或     models.UserInfo.objects.filter(...).only('username','id') def using(self, alias):     指定使用的数据库，参数为别名（setting中的设置）################################################### PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS ###################################################def raw(self, raw_query, params=None, translations=None, using=None):    # 执行原生SQL    models.UserInfo.objects.raw('select * from userinfo')    # 如果SQL是其他表时，必须将名字设置为当前UserInfo对象的主键列名    models.UserInfo.objects.raw('select id as nid from 其他表')    # 为原生SQL设置参数    models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,])    # 将获取的到列名转换为指定列名    name_map = {
    'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}    Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)    # 指定数据库    models.UserInfo.objects.raw('select * from userinfo', using="default")    ################### 原生SQL ###################    from django.db import connection, connections    cursor = connection.cursor()  # cursor = connections['default'].cursor()    cursor.execute("""SELECT * from auth_user where id = %s""", [1])    row = cursor.fetchone() # fetchall()/fetchmany(..)def values(self, *fields):    # 获取每行数据为字典格式def values_list(self, *fields, **kwargs):    # 获取每行数据为元祖def dates(self, field_name, kind, order='ASC'):    # 根据时间进行某一部分进行去重查找并截取指定内容    # kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日）    # order只能是："ASC"  "DESC"    # 并获取转换后的时间        - year : 年-01-01        - month: 年-月-01        - day  : 年-月-日    models.DatePlus.objects.dates('ctime','day','DESC')def datetimes(self, field_name, kind, order='ASC', tzinfo=None):    # 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间    # kind只能是 "year", "month", "day", "hour", "minute", "second"    # order只能是："ASC"  "DESC"    # tzinfo时区对象    models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)    models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))    """    pip3 install pytz    import pytz    pytz.all_timezones    pytz.timezone(‘Asia/Shanghai’)    """def none(self):    # 空QuerySet对象##################################### METHODS THAT DO DATABASE QUERIES #####################################def aggregate(self, *args, **kwargs):   # 聚合函数，获取字典类型聚合结果   from django.db.models import Count, Avg, Max, Min, Sum   result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid'))   ===> {
    'k': 3, 'n': 4}def count(self):   # 获取个数def get(self, *args, **kwargs):   # 获取单个对象def create(self, **kwargs):   # 创建对象def bulk_create(self, objs, batch_size=None):    # 批量插入    # batch_size表示一次插入的个数    objs = [        models.DDD(name='r11'),        models.DDD(name='r22')    ]    models.DDD.objects.bulk_create(objs, 10)def get_or_create(self, defaults=None, **kwargs):    # 如果存在，则获取，否则，创建    # defaults 指定创建时，其他字段的值    obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={
    'email': '1111111','u_id': 2, 't_id': 2})def update_or_create(self, defaults=None, **kwargs):    # 如果存在，则更新，否则，创建    # defaults 指定创建时或更新时的其他字段    obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={
    'email': '1111111','u_id': 2, 't_id': 1})def first(self):   # 获取第一个def last(self):   # 获取最后一个def in_bulk(self, id_list=None):   # 根据主键ID进行查找   id_list = [11,21,31]   models.DDD.objects.in_bulk(id_list)def delete(self):   # 删除def update(self, **kwargs):    # 更新def exists(self):   # 是否有结果其他操作

ORM连表查询操作:

1         # 每次循环连表查询2         # restea = models.teac_clas.objects.filter(teachers_id__tName="武沛齐")3         # for i in restea:4         #     print(i.teachers_id.tName)5         #使用values方法直接获取字典无需循环连表6         # restea = models.teac_clas.objects.filter(teachers_id__tName="武沛齐").values("teachers_id__tName")7         # for i in restea:8         #     print(i["teachers_id__tName"])

ORM连表示例:

class Boy(models.Model):    bname  = models.CharField(max_length=10)class Girl(models.Model):    gname = models.CharField(max_length=10)class b_g(models.Model):    b = models.ForeignKey("Boy")    g = models.ForeignKey("Girl")    class Mate:        unique_bj=[("b","g")] #唯一索引(男生和女生只能唯一匹配)

# 编辑者：闫龙from django.views import Viewfrom django.shortcuts import HttpResponsefrom appOne import modelsclass TestView(View):    def addData(self):        girllist=[            models.Girl(gname="小爽"),            models.Girl(gname="小红"),            models.Girl(gname="小黄"),            models.Girl(gname="小白"),        ]        boylist=[            models.Boy(bname="龙哥"),            models.Boy(bname="小明"),            models.Boy(bname="小齐"),            models.Boy(bname="小杰"),        ]        models.Girl.objects.bulk_create(girllist)        models.Boy.objects.bulk_create(boylist)    def get(self,request):        #通过关系表,双下划线到boy中获取名称,在通过双下划线到girl中拿到相应的值        #res = models.b_g.objects.filter(b_id__bname="龙哥").values("g_id__gname")        # for i in res:        #     print(i["g_id__gname"])        #通过关系表,双下划綫到boy中获取名称,在通过girl对象索引到相应的值        res = models.b_g.objects.filter(b_id__bname="龙哥")        for i in res:            print(i.g.gname)#i.g获取girl对象,拿到相应的名字        return HttpResponse(".....")

ORM关系表:

1 from django.db import models 2 # Create your models here. 3 class boys(models.Model): 4     bname = models.CharField(max_length=10) 5     #ManyToManyField是Django内部方法自动生成的关系表(boys_girls) 6     #在数据库中会自动建立一张关系表:boys_bAg 7     bAg = models.ManyToManyField("girls") 8     #ManyToManyField(through)就可以使用自己创建的关系表作为ManyToManyField 9     #但是这种生成方式是存在问题的:无法使用add(),set(),remove()方法,但是可以使用all()和clear()方法10     bAg = models.ManyToManyField("girls",through="b_g",through_fields=("b","g"))11 12 class girls(models.Model):13     gname = models.CharField(max_length=10)14 15 #如果使用默认生成表,则无需此类16 class B_G(models.Model):17     b = models.ForeignKey("boys")18     g = models.ForeignKey("girls")

1 from appTwo import models 2 from django.views import View 3 from django.shortcuts import HttpResponse 4 class TestView(View): 5     def addData(self): 6         girllist=[ 7             models.girls(gname="小爽"), 8             models.girls(gname="小红"), 9             models.girls(gname="小黄"),10             models.girls(gname="小白"),11         ]12         boylist=[13             models.boys(bname="龙哥"),14             models.boys(bname="小明"),15             models.boys(bname="小齐"),16             models.boys(bname="小杰"),17         ]18         models.Girl.objects.bulk_create(girllist)19         models.Boy.objects.bulk_create(boylist)20 21     def get(self,request):22         res = models.boys.objects.filter(bname="龙哥").first()23         girllist = res.bAg.all() #拿到res.id对应在boys_bAg中,对应girl的所有对象24         for i in girllist:25             print(i.gname)26         res.bAg.add(4)27         #向boys_bAg中加入res.id , 4数据等同于insert into boys_bAg(boys_id,girls_id) values(1,4)28         res.bAg.remove(2)29         #删除boys_bAg中boys_id=res.id and girls_id = 2的数据30         res.bAg.set(2)31         #清空boys_bAg中所有boys_id=res.id的数据,并插入数据boys_id=res.id,girls_id=232         res.bAg.clear()33         #清空boys_bAg中所有boys_id=res.id的数据34         return HttpResponse("......")