Franklynose
Linking Django and MySQL

Linking Django and MySQL

Learn how to connect a MySQL database to your Django project seamlessly

Frank Alimimian's photo
Frank Alimimian
·

6 min read

Table of contents

Introduction

The database is a vital part of a web application. It holds important data that the application needs to perform actions like creating, reading, updating, and deleting information. When building an application, the database can either be relational or non-relational.

Relational databases are commonly used because they allow for structured data storage and retrieval. They provide a way to establish relationships between different pieces of data, making it easier to organize and manage information.

Django supports the use of both relational and nonrelational databases like PostgreSQL, MariaDB, MySQL, Oracle, and SQLite with a minimum level of configuration. Among these databases, MySQL and PostgreSQL are commonly used. In this article, we will be going over how to link the MySQL database with your Django project.

Prerequisite

Assumes prior installation of Django and basic familiarity with the Django web framework.

1. MySQL for Django projects

MySQL is a relational database that uses tables, columns, rows, and fields to store and retrieve data in a structured format. It uses SQL (Structure Query Languages) to perform its various operations. MySQL is quite simple to pick up, and it’s very interactive, which makes it a good choice for most programmers to use as a database. With Django, it really doesn’t matter which database you use since most database actions are carried out using ORM (Object-relational mapping). With the ORM, you can perform basic database actions such as creating, reading, updating, and deleting data using a queryset.

2. Setting up MySQL for Django

Setting up MySQL for Django requires the following steps:

  • Setting up MySQL on your local machine

  • Creating a database for your Django project

  • Creating a new user in your database for your Django project

  • Installing MySQL DB API driver (or connector)

  • Updating the database configuration setting in the Django settings.py files

A. Setting up MySQL on your local machine

Setting up MySQL is a simple process. To install MySQL on your local machine, follow the instructions provided on their official page. Make sure to review the system requirements and supported operating systems before selecting a package. Additionally, you have the option to include any additional community products that you require. Ensure that the password is kept secure.

B. Creating a database for your Django project

After installation, the next step is to create a database for your project, where all the data for your project will be stored and retrieved. To create a database for your Django project, follow these step-by-step instructions:

C. Creating a database for your Django project

1. Create a database: Once connected, create the database for your project using the following command:

CREATE DATABASE database_name;

Replace database_name with the desired name for your database.

2. Verify the database creation: To verify that the database was created successfully, apply the command:

SHOW DATABASES;

This will display a list of all databases, including the one you created.

D. Creating a new user in your database for your Django project

It is highly recommended to create a separate user specifically for your Django project. This user should be granted limited and specific permissions for accessing and managing the project's database. On the other hand, the root user has complete access and authority to perform all operations within the database. It is not advisable to use the root user for your project because if your credentials are compromised, a hacker could gain unrestricted access to your database and potentially carry out administrative tasks.

To create a user in a MySQL database, you can follow these steps:

1. Connect to MySQL: Open a terminal or command prompt and enter the following command:

mysql -u root -p;

You will be prompted to enter your password

2. Create the user. Once you're logged in, you can create a new user using the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replaceusername with the desired username for the new user and password with the desired password.

3. Grant privileges to the user: After creating the user, you need to grant appropriate privileges to perform specific actions. For example, to grant all privileges on a specific database to the user, you can use the following command:

GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';

Replacedatabase_name with the database name to which you want to grant access (the database you created earlier on).

4. Flush privileges: After granting privileges, run the following command to update the changes:

FLUSH PRIVILEGES;

That's it! You have successfully created a user in your MySQL database. The new user can now connect to the MySQL server using the provided username and password and perform the granted actions.

E. Install a database driver (connector)

A database driver plays a crucial role in mapping models and translating Python queries into MySQL instructions through the models. While there are numerous Python drivers available for MySQL, Django recommends using the MySQL API driver package called mysqlclient. This driver is responsible for establishing a connection between your Django project and the MySQL database server, enabling Django to execute SQL queries against the database. To install mysqlclient, follow these steps within your virtual environment:

1. Activate your virtual environment.

2. Open the terminal and execute the following command:

pip3 install mysqlclient

Note: Depending on your package installer, you can use either pip, pip3, or pipenv.

F. Updating the database configuration setting in the Django settings.py file

The final step is to update the database settings in the settings.py file of your Django project. Follow these steps:

2. Locate the DATABASES section within the file and modify it as follows:

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

Replace the placeholders 'database_name', 'username', and 'password' with the actual name of your database, the username you created earlier, and the corresponding password, respectively.

Run the migrations in the terminal to ensure everything is working properly. You can check the tables in your databases to ensure all the Django default tables have been migrated and created.

By updating the database configuration in the settings.py file, Django will be able to establish a connection with the MySQL database using the provided credentials, allowing your Django project to interact with the database seamlessly.

3. Conclusion

In conclusion, SQLite is suitable for small projects and prototyping purposes. However, for more robust and production-ready projects, MySQL is recommended. This article provides a comprehensive guide to connecting MySQL to your Django project. The instructions provided serve as a basic setup for personal projects. To ensure the security of your projects, it is advisable to protect your database credentials and other sensitive information by utilizing environment variables. I recommend exploring this approach to enhance the security of your projects.

Thank you for reading the article, and I hope you found it helpful. Consider subscribing to receive notifications for future articles on various topics related to Django and web development.

DatabasesPythonDjangobackend