[Django] Connect Django Application to a MySQL Server
Tags: django, mysql
Published: 2021-12-22 10:56:27

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, <username>, <localhost>, and <test> are my choice and you're free to choose your own ones.

```sql
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. 

```sql
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:

```py
# 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](https://pypi.org/project/mysqlclient/)

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:

```py
python3 manage.py migrate
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sfm2r6ihr4aoxdy0srpd.png)

It seems working okay!

Commands Reference:
- https://dev.mysql.com/doc/refman/8.0/en/grant.html