2：django models Making queries

向右看齐 2023-06-04 04:59 5阅读 0赞

这是后面要用到的类

class Blog(models.Model):
        name = models.CharField(max_length=100)
        tagline = models.TextField()
    
        def __unicode__(self):
            return self.name
    
    class Author(models.Model):
        name = models.CharField(max_length=50)
        email = models.EmailField()
    
        def __unicode__(self):
            return self.name
    
    class Entry(models.Model):
        blog = models.ForeignKey(Blog)
        headline = models.CharField(max_length=255)
        body_text = models.TextField()
        pub_date = models.DateTimeField()
        mod_date = models.DateTimeField()
        authors = models.ManyToManyField(Author)
        n_comments = models.IntegerField()
        n_pingbacks = models.IntegerField()
        rating = models.IntegerField()
    
        def __unicode__(self):
            return self.headline

## Creating objects ##

>>> from blog.models import Blog
    >>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
    >>> b.save()

### Saving ForeignKey and ManyToManyField fields ###

>>> from blog.models import Entry
    >>> entry = Entry.objects.get(pk=1)
    >>> cheese_blog = Blog.objects.get(name="Cheddar Talk")
    >>> entry.blog = cheese_blog
    >>> entry.save()
    >>> from blog.models import Author
    >>> joe = Author.objects.create(name="Joe")
    >>> entry.authors.add(joe)
    >>> john = Author.objects.create(name="John")
    >>> paul = Author.objects.create(name="Paul")
    >>> george = Author.objects.create(name="George")
    >>> ringo = Author.objects.create(name="Ringo")
    >>> entry.authors.add(john, paul, george, ringo)

## Retrieving objects ##

>>> Blog.objects
    <django.db.models.manager.Manager object at ...>
    >>> b = Blog(name='Foo', tagline='Bar')
    >>> b.objects
    Traceback:
        ...
    AttributeError: "Manager isn't accessible via Blog instances."

Retrieving all objects

>>> all_entries = Entry.objects.all()

filter(\*\*kwargs)

Returns a new QuerySet containing objects that match the given lookup parameters.

exclude(\*\*kwargs)

Returns a new QuerySet containing objects that do *not* match the given lookup parameters.

Entry.objects.filter(pub_date__year=2006)

#### Chaining filters ####

>>> Entry.objects.filter(
    ...     headline__startswith='What'
    ... ).exclude(
    ...     pub_date__gte=datetime.now()
    ... ).filter(
    ...     pub_date__gte=datetime(2005, 1, 1)
    ... )

get,get 方法会返回符合条件的那个元素，当没有符合筛选条件的元素是get方法会抛出一个 DoesNotExist的异常，所以要慎用

>>> one_entry = Entry.objects.get(pk=1)

### Field lookups ###

筛选属性的时候的一些限制条件，基本格式是 field\_\_lookuptype=value，注意是双下划线，下面是lookuptype

**exact：**Exact match，准确匹配，可以匹配到null（None） **iexact：** 不区分大小写的准确匹配

#### contains：包含                                                        **icontains：不区分大小写的包含** ####

#### in：在（一个筛选的列表）里面 ####

#### gt ：Greater than                                                    gte：Greater than or equal to. ####

#### lt：Less than.                                                          lte：Less than or equal to. ####

#### startswith，istartswith，endswith，iendswith ####

#### range：pub\_date\_\_range=(start\_date, end\_date)) ####

year，month，day，weekday，

#### regex:正则表达式匹配，iregex，不区分大小写的 ####

### Aggregation functions ###

聚合函数，针对查询集合的一些操作

#### Avg：计算平均值，返回值类型float ####

#### Count：计算集合的元素个数，返回值类型int ####

#### Max，Min，Sum ####

# 高级操作 #

**F类**：我的理解是返回特定字段的值

例如我想找到所有评论比广播次数多的entry，就可以按照下面这样写

>>> from django.db.models import F
    >>> Entry.objects.filter(n_comments__gt=F('n_pingbacks'))

F类返回的是该字段的值，当值是数值的是，可以做算术运算，当时字符串是，可以比较是否相等等运算

**pk** ：代表primary key

**Q类**：封装查询已形成更复杂的查询，每个Q查询之间可以用&（and）或者|（or）相连，用逗号相隔表示关系and，Q查询也可以使用~（not）

Poll.objects.get(
        Q(question__startswith='Who'),
        Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
    )
    #roughly translates into the SQL:
    SELECT * from polls WHERE question LIKE 'Who%'
        AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')

注意：Q查询一定要在关键字查询之前，否则查询可能无效

# 关系查询 #

**1：外键查询，一个类可以通过属性的方法来访问外键的类**

>>> e = Entry.objects.get(id=2)
    >>> e.blog = some_blog
    >>> e.save()

**2：一个被外键的类可以通过FOO\_set的方法来访问源类，其中Foo是源类**

>>> b = Blog.objects.get(id=1)
    >>> b.entry_set.all() # Returns all Entry objects related to Blog.
    
    # b.entry_set is a Manager that returns QuerySets.
    >>> b.entry_set.filter(headline__contains='Lennon')
    >>> b.entry_set.count()

3：其他的多对一，一对多，一对一的情况可以类比上面这两种情况

如果你不想使用django提供的api接口，那么你可以通过下面两种方式直接运行sql语句

第一种，通过Manager.raw(*raw\_query*, *params=None*, *translations=None*)的方法运行sql语句

class Person(models.Model):
        first_name = models.CharField(...)
        last_name = models.CharField(...)
        birth_date = models.DateField(...)
    >>> Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')
    ...
    >>> Person.objects.raw('SELECT last_name, birth_date, first_name, id FROM myapp_person')
    ...
    >>> Person.objects.raw('''SELECT first AS first_name,
    ...                              last AS last_name,
    ...                              bd AS birth_date,
    ...                              pk as id,
    ...                       FROM some_other_table''')

第二种：完全不用django提供的api，自己建立数据库连接，运行sql查询

def my_custom_sql():
        from django.db import connection, transaction
        cursor = connection.cursor()
    
        # Data modifying operation - commit required
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        transaction.commit_unless_managed()
    
        # Data retrieval operation - no commit required
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()
    
        return row

转载于:https://www.cnblogs.com/qwj-sysu/p/4151460.html