How to get data from MySQL in Django View with Models

In this article, you will learn how to get data from a database in Django.

Django is a high-level Python web framework that enables rapid development of secure and maintainable websites. Django is compatible with almost all popular RDBMS and NoSQL types of databases, like MySQL, PostgreSQL, MongoDB, and Oracle. As for popularity and simplicity, MySQL is most commonly used back-end database for Django. It supports MySQL 5.6 and higher.

These are the step-by-step processes for the MySQL connection with Django and the process to fetch data and display it in the view template.

Install MySQL

MySQL download is available at its official site www.mysql.com/downloads/.
Once the installer package is downloaded, unzip it, run the setup.exe and follow the installation process. By default, it will be installed under the 'C:\MySQL' directory.

Once the process is complete, make sure the installation is done on the command prompt. For this, go to the location of the mysqld server, which is in c:\mysql\bin and type-

mysqld --console

You can also use MySQL on another server, like if you have installed the WAMP or XAMPP server on your system, you can use MySQL of that server.

Django Project

Suppose we have a project name 'demo'-

(env) c:\python37\Scripts\projects\demo>

The MySQL connection with Django requires the installation of the MySQL client. Let's run the following command-

(env) c:\python37\Scripts\projects\school>pip install mysqlclient
Collecting mysqlclient
  Downloading https://files.pythonhosted.org/packages/5d/b3/a753b836eab49c865651eb2bc7203d070c58e5f22b33015b48fa6112bd7a/mysqlclient-1.4.6-cp37-cp37m-win_amd64.whl (262kB)
     |████████████████████████████████| 266kB 726kB/s
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.4.6

If you have faced any errors during the installation of the MySQL client, you can download and try to install the wheel file according to your system OS bits and Python version from here:

http://qpypi.qpython.org/simple/mysqlclient/

Processes to get MySQL data in Django

Create a Model

Django provides Models, which represent a table or collection of database. It is basically a class in which every attribute is the field of the table. Here, we need to deal with student data (roll, sclass, fname, lname). So, we need to create the model of the data and implement that model into the db of Django. Open 'models.py' and put in the following code. The database table 'students' will be created by the following code.

from django.db import connections
from django.db import models

# Create your models here.

class Student(models.Model):   
    roll = models.CharField(max_length=100)
    sclass = models.CharField(max_length=100)
    fname = models.CharField(max_length=100)
    lname = models.CharField(max_length=100)
    class Meta:
        db_table = "students"

Suppose we have the following records in the 'students' table. That, we will have to fetch and display in the HTML template using Django.

INSERT INTO `students` (`id`, `roll`, `sclass`, `fname`, `lname`) VALUES
(1, '12', '1A', 'Priska', 'Kashyap'),
(2, '11', '2A', 'Aditi', 'Vyas');

Create a view

Open 'views.py' and put in the following code. Here, we have first imported the 'render' and 'redirect' modules, and then we have imported the database table we are working with from the 'models.py' file. Then, we retrieve all objects from the students database table and store them in the 'students' variable. So that we can render this to an HTML template file 'show.html'.

from django.shortcuts import render,redirect
from demoapp.models import Student

# Create your views here.

def show(request):
    students = Student.objects.all()
    return render(request,"show.html",{'student':students})

Create a template(UI for Django Model)

Now, create a new directory 'templates' in the 'demoapp' folder and an HTML file 'show.html' and put the following code there.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Django CRUD Operations</title>
  <meta charset="utf-8">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script> 
</head>
<body>
<div class="container">
<table class="table table-striped">
    <thead>
      <tr>
        <th>Student ID</th>
		<th>Roll</th>
		<th>Class</th>
		<th>First Name</th>
		<th>Last Name</th>
      </tr>
    </thead>
    <tbody>
	{% for stud in student %}  
      <tr>
        <td>{{stud.id}}</td>
        <td>{{stud.roll}}</td>
		<td>{{stud.sclass}}</td>
		<td>{{stud.fname}}</td>
		<td>{{stud.lname}}</td>
      </tr>
	  {% endfor %} 
	</tbody>
</table>	
</div>
</body>
</html>

Change settings in 'settings.py'

Django provides default database settings to 'sqlite3', so we will have to change these settings. Here, we have changed the 'ENGINE' setting for MySQL and written the database name in 'NAME', username in 'USER', database password in 'PASSWORD', hostname in 'HOST' and portname in 'PORT'.

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

Change settings in 'urls.py'

The 'urls.py' page contains all the particular paths for routing pages. Put the following code in 'urls.py'.

from django.contrib import admin
from django.urls import path
from demoapp import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('show',views.show),
]

Run the project

Open the command prompt and go to your project directory and activate the virtualenv.

c:\python37\Scripts\projects\env\Scripts>activate

(env) c:\python37\Scripts\projects\env\Scripts>cd ..

To know more in details about 'virtualenv', go to our article-

Windows commands to create and run the first Django app

Now, let's migrate the following project -

(env) c:\python37\Scripts\projects\demo>python manage.py migrate

Once the migration is done, use the following command to run the server -

(env) c:\python37\Scripts\projects\demo>python manage.py runserver

It returns messages something like this -

January 09, 2020 - 14:04:41
Django version 3.0.2, using settings 'demo.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CTRL-BREAK.

Open the 'http://127.0.0.1:8000/show' on the browser. It shows the data as follows-

Python | Uploading images in Django
Django pass variable from view to HTML template
How to read and write a file using Django
Django serialize queryset into JSON and display in template
Django Export Model Data to CSV
Django Custom User Model SignUp, Login and Logout
How to display image from database in Django
Django Pagination with Ajax and jQuery
Django download file
Django Simple File Upload
Django ajax GET and POST request
Django bootstrap 4 form template
Django upload image to database
Windows command to create and run first Django app
Django Send Mail on Contact form submission
How to insert data in MySQL database from an HTML form using Django
Convert MySQL query result to JSON in Python
Display data from MySQL in HTML table using Python
Insert XML Data to MySQL Table using Python
CRUD operations in Python using MYSQL Connector