Use MySQL in Django web application

This article at OpenGenus demonstrates how to use MySQL in Django web application. This is demonstrated by using a Post model.

Table of contents:

1. What is Django?
2. Django Web Framework
3. MySQL
4. Django & MySQL
5. Installation & Setup

What is Django?

Django is a web-development framework that helps in building and maintaining web applications. It is pronounced as "JANG-oh". It is a high level Python web framework that promotes fast & clean development with simple design. It is free and open source that runs on all OS such as UNIX, Windows, Mac and Solaris.
Django provides developers with pre-built components that can be used to avoid repetitive tasks, thereby saving time and making the development process much easier with limited coding. The framework is licensed under BSD license and is a registered trademark of the Django Software Foundation.

Django Web Framework

Django uses MVC(Model-View-Controller) pattern. MVC is popular as it isolates the application logic from the user interface layer and supprts separation of concerns.

• Model: It describes uour data structure/schema
• View: Controls what a user sees.
• Controller: Handles client request
  

MySQL

Python supports various databases like MySQL, DDl, DMl. MySQL is an open-source relational database management system (RDBMS) that is used for managing and storing structured data. MySQL has many features that make it a popular choice for developers, including its ability to handle large datasets, its speed, reliability, and scalability and is free to use which makes it widely used for web-based applications.

MySQL is based on the SQL (Structured Query Language) and is used to store and retrieve data. It supports multiple storage engines, which determine how data is stored and accessed. The most common storage engines in MySQL are InnoDB, MyISAM, and MEMORY. It can be used on various operating systems including Windows, Linux, and macOS. It is often used in combination with other web technologies like PHP, Apache, and Nginx to create web applications. Also it supports a wide range of data types and can be easily integrated with other programming languages.

Django & MySQL

Using MySQL with Django is straightforward. The Django ORM provides a high-level and easy-to-use interface for working with MySQL databases.
Django ORM (Object-Relational Mapping) is a powerful and easy-to-use tool for working with databases in Django. It provides a high-level, Pythonic interface for accessing and manipulating database records, without the need to write SQL queries directly. With the Django ORM, you define your database schema using Python classes called models.Each model represents a database table, and each attribute of the model represents a column in the table. You can define relationships between models using foreign keys and many-to-many fields.
Here's an example of a simple Django model:

from django.db import models

class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    pub_date = models.DateTimeField(auto_now_add=True)

In this example, the Post model has three attributes: title, content, and pub_date. The title attribute is a CharField that represents a text column in the database with a maximum length of 200 characters. The content attribute is a TextField that represents a larger text column. The pub_date attribute is a DateTimeField that represents a date and time column, with the auto_now_add argument set to True, which means it will automatically set the value to the current date and time when the object is created.

• Once you've defined your models, you can use the Django ORM to perform database operations like creating, reading, updating, and deleting records.

# Create a new blog post
post = Post(title='My first post', content='This is my first blog post.')
post.save()

# Retrieve a blog post by ID
post = Post.objects.get(id=1)

# Update a blog post
post.title = 'Updated title'
post.save()

# Delete a blog post
post.delete()

The Django ORM also provides powerful querying capabilities that allow you to filter, sort, and aggregate data from your database.

posts = Post.objects.filter(title__icontains='django')

Here the filter() method is used to retrieve all Post objects where the title attribute contains the word "django", using the icontains lookup which performs a case-insensitive match.

To use MySQL with Django, you need to follow these steps:

1. Install MySQL server and client libraries on your system. You can download MySQL from the official website and install it on your system.
2. Install the MySQL Python connector. You can install it using pip, the Python package manager, by running the following command: pip install mysql-connector-python.
3. In your Django project settings, configure the database settings. You need to provide the database engine, database name, username, password, host, and port.
4. Create the database tables. You can do this by running the following command: python manage.py migrate. This will create the necessary tables in the MySQL database.
5. Use the Django ORM (Object-Relational Mapping) to interact with the database. The ORM provides a high-level interface for creating, reading, updating, and deleting database records.

from myapp.models import MyModel

my_model = MyModel(name='John Doe', email='johndoe@example.com')
my_model.save()

Installation & Setup

Install the Mysql database, After downloading install the setup and set the admin and password.

Install Django

pip install django

Then install another library to use the MySQL database

pip install mysqlclient

Create a new project

django-admin startproject MyDB

Move to the MyDB folder

cd MyDB

Create a MySql database.
Update the settings.py

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

In Django, to switch from SQLite to MySQL database, we need to update the 'DATABASES' configuration in the settings.py file. We need to replace the value of 'ENGINE' from 'django.db.backends.sqlite3' to 'django.db.backends.mysql'.

The 'NAME' field indicates the name of the MySQL database that we want to connect to. The 'USER' field specifies the MySQL username which has access to the database and manages it. The 'PASSWORD' field contains the password for the database.

The 'HOST' field specifies the hostname where the MySQL database is hosted, and the 'PORT' field indicates the port number to connect to the database. In the given example, the hostname is set to "127.0.0.1" and the port number is set to "3306", which means that the MySQL database can be accessed at the hostname "127.0.0.1" on port "3306".
Run the server

python manage.py runserver

Run the migration command

python manage.py makemigrations
python manage.py migrate

Read more about Django intergration with MySQL