2019.03.21 表单查询

今日份Python 表单查询

有一个叫ORM模式的，会显示sql底层代码

当我们用Python去查找是，再用showsql就可以看到刚才查询到的底层sql语句

在pycharm有个PythonConsole

显示ORM底层生成SQL

def showsql():
        from django.db import connection
        queries = connection.queries
        print queries[-1]['sql']

查询单个对象

#所有的继承自models.Model的类都会有一个叫objects（管理器）

* 查询一个（有且只能有一个）（少于一个或者多余一个都报错）
* 返回值Movie对象

Movie.objects.get(mid=147)

SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE `movie`.`mid` = 147

* 获得第一个

Movie.objects.first()

SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` ORDER BY `movie`.`mid` ASC LIMIT 1
* 获得最后一个

Movie.objects.last()

SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` ORDER BY `movie`.`mid` DESC LIMIT 1
* 获得记录的总数

Movie.objects.count()

SELECT COUNT(*) AS `__count` FROM `movie`

查询多个对象

* 获得所有的记录

            Movie.objects.all()
            # 在diango的1.11.6中默认只是获得21个，
            # 懒加载
            SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM            `movie` LIMIT 21
* 切片 （不支持负数索引）
        Movie.objects.all()[20:40]
        # 底层直接使用了limit字句，可以自动的分页
        # django的orm性能非常强大，能节省很多工作      
        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        LIMIT 20 OFFSET 20
* 过滤
        #  集和（游标，结果集，查询集）
        Movie.objects.filter(mname='麻辣学院')

        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE `movie`.`mname` = '麻辣学院' LIMIT 21
    
* 模糊查询 
    * like
    * %(多个字符) _(一个字符)
    * SELECT * from movie WHERE mname LIKE '%爱情_'
* 查询爱情结尾的

            SELECT * from movie WHERE mname LIKE '%爱情'

            Movie.objects.filter(mname__endswith='爱情')

            SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM            `movie` WHERE `movie`.`mname` LIKE BINARY '%爱情' LIMIT 21
* 查询爱情开头的

            SELECT * from movie WHERE mname LIKE '爱情%'
            
            Movie.objects.filter(mname__startswith='爱情')
            
            SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM            `movie` WHERE `movie`.`mname` LIKE BINARY '爱情%' LIMIT 21
* 包含爱情的

        SELECT * from movie where mname like '%爱情%'

        Movie.objects.filter(mname__contains='爱情')


        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        WHERE `movie`.`mname` LIKE BINARY '%爱情%' LIMIT 21
* 完全相等

        Movie.objects.filter(mname__exact='麻辣学院')
        Movie.objects.filter(mname='麻辣学院')
    
        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        WHERE `movie`.`mname` = '麻辣学院' LIMIT 21
* 忽略大小写

        Movie.objects.filter(mname__istartswith='h')

* 查询某个字段是否为null

        Movie.objects.filter(mname__isnull=True)


* 多条件查询

        Movie.objects.filter(mname__contains='爱情',mid=147)
        
            and

            
        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE (`movie`.`mname` LIKE BINARY '%爱情%' AND `movie`.`mid` = 147) LIMIT 21

        Movie.objects.filter(mname__contains='爱情').filter(mid=147)

* 部分查询

        Movie.objects.values('mname','mid').filter(mname__contains='爱情')
        
* 排除一部分

    Movie.objects.filter(mname__contains='爱情').exclude(mname__startswith='爱情')

        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        WHERE (`movie`.`mname` LIKE BINARY '%爱情%' AND NOT (`movie`.`mname` LIKE BINARY '爱情%')) LIMIT 21

* 排序

        Movie.objects.order_by('mid')
    
        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        ORDER BY `movie`.`mid` ASC LIMIT 21


        Movie.objects.order_by('-mid')

        SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie`        ORDER BY `movie`.`mid` DESC LIMIT 21

日期查询



 * 查询大于某个时间的记录

        Post.objects.filter(created__gt='2017-10-20')
 
        SELECT `post_post`.`id`, `post_post`.`title`, `post_post`.`created` FROM `post_post` WHERE          `post_post`.`created` > '2017-10-20' LIMIT 21
 * 查询最近一个月的帖子（查询最近不活跃的用户）
 
        def get_recent_month_posts():
            import datetime
            current = datetime.date.today()-datetime.timedelta(days=30)
            current = str(current)
            return Post.objects.filter(created__gt=current)
            
 * 查询十月二十号--十一月二十号的所有的记录
   * 错误的

            Post.objects.filter(created__in=('2017-10-20','2017-11-20'))
            指的是这两个时间中的一个
  
  * 正确 （sql语句）

        Post.objects.filter(created__range=('2017-10-20','2017-11-20'))

                
        SELECT `post_post`.`id`, `post_post`.`title`, `post_post`.`created` FROM `post_post` WHERE          `post_post`.`created` BETWEEN '2017-10-20' AND '2017-11-20' LIMIT 21