The complete process of changing database from SQLite to MySQL - How to migrate a Django DB from SQLite to MySQL

One of the main benefits to use Django for web development is that it comes with an inbuilt database/filesystem i.e. SQLite. Managing databases and files is super easy in Django. But there are some drawbacks to using SQLite. In my experience, if your website has minimum traffic of about 100-200 per day OR you have 1000-2000 files (or more obviously) in your database, then the problems start rising. SQLite gives amazing performance if your data is up to 2GB (can vary with your file types & size). Thus, you can not handle large traffic efficiently by using SQLite.

Here are some advantages & disadvantages of using SQLite :
Advantages :
1) Sqlite is Lightweight.
2) No Installation Needed.
3) Portable & Reliable

Disadvantages :
1) SQLite is used to handle low to medium traffic.
2) Database size is restricted up to 2GB (in most cases).

Today I will show you how you can migrate db from SQLite to MySQL in 6 simple steps. Just bare with me & i will provide the easiest way to do stuff in the right manner.


Step 1

First of all make a zip file of your whole website folder for backup security purposes (highly recommended).
Now, we have to dump all data from SQLite in a JSON file. For this purpose, we have to use the following command.

python dumpdata > datadump.json

Just go to the working directory (in the virtual environment) and run the above command. The execution may take time depending on database size.
Please try, not to close the console until the final step.

Step 2

Now install mysqlclient by using the following command. This is nothing but a connector for Python with mysql.

pip install mysqlclient

Step 3

Now you have to make some changes in the file. Search for a dictionary named "DATABASES" and replace it with the following code.

'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'Database_name',
'USER': 'Username',
'PASSWORD': 'db_password',
'HOST': 'mysql_host_name',
'PORT': '3306',
'OPTION': {'init_command': 'set storage_engine=InnoDB; \
set session transaction isolation level read committed'},

Make sure you can connect on your MySQL DB (permissions, etc) i.e. check the details carefully before proceeding.
Note that the OPTION may have more parameters. You can check the Django documentation for more values, but if you keep it as it is above there will be no issue.

Step 4

Run the following command to make synchronization among the database tables and the model settings.

python migrate --run-syncdb

You can ignore the warnings that pop-ups in the console (if any)

Step 5

This is the main step, you have to remove old content types to save data in MySQL fields which may use different content types.
Exclude contentypes data with this snippet in the shell.

python shell
from django.contrib.contenttypes.models import ContentType

Step 6

This is the final step. Everything is now all set, the database, settings, etc. Now we have to write or store the data from the json file which we saved in our very first step. For this purpose run the following command :

python loaddata datadump.json


If you face any problem for example if you have any app like sorl-thumbnail or easy-thumbnail in your INSTALLED_APPS, sometimes a file(thumbnail) is not present in media files which makes the last step incomplete. The point is if you face any issue just read the error location and the app/module which is causing the error. Then replace the website data from our backup file(that is why I recommended making a backup zip file of the whole website) and remove that app from INSTALLED_APPS (run migrate command if required).
Now you have to perform all the steps again.

Recent Articles

Changing Database from SQLite to MySQL - Django

One of the main benefit to use django for web development is that it comes with an inbuilt database/filesystem i.e SQLITE. Managing database and files is super easy in django. But there are some drawbacks of using SQLITE...

  - 2023-07-12
How to download a file in django

Sometimes we have to serve a file which may be a large file from our project directory. Suppose you want to transfer a file from your project directory to your friend or to some other person/server/platform....

  - 2023-07-12
How to change base url or domain in sitemap - Django

A sitemap is the first important SEO step which elaborates a website's content to search engines. It provides information to search engines about the available content on our site, which helps them to crawl webpages for indexing....

  - 2023-07-12
How to Make a Website

After reading this article you will never ever search for web development guide neither on Google nor on YouTube....

  - 2023-07-12
What is Javascript - Features and usages

I'm kind of a person who always likes to get started by installing the things & writing some code. I love that but this time JavaScript requires a little bit of the theoretical basic so that we can understand what things we are learning, how hard and powerful it is and where they can be applied....

  - 2023-07-12
Top 5 Interview Questions : Tips for HR round

In this article we are talking about the most famous HR questions and the mind-set to answer those HR questions....

  - 2023-07-12
How to get job in IT - Perfect resume guide

In today's article I want to talk about how to prepare the best resume to get a job in IT. Yes, everybody wants to have a job and in order to get a job you have to apply for it and when you apply for that, the resume is one good thing that you always submit....

  - 2023-07-12