• django-modles操作(ORM)


    配置数据库

    1.在Mysql中创建数据库

    2.在settings中配置DATABASE

        DATABASES = {
        	'default': {
        		'ENGINE': 'django.db.backends.mysql',
        		'NAME':'s4day70db',
        		'USER': 'root',
        		'PASSWORD': '',
        		'HOST': 'localhost',
        		'PORT': 3306,
        		}
        }
    

    3.在与project同名的init下修改django中默认连接数据库的方式

        import pymysql
        pymysql.install_as_MySQLdb()
    

    4.在settings.py中注册app01

    INSTALLED_APPS = [
        'django.contrib.admin',
        'django.contrib.auth',
        'django.contrib.contenttypes',
        'django.contrib.sessions',
        'django.contrib.messages',
        'django.contrib.staticfiles',
        'app01',
    ]
    

    5.运行命令创建数据表

    python manage.py makemigrations
    python manage.py migrate
    

    orm基本操作

    参考博客

    # 增
            #
            # models.Tb1.objects.create(c1='xx', c2='oo')  增加一条数据,可以接受字典类型数据 **kwargs
    
            # obj = models.Tb1(c1='xx', c2='oo')
            # obj.save()
    
            # 查
            #
            # models.Tb1.objects.get(id=123)         # 获取单条数据,不存在则报错(不建议)
            # models.Tb1.objects.all()               # 获取全部
            # models.Tb1.objects.filter(name='seven') # 获取指定条件的数据
            # models.Tb1.objects.exclude(name='seven') # 获取指定条件的数据
    
            # 删
            #
            # models.Tb1.objects.filter(name='seven').delete() # 删除指定条件的数据
    
            # 改
            # models.Tb1.objects.filter(name='seven').update(gender='0')  # 将指定条件的数据更新,均支持 **kwargs
            # obj = models.Tb1.objects.get(id=1)
            # obj.c1 = '111'
            # obj.save()                                                 # 修改单条数据
    
    基本操作
    

    进阶操作

    # order by
    models.Tb1.objects.filter(name='seven').order_by('id')    # asc
    models.Tb1.objects.filter(name='seven').order_by('-id')   # desc
    
    # 批量插入
    
    objs = [
        models.UserInfo(name="批量插入1", age=23, ut_id=1),
        models.UserInfo(name="批量插入2", age=23, ut_id=1),
        models.UserInfo(name="批量插入3", age=23, ut_id=1),
        models.UserInfo(name="批量插入4", age=23, ut_id=1),
        models.UserInfo(name="批量插入5", age=23, ut_id=1),
    ]
    models.UserInfo.objects.bulk_create(objs, 10)
    
    # aggregate()聚合函数
    from django.db.models import Max, Avg, Count, Min, Sum
    result = models.UserInfo.objects.aggregate(k=Count("ut_id", distinct=True), n=Count("id", distinct=True))
    print(result) # {'k': 3, 'n': 21}
    

    annotate()分组

    # 用于实现聚合group by查询
    
    from django.db.models import Count, Avg, Max, Min, Sum
    def test(request):
        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) > 1
        return HttpResponse("....")
    

    正则匹配

    # regex正则匹配,iregex 不区分大小写
    Entry.objects.get(title__regex=r'^(An?|The) +')
    Entry.objects.get(title__iregex=r'^(an?|the) +')
    

    F和Q

    from django.db.models import F ,Q
    def test(request):
        # F,更新时取原来值
        # 原有age字段都加5
        models.UserInfo.objects.all().update(age=F("age")+5)
    
        # Q:用于构造复杂查询条件
        q1 = Q()
        q1.connector = "OR"
        q1.children.append(("id__gt", 2)) # id > 2
        
        q2 = Q()
        q2.connector= "OR"
        q2.children.append(("age", 23)) # age=23
    
        conn = Q()
        conn.add(q1, "AND")
        conn.add(q2, "AND") # conn此时相当于 (id > 2) and ( age = 23 or age = 24)
    
        obj = models.UserInfo.objects.filter(conn)
        print(obj)
        return HttpResponse("...")
    

    extra()

    models.UserInfo.objects.extra(
    	select={'newid':'select count(1) from app01_usertype where id>%s'},
    	select_params=[1,],
    	where = ['age>%s'],
    	params=[18,],
    	order_by=['-age'],
    	tables=['app01_usertype']
    )
    
    """
    select 
    	app01_userinfo.id,
    	(select count(1) from app01_usertype where id>1) as newid
    from app01_userinfo,app01_usertype
    where 
    	app01_userinfo.age > 18
    order by 
    	app01_userinfo.age desc
    """
    

    连表性能相关

    # select_related: 查询主动做连表
    q = models.UserInfo.objects.all().select_related('ut','gp')
    # select * from userinfo
    # select * from userinfo inner join usertype on ...
    for row in q:
        print(row.name,row.ut.title)
    
    # prefetch_related: 不做连表,做多次查询
    q = models.UserInfo.objects.all().prefetch_related('ut')
    # select * from userinfo;
    # Django内部:ut_id = [2,4]
    # select * from usertype where id in [2,4]
    for row in q:
        print(row.id,row.ut.title)
    

    models执行原生sql

    def test(request):
        # models执行原生SQL
        from django.db import connections,connection
        cursor = connection.cursor() # = connections["default"].cursor()
        cursor.execute("select * from app01_userinfo where id = %s",[1, ])
        result = cursor.fetchall()
        print(result)
        return HttpResponse(".....")
        
    

    时间操作date()和datetimes()

    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’)
        """
    

    多对多操作

    # manytomanyfied创建第三张表
    m = models.ManyToManyField("classes")
    obj.m.add(*[1,2])
    obj.m.remove(*[1,2])
    obj.m.set([1,2,3]) # 会覆盖第三张表原有数据
    obj.m.clear()
    obj.m.filter(name="alex")
    obj.m.all() # 查询所有
    
    # 自定义第三张表
    # 1. 和方少伟有关系的姑娘
    		# obj = models.Boy.objects.filter(name='方少伟').first()
    		# love_list = obj.love_set.all()
    		# for row in love_list:
    		#     print(row.g.nick)
    		#
    		#
    		# love_list = models.Love.objects.filter(b__name='方少伟')
    		# for row in love_list:
    		#     print(row.g.nick)
    		#
    		# love_list = models.Love.objects.filter(b__name='方少伟').values('g__nick')
    		# for item in love_list:
    		#     print(item['g__nick'])
    		#
    		# love_list = models.Love.objects.filter(b__name='方少伟').select_related('g')
    		# for obj in love_list:
    		#     print(obj.g.nick)
    

    自关联

    ManyToManyField自关联

    // 建表
    class UserInfo(models.Model):
        nickname = models.CharField(max_length=32)
        username = models.CharField(max_length=32)
        password = models.CharField(max_length=32)
        gender_list = (
            (1,"男"),
            (2,"女"),
        )
        gender = models.IntegerField(choices=gender_list)
        # 多对多
        m = models.ManyToManyField("UserInfo")
        
    // django会自动帮我们创建第二张表,两个字段分别是,from_userinfo_id和to_userinfo_id,通过userinfo.m正向操作的时候,选中的是from_userinfo_id,反向操作时选中的是to_userinfo_id
    
    def test2(request):
        # 正向操作
        obj = models.UserInfo.objects.filter(nickname="少伟").first()
        obj1 = obj.m.all()
        # <QuerySet [<UserInfo:  高圆圆>, <UserInfo: 赵丽颖>, <UserInfo: 刘涛>]>
        
        # 反向操作
        obj = models.UserInfo.objects.filter(nickname=" 高圆圆").first()
        obj1 = obj.userinfo_set.clear()
        print(obj1)
        # <QuerySet [<UserInfo: 少伟>, <UserInfo: 陈军>]>
    
        return HttpResponse("...from test 2")
    

    ForeignKey自关联

    FK自关联:
    class Comment(models.Model):
    	"""
    	评论表
    	"""
    	news_id = models.IntegerField()            # 新闻ID
    	content = models.CharField(max_length=32)  # 评论内容
    	user = models.CharField(max_length=32)     # 评论者
    	reply = models.ForeignKey('Comment',null=True,blank=True,related_name='xxxx')
    	"""
    
    	id  新闻ID   content     user       reply_id
    	1   1        别比比     root         null
    	2   1        就比比     root         null
    	3   1        瞎比比     shaowei      null
    	4   2        写的正好   root         null
    	5   1        拉倒吧     由清滨         2
    	6   1        拉倒吧1    xxxxx         2
    	7   1        拉倒吧2    xxxxx         5
    	
    """
    新闻1
    	别比比
    	就比比
    		- 拉倒吧
    			- 拉倒吧2
    		- 拉倒吧1
    	瞎比比
    新闻2:
    	写的正好
    """
    	
    

    多表自关联

    class UserInfo(models.Model):
        nickname = models.CharField(max_length=32)
        username = models.CharField(max_length=32)
        password = models.CharField(max_length=32)
        gender_list = (
            (1,"男"),
            (2,"女"),
        )
        gender = models.IntegerField(choices=gender_list)
    
    class U2U(models.Model):
        b = models.ForeignKey("UserInfo", related_name="girls")
        g = models.ForeignKey("UserInfo", related_name="boys")
        # g = models.ForeignKey("UserInfo", related_query_name="boys")
        # 若使用related_query_name,反向查找时需要加上_set(如obj.boys_set)
    
    def test(request):
        obj = models.UserInfo.objects.filter(id=1).first()
        obj1 = obj.girls.all()  # 此时是U2U2对象
        obj2 = obj.boys
        for row in obj1:
            print(row.g) # 高圆圆 赵丽 颖刘涛
    
  • 相关阅读:
    事件委托
    a标签深入研究
    windows查看端口号
    什么是WCM
    map key循环
    用Myeclipse打war包
    myeclipse 8.510.0 安装 svn 方法
    SVN使用&CVS使用
    MyEclipse 字体大小 字体颜色
    什么是Quickr
  • 原文地址:https://www.cnblogs.com/zouruncheng/p/7106958.html
Copyright © 2020-2023  润新知