Django Mysql Retrieving Filtered Data

This is our thirteenth article in Django Mysql, in this article we are going to learn about Django Mysql Retrieving Filtered Data . so in the previous article we have learned that how you can retrieve data from mysql database, you can read this article Django Rendering Data from Mysql Database. so some times you need to get and render filtered data in Django, in this example we are going to learn that how you can retrieve the filtered data according to specific date.

Flask Web Development Tutorials

1: Flask CRUD Application with SQLAlchemy

2: Flask Creating News Web Application

3: Flask Creating REST API with Marshmallow

Python GUI Development Tutorials

1: PyQt5 GUI Development Tutorials

2: Pyside2 GUI Development Tutorials

3: wxPython GUI Development Tutorials

4: Kivy GUI Development Tutorials

5: TKinter GUI Development Tutorials

First of all we need to create a Django Project. you can read the complete article on creating of Django Project in the link Django Introduction & Installation . but you can use this command for project creation and i have named the project as MyProject, but you can give what name you want.

django-admin startproject MyProject

1	django-admin startproject MyProject

Now you need to create a database in the WAMP Server, so my Database name is codeloop. but you can give what ever you want for the database name.

As we have created our Django Project, now we need to open settings.py file in Django Project and bring some changes in the DATABASE section, by default if you see we have SQLite3 database. but we want to use MySQL Database. for this purpose you need to add some configuration of your mysql database, for example like database name, port, username, password and database host.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'codeloop',
        'USER': 'root',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

DATABASES = {

'default': {

'ENGINE': 'django.db.backends.mysql',

'NAME': 'codeloop',

'USER': 'root',

'PASSWORD': '',

'HOST': 'localhost',

'PORT': '3306',

}

After adding these configurations in your settings.py file, you need to migrate your project, because we have added a new database configuration.

cd MyProject
python manage.py migrate

1 2	cd MyProject python manage.py migrate

So now it is time to create our App, i have already talked about Django Apps in details in one of my previous articles, you can check this Django Apps & URL Routing. but you can use this command for creating of the Django App, and the name that i have given for Django App is news. make sure that you have added your newly created App in the INSTALLED_APP of settings.py. always remember, when you have created a new App, you need to add that in your settings.py file.

python manage.py startapp news

1	python manage.py startapp news

So now we need to create our templates, we have already talked about templates in Django, you can read this article Django Templates Introduction. but you need to just create a templates folder in your project and you need to add some html files. also make sure that you have added your templates folder in your settings.py DIRS section.

This is our navbar.html, and we have already included this file in to our base.html, you can check this article for How to Include Template Tag in Django Django Include Template Tag. also we have used url template tag for linking of the views with our navbar, you can read this article for that Django URL Template Tag.

<nav>
<ul>
 
    <li><a href="{% url 'home' %}" >Home</a></li>
    <li><a href="{% url 'contact' %}" >Contact</a></li>
    <li><a href="{% url 'about' %}" >About</a></li>
 
</ul>
 
</nav>

<nav>

<ul>

<li><a href="{% url 'contact' %}" >Contact</a></li>

<li><a href="{% url 'about' %}" >About</a></li>

</ul>

</nav>

OK now this is our base.html.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %}{% endblock %}</title>
</head>
 
{% include 'navbar.html' %}
 
<body>
{% block body %}
 
 
{% endblock %}
</body>
</html>

<!DOCTYPE html>

<head>

<title>{% block title %}{% endblock %}</title>

</head>

{% include 'navbar.html' %}

<body>

{% block body %}

{% endblock %}

</body>

</html>

Now we need to open our models.py file in our news App, we have already talked about Django Models in one of our articles Django Models Introduction. but we are going to just create a simple model that has four fields. and we will filter our data according to the specific date for a news article. if you have watched the previous article about Django Rendering Data from Mysql Database, we had just three fields for our model, but we need to add a new field to our model that is for publication date.

from django.db import models
from django.utils import timezone
# Create your models here.


class News(models.Model):
    author = models.CharField(max_length=100)
    title = models.CharField(max_length=100)
    description = models.TextField()
    pub_date = models.DateField(default=timezone.now())   #newly added


    def __str__(self):
        return self.title

from django.db import models

from django.utils import timezone

# Create your models here.

class News(models.Model):

author = models.CharField(max_length=100)

title = models.CharField(max_length=100)

description = models.TextField()

pub_date = models.DateField(default=timezone.now()) #newly added

def __str__(self):

return self.title

After adding your model, you need to do migrations like this

python manage.py makemigrations

1	python manage.py makemigrations

python manage.py migrate

1	python manage.py migrate

Now you should have some data in to your database, if you don’t have, add some data.

OK after adding some data to your database table, now we need to create our views, and we want to retrieve filtered data in our news_year view. basically we want to filter our data using the specific publication date.

from django.shortcuts import render
from .models import News

def home(request):

    obj = News.objects.all()

    context = {"object":obj}

    return render(request, "home.html", context)


def news_year(request, year):
    a_list = News.objects.filter(pub_date__year = year)

    context = {
        'year':year,
        'article_list':a_list

    }

    return render(request, "details.html", context)



def about(request):
    return render(request, "about.html")

def contact(request):
    return render(request, "contact.html")

from django.shortcuts import render

from .models import News

def home(request):

obj = News.objects.all()

context = {"object":obj}

return render(request, "home.html", context)

def news_year(request, year):

a_list = News.objects.filter(pub_date__year = year)

context = {

'year':year,

'article_list':a_list

}

return render(request, "details.html", context)

def about(request):

return render(request, "about.html")

def contact(request):

return render(request, "contact.html")

You can see that in the news_year view, we have filtered our data according to specific publication date using filter.

Now we need to render the filtered data in our details.html file, and we have used for loop for iterating over the data, you can read the article about django for loop in this link Django Template For Loop.

templates/details.html

{% extends 'base.html' %}

{% block title %} {{year}} {% endblock %}


{% block body %}

<h1>Codeloop.org</h1>
<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

<hr/>

{% for news in article_list %}

<div>
    <h1>{{news.title}}</h1>
    <h4>{{news.pub_date}}</h4>
    <h3>{{news.author}}</h3>
    <p>{{news.description}}</p>
   <hr/>


</div>

{% endfor %}


{% endblock %}

{% extends 'base.html' %}

{% block title %} {{year}} {% endblock %}

{% block body %}

<h1>Codeloop.org</h1>

<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

<hr/>

{% for news in article_list %}

<div>

<h1>{{news.title}}</h1>

<h3>{{news.author}}</h3>

<p>{{news.description}}</p>

<hr/>

</div>

{% endfor %}

{% endblock %}

templates/home.html

{% extends 'base.html' %}

{% block title %} Home {% endblock %}


{% block body %}

<h1>Codeloop.org</h1>
<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

<hr/>

{% for news in object %}

<div>
    <h1>{{news.title}}</h1>
    <h3>{{news.author}}</h3>
    <p>{{news.description}}</p>
   <hr/>


</div>

{% endfor %}


{% endblock %}

{% extends 'base.html' %}

{% block title %} Home {% endblock %}

{% block body %}

<h1>Codeloop.org</h1>

<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

<hr/>

{% for news in object %}

<div>

<h1>{{news.title}}</h1>

<h3>{{news.author}}</h3>

<p>{{news.description}}</p>

<hr/>

</div>

{% endfor %}

{% endblock %}

templates/about.html

{% extends 'base.html' %}

{% block title %} About {% endblock %}


{% block body %}

<h1>Codeloop.org, this is our about page</h1>
<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

{% endblock %}

{% extends 'base.html' %}

{% block title %} About {% endblock %}

{% block body %}

<h1>Codeloop.org, this is our about page</h1>

<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

{% endblock %}

templates/contact.html

{% extends 'base.html' %}

{% block title %} Contact {% endblock %}


{% block body %}

<h1>Codeloop.org, this is our contact page</h1>
<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

{% endblock %}

{% extends 'base.html' %}

{% block title %} Contact {% endblock %}

{% block body %}

<h1>Codeloop.org, this is our contact page</h1>

<p>Django Tutorial Number 13 , Rendering Filtered Data From Mysql Database</p>

{% endblock %}

Also you need to create your urls, you can read this article for url routing Django Apps & URL Routing . but just create a new python file in your news app at name of urls.py and add these codes. you can see that we have added our new url for details.

from django.urls import path
from .views import home, about,contact, news_year

urlpatterns = [
    path('', home, name = 'home'),
   path('year/<int:year>/', news_year, name = 'details'),
   path('about/', about, name = 'about'),
   path('contact/', contact, name = 'contact'),


]

from django.urls import path

from .views import home, about,contact, news_year

urlpatterns = [

path('', home, name = 'home'),

path('year/<int:year>/', news_year, name = 'details'),

path('about/', about, name = 'about'),

path('contact/', contact, name = 'contact'),

]

And also you need to include your app urls.py in your project urls.py file.

from django.contrib import admin
from django.urls import path,include
 
urlpatterns = [
    path('admin/', admin.site.urls),
 
    path('', include('news.urls')),
]

from django.contrib import admin

from django.urls import path,include

urlpatterns = [

path('admin/', admin.site.urls),

path('', include('news.urls')),

]

Now you can run the project and you can see our filtered data in the details page.

python manage.py runserver

1	python manage.py runserver

http://localhost:8000/year/2020/

http://localhost:8000/year/2019/

Subscribe and Get Free Video Courses & Articles in your Email