PART -7

Mastering Django Queries

Atufa Shireen

Jun 13, 2020·3 min read

This chapter will teach you the following:

• Using the Django ORM to query the database and filter through results
• Applying migrations to database

Once you’ve created a model, Django automatically provides a high-level Python API for working with those models.

You can check the raw sql for the table created using

python manage.py sqlmigrate <appname> migration number

or

python manage.py sqlmigrate blogapp 0001 , which gives us

BEGIN;
 — 
 — Create model Post
 — 
CREATE TABLE “blogapp_post” (“id” integer NOT NULL PRIMARY KEY AUTOINCREMENT, “title” varchar(100) NOT NULL, “content” text NOT NULL, “date_posted” datetime NOT NULL, “author_id” integer NOT NULL REFERENCES “auth_user” (“id”) DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX “blogapp_post_author_id_5ec80b2c” ON “blogapp_post” (“author_id”);
COMMIT;

To learn how to use Django’s database API, we’re going to be using the Django interactive shell. Django’s interactive shell runs just like the regular Python interactive shell. Try it out by running python manage.py shell and typing the following:

>>> from blogapp.models import Post
>>> from django.contrib.auth.models import User

The first 2 lines import the Post and User model

Retrieving all objects

>>> User.objects.all()
<QuerySet [<User: atufashireen>]>
>>> Post.objects.all()
<QuerySet []>

The all()method returns a Queryset of all the objects in the database.

Creating objects

>>> post_1=Post(title=’Post 1',content=’post 1 content’,author=x)
>>> post_1.save()
>>> post_2=Post(title='Post2',content='post 2 content',author=x)
>>> post_2.save()

This performs an INSERT SQL statement behind the scenes. Django doesn’t hit the database until you explicitly call save().

Running the Post.objects.all() now will return

<QuerySet [<Post: Post 1>, <Post: Post2>]>

You can now get all the fields of the particular like this..

>>> post_1.title
‘Making A Web App’
>>> post_1.author
<User: atufashireen>
>>> post_1.author.email
'atufashireen@gmail.com'

Retrieving a single object with get()

x= Post.objects.get(pk=1)

returns the first object which have primary key as 1

Related objects

When you define a relationship in a model (i.e., a ForeignKey, OneToOneField, or ManyToManyField), instances of that model will have a convenient API to access the related objects.

Django also creates API accessors for the “other” side of the relationship — the link from the related model to the model that defines the relationship.

>>> post_1.author.post_set.all()
<QuerySet [<Post: Making A Web App>, <Post: Making A Website>]>(or)
>>> x=User.objects.get(id=1)
>>> x.post_set.all()
<QuerySet [<Post: Making A Web App>, <Post: Making A Website>]>

the .model_set (or ) x.post_set.all() returns all the posts of the user (if it has the relationship)

>>> x.post_set.create(title=’post 3',content=’post 3 content’)
<Post: post 3>

creates a new post for the user in the Post model

Retrieving specific objects with filters

To create a subset of a QuerySet, you refine the initial QuerySet, adding filter conditions. The two most common ways to refine a QuerySet are:

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.

>>> Post.objects.filter(id=2).all()
<QuerySet [<Post: Making A Website>]>>>> Post.objects.exclude(id=2).all()
<QuerySet [<Post: Making A Web App>]>>>> Post.objects.filter(date_posted__month=6)
<QuerySet [<Post: Making A Web App>, <Post: Making A Website>]>

Now let’s filter using other attributes of the model.

>>> q1 = Post.objects.filter(title__startswith="Making A Web")
>>> q1
<QuerySet [<Post: Making A Web App>, <Post: Making A Website>]>
>>> q2 = q1.exclude(date_posted__gte=datetime.date.today())
>>> q2
<QuerySet [<Post: Making A Web App>, <Post: Making A Website>]>
>>> q = Post.objects.exclude(content__icontains="building")
>>> q
<QuerySet [<Post: Making A Web App>]>

The first is a base Queryset containing all entries that contain a headline starting with “Making A Web”. The second is a subset of the first, with an additional criteria that excludes records whose date_posted is today or in the future (greater than or equal to) .The third excludes the post which have ‘building’ in it’s content (i for case insensitive)

Deleting objects

The delete method, conveniently, is named delete(). This method immediately deletes the object and returns the number of objects deleted and a dictionary with the number of deletions per object type.

>>> import datetime
>>> x=Post.objects.filter(date_posted__gte=datetime.date.today())
>>> x
<QuerySet [<Post: post 3>]>
>>> x.delete()
(1, {'blogapp.Post': 1})

Let’s now set the home function in views.py to as follows,

def home(request):

    content={'posts':Post.objects.all()}
    return render(request, 'blogapp/home.html',content)

And home.html to..

Next Up..

Previously…