Some operations of the Django database

1. Data filtering:

Using the filter () method

>>> Publisher.objects.filter (name= ' Apress ')

[<publisher:apress>]

SELECT ID, name, address, city, State_province, country, websitefrom books_publisherwhere name = ' Apress ';

>>> Publisher.objects.filter (country= "U.S.A.", state_province= "CA")

[<publisher:apress>]

SELECT ID, name, address, city, State_province, country, websitefrom books_publisherwhere country = ' U.S.A. ' and State_prov ince = ' CA ';

>>> Publisher.objects.filter (name__contains= "Press") [<publisher:apress>]    equivalent

SELECT ID, name, address, city, State_province, country, websitefrom books_publisherwhere name like '%press% ';

2. Get the Single object  get () method

>>> Publisher.objects.get (name= "Apress") <Publisher:Apress>

Try:    p = Publisher.objects.get (name= ' Apress ') except Publisher.doesnotexist:    print "Apress isn ' t in the Database yet. " else:    print "Apress is in the database."
Exception handling can be done to catch errors

3. Data sequencing  using the Order_by () method

>>> Publisher.objects.order_by ("name") [<publisher:apress>, <publisher:o ' reilly>]

If you need to sort the criteria in more than one field (the second field is used in the same way as the value of the first field), you can use multiple parameters, as follows:

>>> Publisher.objects.order_by ("State_province", "Address") [<publisher:apress>, <publisher:o ' Reilly>]

We can also specify a reverse sort, preceded by a minus - prefix:

>>> Publisher.objects.order_by ("-name") [<publisher:o ' Reilly>, <publisher:apress>]

Default sort:

Django lets you specify the default sort mode for the model:

class Publisher (models. Model): name = models. Charfield (max_length=30) address = models. Charfield (max_length=50) city = models. Charfield (max_length=60) state_province = models. Charfield (max_length=30) country = models. Charfield (max_length=50) website = models. Urlfield () def __unicode__ (self): return self.name **class meta:** **ordering = [' Name ']** 

Now, let's touch on a new concept. class Meta, embedded in the definition of the publisher class (if class publisher is shelf, the class Meta Under it you want to indent 4 spaces-in Python's tradition. You can use the Meta class in any of the model classes to set some options related to a particular model. In Appendix B there is a complete reference to all the options in Meta , and now we're going to focus on the ordering option. If you set this option, the relative return value of thePublisher object will default to when you use the Django database API to retrieve it unless you specifically use order_by ()in your search. The name field is sorted.

4. Chain query:

We already know how to filter and sort the data. Of course, we usually need to filter and sort the queries at the same time. So you can simply write this "chained" form:

>>> Publisher.objects.filter (country= "U.S.A."). Order_by ("-name") [<publisher:o ' Reilly>, <publisher:apress>]

You should have guessed right, the conversion to SQL query is the combination of WHERE and ORDER by:

SELECT ID, name, address, city, State_province, country, websitefrom books_publisherwhere country = ' U.S.A ' ORDER by name D ESC;



5. Limit the data returned: Crop a statement using a standard list

Another common requirement is to take out a fixed number of records. Imagine you have thousands of publishers in your database, but you just want to show the first one. You can use the standard Python list to crop the statement:

6

>>> Publisher.objects.order_by (' name ') [0]<publisher:apress>

This is equivalent to:

SELECT ID, name, address, city, State_province, Country, Websitefrom books_publisherorder by namelimit 1;

Similarly, you can use Python's range-slicing syntax to extract a specific subset of the data:

1

>>> Publisher.objects.order_by (' name ') [0:2]

This example returns two objects, equivalent to the following SQL statement:

SELECT ID, name, address, city, State_province, Country, Websitefrom Books_publisherorder by Nameoffset 0 LIMIT 2;

Note that Python's negative index (negative slicing) is not supported:

>>> Publisher.objects.order_by (' name ') [ -1]traceback (most recent):  ... Assertionerror:negative indexing is not supported.

Although negative indexes are not supported, we can use other methods. For example, slightly modify the order_by () statement to implement:

>>> Publisher.objects.order_by ('-name ') [0]

6. Updating multiple objects The Django Save () method not only updates the value of the specified column, but updates all the columns, and if we just update a column, we use the update () function,

>>> Publisher.objects.filter (id=52). Update (name= ' Apress publishing ')

The equivalent SQL statement becomes more efficient and does not cause race conditions.

UPDATE books_publisherset name = ' Apress publishing ' WHERE id = 52;

The update () method is valid for any result set (QuerySet), which means that you can update multiple records at the same time. The following example shows how to set all Publisher's Country field values by ' U. S.A. ' changed to ' USA ':

>>> Publisher.objects.all (). Update (country= ' USA ')

The update () method returns an integer value that represents the number of record bars affected. In the example above, this value is 2.

7. Delete Object   Delete object in database only need to call the object's Delete () method

>>> p = Publisher.objects.get (name= "O ' Reilly") >>> p.delete () >>> Publisher.objects.all () [ <publisher:apress Publishing>]

Similarly, we can call the Delete () method on the result set to delete multiple records at the same time. This is similar to the update () method we mentioned in the previous section:

2

>>> Publisher.objects.filter (country= ' USA '). Delete () >>> Publisher.objects.all (). Delete () > >> Publisher.objects.all () []

Be cautious when deleting data! To prevent accidental deletion of all data in a table, Django requires that all data in the table be deleted with all (). For example, the following operation will make an error:

1

>>> Publisher.objects.delete () Traceback (most recent):  File "<console>", line 1, in < Module>attributeerror: ' Manager ' object has no attribute ' delete '

Once the All () method is used, all data will be deleted:

>>> Publisher.objects.all (). Delete ()

If you only need to delete part of the data, you do not need to call the all () method. Look again at the previous example:

>>> Publisher.objects.filter (country= ' USA '). Delete ()

Some operations of the Django database