DEV Community

Cover image for How to use MySql with Django - For Beginners
Sm0ke
Sm0ke

Posted on • Originally published at docs.appseed.us

How to use MySql with Django - For Beginners

Hello Coders,

This article explains How to use MySql with Django and switch from the default SQLite database to a production-ready DBMS (MySql). This topic might sound like a trivial subject but during my support sessions, I got this question over and over especially from beginners.

For newcomers, Django is a leading Python web framework built by experts using a bateries-included concept. Being such a mature framework, Django provides an easy way to switch from the default SQLite database to other database engines like MySql, PostgreSQL, or Oracle. MySql is a powerful open-source relational database where the information is correlated and saved in one or more tables.

Thanks for reading! - Content provided by App Generator.


Django Database System

Django provides a generic way to access multiple database backends using a generic interface. In theory, Django empowers us to switch between DB Engines without updating the SQL code. The default SQLite database usually covers all requirements for small or demo projects but for production use, a more powerful database engine like MySql or PostgreSQL is recommended.

The database settings are saved in the file referred by manage.py file. In my Django projects, this file is saved inside the core directory:

< PROJECT ROOT >
   |
   |-- manage.py         # Specify the settings file 
   | 
   |-- core/             # Implements app logic 
   |    |-- settings.py  # Django app bootstrapper
   |    |-- wsgi.py      # Start the app in production
   |    |-- urls.py      # Define URLs served by all apps/nodes
Enter fullscreen mode Exit fullscreen mode

Let's visualize the contents of the settings.py file that configures the database interface.

# File: core/settings.py
...
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME'  : 'db.sqlite3',
    }
}
...
Enter fullscreen mode Exit fullscreen mode

The above snippet is provided by when Django scaffolds the project. We can see that the SQLite driver is specified by the ENGINE variable.


Update Django for MySql

To use MySql as the backend engine for a Django project, we need to follow a simple setup:

  • Install the MySql Server (we can also use a remote one)
  • Install the Mysql Python driver - used by Django to connect and communicate
  • Create the Mysql database and the user
  • Update settings Django
  • Execute the Django migration and create the project tables

Install MySql Server

The installation process is different on different systems but this phase should not be a blocking point because Unix systems provide by default a MySql server and for Windows, we can use a visual installer. For more information please access the download page and select the installer that matches your operating system:


Install the Python Driver

To successfully access the Mysql Engine, Django needs a driver (aka a connector) to translate the Python queries to pure SQL instructions.

$ pip install mysqlclient
Enter fullscreen mode Exit fullscreen mode

The above instruction will install the Python MySql driver globally in the system. Another way is to use a virtual environment that sandboxes the installation.

$ # Create and activate the virtual environment
$ virtualenv env
$ source env/bin/activate
$ 
$ # install the mysql driver
$ pip install mysqlclient
Enter fullscreen mode Exit fullscreen mode

Create the MySql Database

During the initial setup, Django creates the project tables but cannot create the database. To have a usable project we need the database credentials used later by the Django project. The database can be created visually using a database tool (like MySQL Workbench) or using the terminal:

CREATE DATABASE mytestdb;
Enter fullscreen mode Exit fullscreen mode

Create a new MySql user

CREATE USER 'test'@'localhost' IDENTIFIED BY 'Secret_1234';
Enter fullscreen mode Exit fullscreen mode

Grant all privileges to the newly created user

GRANT ALL PRIVILEGES ON `mytestdb` . * TO 'test'@'localhost';
FLUSH PRIVILEGES; 
Enter fullscreen mode Exit fullscreen mode

Update Django Settings

Once the MySql database is created we can move on and update the project settings to use a MySql server.

# File: core/settings.py
...
DATABASES = {
    'default': {
        'ENGINE'  : 'django.db.backends.mysql', # <-- UPDATED line 
        'NAME'    : 'mytestdb',                 # <-- UPDATED line 
        'USER'    : 'test',                     # <-- UPDATED line
        'PASSWORD': 'Secret_1234',              # <-- UPDATED line
        'HOST'    : 'localhost',                # <-- UPDATED line
        'PORT'    : '3306',
    }
}
...
Enter fullscreen mode Exit fullscreen mode

Start the project

The next step in our simple tutorial is to run the Django migration that will create all necessary tables.

$ # Create tables
$ python manage.py makemigrations
$ python manage.py migrate
Enter fullscreen mode Exit fullscreen mode

Start the Django project

$ # Start the application (development mode)
$ python manage.py runserver
Enter fullscreen mode Exit fullscreen mode

At this point, Django should be successfully connected to the Mysql Server and we can check the database and list the newly created tables during the database migration.

Django configured to use MySql - The default page.


Thanks for reading! For more resources feel free to access:

Discussion (3)

Collapse
socr102 profile image
Elina

Excellent!! your post is interesting
Can you post how to do the crud in the Django using the MySQL database?
It will be a piece of cake for you
Also do you know how to use the stripe in the Django?
It is about the payment
I am developing the Django-Ecommerece site
As well I have code for stripe
But I am not sure it works correctly
so I hope you help me with stripe part
thank you
best

Collapse
sm0ke profile image
Sm0ke Author

Hello @socr102 ,
Noted your suggestion. In the mean time you can use a free sample that covers in full a Django API project released on Github.
github.com/app-generator/api-serve...

Let me know if you find it useful.
🚀🚀

Collapse
socr102 profile image
Elina

Thank you for your help
but there is no answer I want
But it will help me in the future
best
Elina