Django database Query

Class Poll (models. Model):

slug = models. Slugfield (unique_for_month= ' pub_date ')
Question = models. Charfield (maxlength=255)
Pub_date = models. Datetimefield ()
Expire_date = models. Datetimefield ()

def __repr__ (self):
Return self.question

Class Meta:
get_latest_by = ' Pub_date '

Class Choice (models. Model):
Poll = models. ForeignKey (Poll, Edit_inline=models. Tabular,
num_in_admin=10, min_num_in_admin=5)
Choice = models. Charfield (maxlength=255, Core=true)
Votes = models. Integerfield (Editable=false, default=0)

def __repr__ (self):
Return Self.choice

and the following simple session:

>>> from datetime import datetime
>>> P1 = Poll (slug= ' WhatsUp ', question= "What's Up?",
... Pub_date=datetime (2005, 2, 20), Expire_date=datetime (2005, 4,))
>>> P1.save ()
>>> P2 = Poll (slug= ' name ', question= "What ' s your name?",
... Pub_date=datetime (2005, 3, 25), Expire_date=datetime (2005, 3,))
>>> P2.save ()
>>> Poll.objects.all ()
[What's Up?, what ' s your name?]

How the query works

The Django data query is based on building the result set and taking value on the result set. A result set is a collection of data objects that are independent of a database and conform to a query condition. This is an inert collection: You cannot know which members of the collection are available until the collection is evaluated.

To generate a result set that meets your needs, you first get an initial result set that describes all the objects of a given type. This initial result set can be refined by a series of functions. When a processed result set meets your requirements, you can take a value operation (using an iterative operation, Slicing operation, or a series of other techniques), to get a list of objects or objects you need.
Get the initial result set

Each Django model has an innate Manager object objects, and the manager's most important role is as the source of the initial results. A manager is a special initial result set that describes all objects of a given type. Poll.objects is an initial result set that contains all the Poll objects. The only special thing about it is that it cannot be evaluated. To overcome this limitation, the Manager object has an all () method. The method generates a copy of the initial result set that can be evaluated:

All_polls = Poll.objects.all ()

Refer to the Managers section of the Model API for the manager's positioning and creation details.
Optimize custom Result Sets

The initial result set provided by the manager describes all objects of a given type. But usually you only need a small subset of this object set.

To create such a result set, you need to refine and customize the initial result set, adding some restrictions until the subset of the description meets your needs. The two most common ways to customize a result set are:

Filter (**kwargs)
Returns a new result set that matches the query parameters.
Exclude (**kwargs)
Returns a new result set that does not match the query parameters.

The parameter format is described in the "Field query" subsection below.

The return values of both methods are result set objects, so the result set can be chained:

Poll.objects.filter (
Question__startswith= "what"). Exclude (
Pub_date__gte=datetime.now ()). Filter (
Pub_date__gte=datetime (2005,1,1))

... Take an initial result set as an argument, filter it, then exclude it, and then make another filter. So get the end result on one question the beginning of the word is "what", a collection of all polls that release the date on January 1, 2005 to the present.

Each result set is a unique object. Each step of the above operation generates a new result set:

Q1 = Poll.objects.filter (question__startswith= "what")
Q2 = Q1.exclude (Pub_date__gte=datetime.now ())
Q3 = Q1.filter (Pub_date__gte=datetime.now ())

These three steps generate three result sets; An initial result set contains all polls starting with "what", a subset of two initial result sets (one exclusion condition, one filter condition). The process of improving the original result set does not affect the original result set.

It is important to note that the creation of the result set does not have access to the database at all. The database is accessed only when the result set is evaluated.
Field Query

Basic field queries are made in the form of field__lookuptype (note double downline), for example:

Polls.objects.filter (Pub_date__lte=datetime.now ())

The query is translated into sql:

SELECT * from Polls_polls WHERE pub_date <= now ();

Implementation Details

Python can accept arbitrary name-value (names and values can be computed at run time) parameters when defining a function. To learn more, see the keyword parameters in the official Python tutorial.

The DB API supports the following lookup types:

Type description
Exact exact match: Polls.get_object (id__exact=14).
Iexact ignores the exact match of the case: Polls.objects.filter (slug__iexact= "foo") matches foo, Foo, foo, and so on.
Contains case sensitive content contains tests: Polls.objects.filter (question__contains= "spam") returns all polls that contain "question" in spam. (PostgreSQL and MySQL support only.) The like statement for SQLite does not support case sensitive attributes. For SQLite, contains equals Icontains.)
Icontains case-insensitive content contains tests:
GT greater than: Polls.objects.filter (id__gt=4).
GTE is greater than or equal to.
LT is less than.
LTE is less than equals.
NE is not equal to.
In on the given list: Polls.objects.filter (Id__in=[1, 3, 4]) returns a list of polls (ID values are 1 or 3 or 4, respectively).
StartsWith the case Sensitive Starts-with:polls.objects.filter (question__startswith= "would"). (PostgreSQL and MySQL support only.) The like statement for SQLite does not support case sensitive attributes. For SQLite, ' startswith ' equals Istartswith)
EndsWith case Sensitive Ends-with. (PostgreSQL and MySQL only)
Istartswith case-insensitive starts-with.
Iendswith case-insensitive ends-with.
Range Range Test: Polls.objects.filter (pub_date__range= (start_date, end_date)) return pub_date at Start_date and end_date (included) All polls between the
Year for the Date/datetime field, make an exact annual match: Polls.get_count (pub_date__year=2005).
Month for the Date/datetime field, make an exact monthly match:
Day for the Date/datetime field, make an exact daily match:
IsNull True/false; Do IF null/if not NULL query: Polls.objects.filter (expire_date__isnull=true).

If a lookup type is not provided, the system assumes that the lookup type is exact. The following two statements are equivalent:

Poll.objects.get (ID=14)
Poll.objects.get (ID__EXACT=14)

Queries allow multiple conditional parameters, and multiple conditional parameters separated by commas are used by "and":

Polls.objects.filter (
pub_date__year=2005,
Pub_date__month=1,
Question__startswith= "would",
)

... Get all the polls published in January 2005 with a question starting with a "would".

For ease of use, there is also a PK lookup type that can be translated into (Primary_key) __exact. In this poll example, the following two statements are equivalent.:

Polls.get_object (id__exact=3)
Polls.get_object (pk=3)

PK can also be queried via a connection. In this poll example, the following two statements are equivalent:

Choices.objects.filter (poll__id__exact=3)
Choices.objects.filter (poll__pk=3)

If the passed keyword argument is illegal, an TypeError exception is thrown.
OR Query

Each condition of a keyword parameter query is an "and" relationship. If you need a complex query (for example, you need an OR statement), you need to use the Q object.

The Q object is an instance of DJANGO.CORE.META.Q that is used to load a series of keyword parameters. These keyword parameters are like the keyword parameters assigned to the Get () and filter () functions. For example:

Q (question__startswith= ' what ')

Q objects can use & and | operator is combined. When a two Q object is & or | Operation, a new Q object is generated. For example, the statement:

Q (question__startswith= ' Who ') | Q (question__startswith= ' what ')

... A new Q object is generated to represent the "OR" relationship between these two "question__startswith" query conditions. is equivalent to the following SQL WHERE clause:

... WHERE question like ' who% ' OR question like ' what% '

Through the & of multiple Q objects | Operation you can get arbitrary complex query statements. You can also use parentheses to group.

A query function can accept one or more Q objects as arguments. If you provide multiple Q object parameters, they will be "and" together. For example:

Polls.get_object (
Q (question__startswith= ' who '),
Q (Pub_date__exact=date (2005, 5, 2)) | Q (Pub_date__exact=date (2005, 5, 6))
)

... This is how it is translated into SQL:

SELECT * From polls WHERE question like ' who% '
and (pub_date = ' 2005-05-02 ' OR pub_date = ' 2005-05-06 ')

If required, query functions can be mixed using the Q object parameter and the keyword parameter. All parameters provided to the query function, whether keyword or q, are "and" together. If the Q object is supplied as a parameter, it must precede the other keyword arguments, if any. For example:

Polls.get_object (
Q (Pub_date__exact=date (2005, 5, 2)) | Q (Pub_date__exact=date (2005, 5, 6)),
Question__startswith= ' who ')

... This is a valid query, equivalent to the previous example, but:

# INVALID QUERY
Polls.get_object (
Question__startswith= ' Who ',
Q (Pub_date__exact=date (2005, 5, 2)) | Q (Pub_date__exact=date (2005, 5, 6)))

... This query does not conform to our rules and throws an exception.

The Q object can also be used in the form of the complex keyword parameter. For example:

Polls.get_object (
Complex=q (question__startswith= ' who ') &
(Q (Pub_date__exact=date (2005, 5, 2)) |
Q (Pub_date__exact=date (2005, 5, 6))
)
)

See the OR query sample to read more instances.
To take a value from a result set

You can only get the object that the result set contains by taking a value operation. The value operation may be implemented by iterating, slicing, or other specialized functions.

A result set is an iterative object. Therefore, a loop can be used to remove its value:

For P in Poll.objects.all ():
Print P

All Poll objects will be printed out using the __repr__ () method of the Poll object.

A result set can also be sliced and manipulated using array symbols:

Fifth_poll = Poll.objects.all () [4]
All_polls_but_the_first_two = Poll.objects.all () [2:]
Every_second_poll = Poll.objects.all () [:: 2]

Result set objects are lazy objects-that is, they do not really contain a collection (or list) of objects they represent. Python's protocol magic makes the result set appear to be an iterative, sliced object. In fact, behind the scenes, Django uses the caching technology.

If you really need a list, you can force a value on a lazy object:

Querylist = List (Poll.objects.all ())

However, it is best not to do this, especially if a result set is quite large. Because Django wants to create a memory representation of each object, this consumes a considerable amount of memory.
Result set and its caching behavior

Each result set contains a cache. For a newly created result set, the buffer is empty. When a result set is first evaluated, Django makes a database query, puts the query results into the cache, and then returns the data that the user needs. The subsequent value operation uses the data in the cache without having to access the database again.

You must always remember that the result set has cached behavior. The following two lines of statements generate two temporary result sets, take values, and discard them:

Print [P for P in Poll.objects.all ()] # Evaluate the Query Set
Print [P for P in Poll.objects.all ()] # Evaluate the Query Set again

For a small, low-traffic site, this does not cause serious problems. However, for a high-volume site, it doubles the burden on the database server. In addition, these two operations may not have the same result because other users may have added or removed votes between two operations.

To avoid this problem, save the result set and reuse the result set later:

Queryset = Poll.objects.all ()
Print [P for P in Queryset] # Evaluate the query set
Print [P for P in Queryset] # re-use the cache from the evaluation

Specialized result set accessor functions

The following functions can also be used to take values from a result set. Unlike iteration and slice operations, these methods do not have caching behavior. Each time you use these functions, you access the database.
Get (**kwargs)

Returns the object matching the lookup parameter in the field query format described below. If an object that matches a given parameter is not found, a module-level Doesnotexist exception is thrown. If more than one object is found, a Assertionerror exception is thrown.
Count ()

Returns the number of rows in the result set. ' Count () ' never throws an exception.

Depending on the database engine you are using (such as PostgreSQL vs. MySQL), it may return a long integer instead of a normal integer.
In_bulk (Id_list)

Takes an ID list as a parameter and returns a dictionary (each ID maps an instance of an object with the given ID). Also accepts optional keyword query parameters (parameter formats are described in the "Field query" section below), here is an example using the Poll model defined above.

>>> Poll.objects.in_bulk ([1])
{1:what ' s up?}
>>> Poll.objects.in_bulk ([1, 2])
{1:what's up?, 2:what ' s your name?}
>>> Poll.objects.in_bulk ([])
{}

Latest (Field_name=none)

Returns the most recent object according to the model's ' get_latest_by ' option or an optional field name parameter. Example:

>>> Poll.objects.latest ()
What's up?
>>> Poll.objects.latest (' expire_date ')
What ' s your name?

Relationship (Connection)

When you define a relationship field in model (that is, a foreignkey, Onetoonefield, or Manytomanyfield). Django uses the Relationship field name to add a descriptor for each instance of model. This descriptor is like a regular property when accessing an object or associated object. For example, Mychoice.poll returns the poll object associated with the Choice instance object.

The connection can be performed in a non-explicit manner by using the following relationship: Choices.objects.filter (poll__slug= "eggs") gets a list of Choice objects that are associated poll object slug field value eggs. Multi-level connections are allowed.

An object instance's convenience function (convenience functions) allows you to directly query the object's associated object. For example, if P is a Poll instance, P.choice_set () returns a list of all associated choice objects. Smart readers will notice that it is equivalent to Choices.objects.filter (poll__id=p.id), just clearer.

Each type of relationship automatically creates a series of convenient methods (such as Choice_set ()) for each object in the relationship. These methods are created in both directions so that the associated object does not need to explicitly define the inverse relationship, which is done automatically.
One-to-one relations

Each object in the one-to-one relationship has a get_relatedobjectname () method. For example:

Class place (meta. Model):
# ...

Class Restaurant (meta. Model):
# ...
The_place = Meta. Onetoonefield (places. place)

In the example above, each place will automatically have a get_restaurant () method, and each restaurant will automatically have a Get_the_place () method.
Many-to-one relations

In a many-to-one relationship, the associated object (many) automatically has a Get_relatedobject () method. The associated object (one) automatically owns the Get_relatedobject (), Get_relatedobject_list (), and Get_relatedobject_count () methods (function and module-level get_object (), Filter (), same as get_count ().

In the public opinion test example above, a Poll object P automatically has the following methods:

P.get_choice ()
P.get_choice_list ()
P.get_choice_count ()

The Choice object C automatically has the following method:

C.get_poll ()

Many-to-many relationship

The many-to-many relationship is similar to ' many-to-one relations ' _, which produces the same set of methods. The exception is that the Get_relatedobject_list () method of the associated object returns a list of instances instead of one instance. Therefore, if Poll and Choice are many-to-many relationships, and Choice.get_poll_list () returns a list.
A specialized result set

In addition to filter and exclude (), Django provides a series of result set processing methods, modifies the type of the result, or modifies how the SQL query executes in the database.
Order_by (*fields)

The ordering tuple is provided according to the model, and the result assembly is automatically sorted. However, sorting can also be done explicitly by the Order_by method:

Poll.objects.filter (pub_date__year=2005,
pub_date__month=1). order_by ('-pub_date ', ' question ')

The result set will be sorted in descending order pub_date, and then sorted in ascending order question. " The minus sign in-pub_date indicates descending (decreasing). To take a random order, use "?", like this:

Poll.objects.order_by= ('? ')

To sort by a field in another table, add the name of another table and a period, as follows:

choice.objects.order_by= (' poll.pub_date ', ' Choice ')

You cannot specify whether the sort is case sensitive, and Django will reorder the result set in a case-sensitive manner, regardless of how your database backend is sorted. (Does this reduce efficiency?) can not turn off this feature?)
Distinct ()

By default, a result set does not automatically remove duplicate rows. Especially when you're making cross-relationship queries, it's easy to duplicate rows.

Distinct () returns a new result set that has the duplicate row removed, which is equivalent to the SELECT distinct SQL statement.
VALUES (*fields)

Like filter (), but it returns a list of dictionaries instead of a list of model instance objects.

It accepts an optional parameter: Fields, which is a list of field names or tuple. If you do not specify fields, each field is returned. Otherwise, only the field names and values you specify are returned. Here is an example of the Poll model defined above.

>>> from datetime import datetime
>>> P1 = Poll (slug= ' WhatsUp ', question= "What's Up?",
... Pub_date=datetime (2005, 2, 20), Expire_date=datetime (2005, 3,))
>>> P1.save ()
>>> P2 = Poll (slug= ' name ', question= "What ' s your name?",
... Pub_date=datetime (2005, 3, 20), Expire_date=datetime (2005, 4,))
>>> P2.save ()
>>> Poll.objects.all ()
[What's Up?, what ' s your name?]
>>> Poll.objects.values ()
[{' id ': 1, ' slug ': ' whatsup ', ' question ': "What's Up?", ' Pub_date ': Datetime.datetime (2005, 2,), ' expire_date ': Datetim E.datetime (2005, 3, 20)},
{' id ': 2, ' slug ': ' name ', ' question ': ' What ' s your name? ', ' Pub_date ': Datetime.datetime (2005, 3,), ' expire_date ': Date Time.datetime (2005, 4, 20)}]
>>> Poll.objects.values (fields=[' id ', ' slug ')
[{' id ': 1, ' slug ': ' whatsup '}, {' id ': 2, ' slug ': ' name '}]

Use the values () function when you know what field values you want to get and you don't need the functionality of those model instance objects.

Django database Query