Django database Query

classBlog (models. Model): Name= Models. Charfield (max_length=100) Tagline=models. TextField ()def __unicode__(self):returnSelf.nameclassAuthor (models. Model): Name= Models. Charfield (max_length=50) Email=models. Emailfield ()def __unicode__(self):returnSelf.nameclassEntry (models. Model): Blog=models. ForeignKey (Blog) headline= Models. Charfield (max_length=255) Body_text=models. TextField () pub_date=models. Datetimefield () Authors=models. Manytomanyfield (Author)def __unicode__(self):returnSelf.headline

This is model, with Blog,author, and entry, where entry is associated with a blog and author table, entry and the blog table is through the foreign key (models. ForeignKey ()) is connected to a one-to-many relationship where a entry corresponds to multiple blog,entry and author is many-to-many relationships through modles. Manytomanyfield () implementation.
First, insert the database, implemented with the Save () method, as follows:
>>> from mysite.blog.models Import Blog
>>> b = Blog (name= ' Beatles blog ', tagline= ' all the latest Beatles news. ')
>>> B.save ()

Second, update the database, also with the Save () method is implemented, as follows:
>> b5.name = ' New name '
>> B5.save ()


Save fields for foreign keys and many-to-many relationships, as in the following example:
Updating a foreign key field is the same as a normal field, as long as you specify the correct type of an object.
>>> Cheese_blog = Blog.objects.get (name= "Cheddar talk")
>>> Entry.blog = Cheese_blog
>>> Entry.save ()

When updating many-to-many fields is not the same, add the value of the associated field using the Add () method.
>> Joe = Author.objects.create (name= "Joe")
>> Entry.authors.add (Joe)

Iii. Retrieval of 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."

1. Retrieving all objects

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

Use the All () method to return all objects in the database.

2. Retrieving specific objects
Use the following two methods:
Fileter (**kwargs)
Returns a queryset that matches the argument, equal to (=).
Exclude (**kwargs)
Returns a queryset that does not match the parameter, equal to (! =).

Entry.objects.filter (pub_date__year=2006)
Do not use Entry.objects.all (). Filter (pub_date__year=2006), although it can also run, all () is best to use when retrieving all the objects.
The above example is equivalent to the SQL statement:
Slect * from entry where pub_date_year= ' 2006 '

Link filter:
>>> Entry.objects.filter (
... headline__startswith= ' What '
... ). Exclude
... Pub_date__gte=datetime.now ()
... ). Filter
... Pub_date__gte=datetime (2005, 1, 1)
... )

The last returned Queryset is headline like ' what% ' and Put_date<now () and pub_date>2005-01-01

Another method:
>> q1 = Entry.objects.filter (headline__startswith= "what")
>> q2 = q1.exclude (Pub_date__gte=datetime.now ())
>> q3 = Q1.filter (Pub_date__gte=datetime.now ())
The benefit of this approach is that Q1 can be reused.

Queryset is a deferred load
Access the database only when it is used, as follows:
>>> q = Entry.objects.filter (headline__startswith= "what")
>>> q = Q.filter (Pub_date__lte=datetime.now ())
>>> q = q.exclude (body_text__icontains= "food")
>>> Print Q
The database is not accessed at print Q.

Other methods of Queryset
>>> Entry.objects.all () [: 5]
This is the data found in the first 5 entry tables.

>>> Entry.objects.all () [5:10]
This is to find data from 5th to 10th.

>>> Entry.objects.all () [: 10:2]
This is the data for the query from No. 0 to 10th, with a step size of 2.

>>> Entry.objects.order_by (' headline ') [0]
This is the first object that is sorted by the headline field.

>>> Entry.objects.order_by (' headline ') [0:1].get ()
This is equivalent to the above.

>>> Entry.objects.filter (pub_date__lte= ' 2006-01-01 ')
Equivalent to select * from Blog_entry WHERE pub_date <= ' 2006-01-01 ';

>>> Entry.objects.get (headline__exact= "Man Bites Dog")
Equivalent to select ... WHERE headline = ' Man Bites dog ';

>>> Blog.objects.get (id__exact=14) # Explicit form
>>> Blog.objects.get (id=14) # __exact is implied
The two methods are identical and are the objects that look for id=14.

>>> Blog.objects.get (name__iexact= "Beatles Blog")
Find Name= "Beatles Blog" object, not to eat the case.

Entry.objects.get (headline__contains= ' Lennon ')
Equivalent to select ... WHERE headline like '%lennon% ';

StartsWith is equivalent to the name like ' lennon% ' in the SQL statement,
EndsWith is equivalent to the name like '%lennon ' in the SQL statement.

>>> Entry.objects.filter (blog__name__exact= ' Beatles blog ')
Find the Entry object of the Entry table foreign key relationship blog_name= ' Beatles Blog '.

>>> Blog.objects.filter (entry__headline__contains= ' Lennon ')
Find blog Table foreign key relationships the headline field in the Entry table contains Lennon blog data.

Blog.objects.filter (entry__author__name= ' Lennon ')
Find blog Table foreign key relationships the author field in the Entry table contains Lennon blog data.

Blog.objects.filter (Entry__author__name__isnull=true)
Blog.objects.filter (Entry__author__isnull=false,entry__author__name__isnull=true)
Query for a value that is author_name null

Blog.objects.filter (entry__headline__contains= ' Lennon ', entry__pub_date__year=2008)
Blog.objects.filter (entry__headline__contains= ' Lennon '). Filter (entry__pub_date__year=2008)
Both of these queries are the same in some cases, and in some cases they are different. The first is to limit all the blog data, whereas the second case is the first filter
Restrict the blog, while the second filter is limited to entry

>>> Blog.objects.get (id__exact=14) # Explicit form
>>> Blog.objects.get (id=14) # __exact is implied
>>> Blog.objects.get (pk=14) # PK implies id__exact
Equivalent to select * from where id=14


# Get Blogs entries with ID 1, 4 and 7
>>> Blog.objects.filter (pk__in=[1,4,7])
Equivalent to select * from where ID in{1,4,7}
# Get All blogs entries with ID > 14
>>> Blog.objects.filter (pk__gt=14)
Equivalent to select * from id>14

>>> Entry.objects.filter (blog__id__exact=3) # Explicit form
>>> Entry.objects.filter (blog__id=3) # __exact is implied
>>> Entry.objects.filter (blog__pk=3) # __PK implies __id__exact
These three cases are the same


>>> Entry.objects.filter (headline__contains= '% ')
Equivalent to select ... WHERE headline like '%\%% ';


Caching and Querysets

>>> Print [E.headline for E in Entry.objects.all ()]
>>> Print [e.pub_date for E in Entry.objects.all ()]
Should be rewritten as:
>> Queryset = Poll.objects.all ()
>>> Print [P.headline for P in Queryset] # Evaluate the query set.
>>> Print [P.pub_date for P in Queryset] # re-use the cache from the evaluation.
This uses caching to reduce the number of times the database is accessed.

Four, using Q object to achieve complex query

Q (question__startswith= ' Who ') | Q (question__startswith= ' what ')
Equivalent to where question like ' who% ' OR question like ' what% '


Poll.objects.get (
Q (question__startswith= ' who '),
Q (Pub_date=date (2005, 5, 2)) | Q (Pub_date=date (2005, 5, 6))
)
Equivalent to select * from polls WHERE question like ' who% ' and (pub_date = ' 2005-05-02 ' OR pub_date = ' 2005-05-06 ')

Poll.objects.get (
Q (Pub_date=date (2005, 5, 2)) | Q (Pub_date=date (2005, 5, 6)),
Question__startswith= ' who ')
Equivalent to Poll.objects.get (Question__startswith= ' Who ', Q (Pub_date=date (2005, 5, 2)) | Q (Pub_date=date (2005, 5, 6)))


V. Objects of comparison

>>> Some_entry = = Other_entry
>>> Some_entry.id = = Other_entry.id


Vi. deletion of

Entry.objects.filter (pub_date__year=2005). Delete ()


b = Blog.objects.get (pk=1)
# This would delete the Blog and all of its Entry objects.
B.delete ()

Entry.objects.all (). Delete ()
Delete all

七、一次 Updating multiple values

# Update All the headlines with Pub_date in 2007.
Entry.objects.filter (pub_date__year=2007). Update (headline= ' Everything is the same ')

>>> B = Blog.objects.get (pk=1)
# change every Entry so it belongs to the this Blog.
>>> Entry.objects.all (). Update (BLOG=B)

If you use the Save () method, you must save it one at a to walk through it, as follows:
For item in My_queryset:
Item.Save ()

Associating objects

One-to-many
>>> e = Entry.objects.get (id=2)
>>> E.blog # Returns the related blog object.


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

>>> e = Entry.objects.get (id=2)
>>> E.blog = None
>>> E.save () # "UPDATE blog_entry SET blog_id = NULL ...;"

>>> e = Entry.objects.get (id=2)
>>> Print E.blog # Hits The database to retrieve the associated blog.
>>> Print E.blog # doesn ' t hit the database; Uses cached version.

>>> e = Entry.objects.select_related (). Get (id=2)
>>> Print E.blog # doesn ' t hit the database; Uses cached version.
>>> Print E.blog # doesn ' t hit the database; Uses cached version

>>> 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 ()


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

You cannot access a reverse ForeignKey Manager from the class; It must is accessed from an instance:
>>> Blog.entry_set

Add (Obj1, Obj2, ...)
Adds the specified model objects to the related object set.
Create (**kwargs)
Creates a new object, saves it and puts it in the related object set. Returns the newly created object.
Remove (obj1, obj2, ...)
Removes the specified model objects from the related object set.
Clear ()
Removes all objects from the related object set.

Many-to-many Type:
E = Entry.objects.get (id=3)
E.authors.all () # Returns all Author objects for this Entry.
E.authors.count ()
E.authors.filter (name__contains= ' John ')
A = Author.objects.get (id=5)
A.entry_set.all () # Returns All entry objects for this Author.

One-to-one type:
Class Entrydetail (models. Model):
Entry = models. Onetoonefield (Entry)
Details = models. TextField ()

ed = EntryDetail.objects.get (id=2)
Ed.entry # Returns The related entry object


To query using SQL statements:

def my_custom_sql (self):
From django.db Import Connection
cursor = Connection.cursor ()
Cursor.execute ("Select Foo from bar WHERE baz =%s", [Self.baz])
row = Cursor.fetchone ()
Return row

http://canofy.iteye.com/blog/293494

Django database Query