Introduction

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. Django officially supports different types of database in its buckets like PostgreSQL, MariaDB, MySQL, Oracle, SQLite and other databases using third parties like CockroachDB, Firebird, Microsoft SQL Server.

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

 

Implementation

I assumed that you have already downloaded MySQL database into your system. If you have not downloaded yet, just download it from MySQL 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 Django project

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

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

django-admin startproject my_project .

With the period (.) at the end of my_project means that we are creating the project in the working directory. If we do not give period, then the project will be built in 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.

 

Django Welcome Page

 

 

Step 2: Create a new database in MySQL Workbench

For creating database, first we have to create 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 availabe in almost all operating sytems(Windows, Mac, Linux). For creating connection first simply open MySQL Workbench CE in your system and do as follows:

 

Step 2.1: Create new MySQL Connection

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

Creating New MySQL Connection

 

Step 2.2: Create database using SQL query

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

Creating Database In MySQL Workbench

 

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 on 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 empy now because we did not create any tables in database yet.

 

Schema In MySQL Workbench

 

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

 

Step 3: Update settings.py file

After successfully creating database for our project, we have to update database section of  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 who have access to database and acts as database administrator. 

  •  'PASSWORD': '12345'  is the password for database. I keep this simple to remember for long term. You can have your own strong password.

  •  'HOST': '127.0.0.1'  and  'PORT': '3306'  is simply points out that MySQL database is hosted with the host name '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, warnings being occurring when we have 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.

Upto the point, we have created Django project and MySQL database. Now, we have to connect them using mysqlclient package which can be easily install 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 newly created database named my_project with the command:

python manage.py migrate

When we created database at 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:

Django Created Tables In MySQL Workbench

 

Conclusion

In this blog post, we discussed different types of database that Django officially supports and connect MySQL database with Django. We knew how tables are created in the new database and learned configuring MySQL from  settings.py  file and different commands that it includes. If you have any queries about this post, you can have in the commen section below.

 

Reference

Happy Learning :-)