How to Connect MySQL Database with Django Project

Introduction

The database is the storehouse of an organized collection of data that is used from small projects to big projects to store information persistently so that we can easily access them when we need it.

Django officially supports different types of databases in its buckets like PostgreSQL, MariaDB, MySQL, Oracle, SQLite, and other databases using third parties like CockroachDB, Firebird, and Microsoft SQL Server.

In this blog post, we are going to connect the MySQL database with Django from scratch. MySQL is one of the most popular open-source relational database management systems developed by Oracle Corporation in 1995.

Implementation

I assumed that you have already downloaded the MySQL database into your system. If you have not downloaded it yet, just download it from MySQL’s official website click here.

Note: I am using MySQL 8.0 Community Edition(CE) version and Django 3.0.3 version

Step 1: Setting up the Django project

First, create a virtual environment for your project and activate it in your working directory. I skip these processes as they lengthen the post.

Create a new Django project called my_project with the following command:

django-admin startproject my_project .

The period (.) at the end of my_project means that we are creating the project in the working directory.

If we do not give a period, then the project will be built in a new directory named my_project and inside that directory contains our actual Django files.

Now let’s run the project with the command:

python manage.py runserver

Then, go to your browser and visit the link http://127.0.0.1:8000. If you see Django welcome page, then you are good to go in the next steps.

Step 2: Create a new database in MySQL Workbench

For creating a database, first, we have to create a new MySQL Connection in the MySQL Workbench.

MySQL Workbench is a GUI tool for the MySQL database that provides features like SQL development, data modeling, and server administration and it is available in almost all operating systems(Windows, Mac, Linux).

For creating a connection first simply open MySQL Workbench CE in your system and do as follows:

Step 2.1: Create a new MySQL Connection

Here we create a new MySQL Connection named my_project_connection for our project.

Step 2.2: Create a database using SQL query

Open the created my_project_connection and in the query section create a new database named my_project as like:

After executing that query, refresh the schema that is present in the left bar of MySQL Workbench.

There you will see your databases if created and then select the database named my_project that we just created and inside that database you will notice different folders are created automatically like Tables, Views, StoredProcedures, and Functions. They all are empty now because we did not create any tables in the database yet.

Note: We can also create a database simply from the MySQL Command Line Client tool that is available when you installed MySQL. But this tool is not user-friendly.

Step 3: Update the settings.py file

After successfully creating the database for our project, we have to update the database section of the settings.py file with the following changes:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'my_project',
        'USER': 'root',
        'PASSWORD': '12345',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    }
}

Let’s discuss what we have done above:

We replaced ‘django.db.backends.sqlite3’ to ‘django.db.backends.mysql’ in ENGINE to point out MySQL server. In another way, we changed to ‘mysql‘ to tell Django that we are using MySQL database.
‘NAME’: ‘my_project’ is the name of our database that we created earlier in MySQL Workbench.
‘USER’: ‘root’ is the MySQL username that has access to the database and acts as a database administrator.
‘PASSWORD’: ‘12345’ is the password for the database. I keep this simple to remember for the long term. You can have your own strong password.
‘HOST’: ‘127.0.0.1’ and ‘PORT’: ‘3306’ simply point out that the MySQL database is hosted with the hostname ‘127.0.0.1‘ and it listens to the specific port number which is 3306.
SET sql_mode = ‘STRICT_TRANS_TABLES’ This command handles invalid or missing values from being stored in the database by INSERT or UPDATE statements. This is optional but saves us from lots of errors, and warnings occurring when we have a huge set of datasets.

Step 4: Install mysqlclient package

What is mysqlclient package?

mysqlclient package is the python interface to MySQL that allows python projects to connect to the MySQL server.

Up to this point, we have created a Django project and MySQL database. Now, we have to connect them using mysqlclient package which can be easily installed through pip. In your working directory run the command:

pip install mysqlclient

Step 5: Migrate the changes

This is the final step where we will be migrating the changes to a newly created database named my_project with the command:

python manage.py migrate

When we created the database for the first time there we saw empty tables.

But after, running this command Django automatically creates necessary tables like auth_group, auth_user, auth_permission, django_session, etc. in our my_project database as like:

Conclusion

In this blog post, we discussed different types of databases that Django officially supports and connect MySQL database with Django.

We knew how tables are created in the new database and learned to configure MySQL from the settings.py file and the different commands that it includes.

If you have any queries about this post, you can have them in the comment section below.

Reference

https://docs.djangoproject.com/en/3.0/ref/databases/

Happy Learning 🙂

Check out: