[Django] Connect Django Application to a MySQL Server

Recently, I was struggling against connecting a Django project to a MySQL server. There are a few subtle settings which made it extremely difficult to find out what was the problem. Moreover, the explanation provided by Django official website is a bit scattered and it was a little bit hassle to gather together to check out my todo-list.

see:

• https://docs.djangoproject.com/en/4.0/intro/tutorial02/
• https://docs.djangoproject.com/en/4.0/topics/install/#database-installation
• https://docs.djangoproject.com/en/4.0/ref/settings/#std:setting-DATABASES

Yes, at least three of them explains almost the same topic with different contents.

Nonetheless, finally I made it according to the explanation given from those links. Then, I was wondering, whether the same method could be okay with a certain user, not the root user who tries to connect to the MySQL server.

The following shows that it also works for an arbitrary configured user. I strictly followed the instruction by the official Django site and MySQL dev site.

1)First of all, I assume you already have a Django project and your MySQL server is installed on your computer.

2)On the MySQL prompt, type in the following commands in turn. Of course, , , and are my choice and you're free to choose your own ones.

CREATE DATABASE test;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT INDEX, ALTER, REFERENCES, SELECT, INSERT, UPDATE, DELETE ON test.* TO 'username'@'localhost';

Yes. You need to have your own DB(test), and the user(username) from the beginning, even before Django connects to the MySQL server. Then, our user is granted several privileges for one's own role.

(edited)For testing, Django automatically generate a database for pure testing. For this purpose, we give additional permissions to our user, so that it can create and delete a database for testing.

GRANT CREATE, DROP, INDEX, ALTER, REFERENCES, SELECT, INSERT, UPDATE, DELETE ON test_test.* TO 'username'@'localhost';

You can see that there are two additional permissions: CREATE and DROP. DROP is for when a test is over so that the database is no longer required. test_<your_database_name> is the default naming by Django of temporary databases for testing. You can set your customized name in settings.py though.

3)Now go to your Django project. In the settings.py where the project setup is specified, find DATABASE dictionary and change it as below:

# config/settings.py

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

4)Finally, install mysqlclient that binds the Django project and the MySQL server.

Install: link

5)Now, test whether the connection is going well.
Type in the command in the root directory
on the terminal to check whether data migration to the MySQL server is well implemented:

python3 manage.py migrate

It seems working okay!

Commands Reference:

• https://dev.mysql.com/doc/refman/8.0/en/grant.html