Sept. 7, 2021, 7:35 p.m.

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

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. With my experience if your website has a 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 upto 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 upto 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 right manner.
Step 1 First of all make a zip file of your whole website folder for backup security purpose (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 manage.py dumpdata > datadump.json

Just go to the working directory (in the virtual environment) and run the above command. The execution may takes time depends upon database size.
Please try ,not to close console untill the final step.
Step 2 Now install mysqlclient by using following command. This is nothing but a connector for python with mysql.

pip install mysqlclient

Step 3 Now you have make some changes in settings.py file. Search for a dictionary named as "DATABASES" and replace it with the following code.

DATABASES = {
'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 manage.py migrate --run-syncdb

You can ignore the warnings that pop-ups in 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 uses different content types.
Exclude contentype data with this snippet in shell.

python manage.py shell
from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
quit()

Step 6 This is the final step. Everything now all set , the database ,settingse 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 manage.py loaddata datadump.json

Tips 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 error. Then replace the website data from our back-up file(that is why i recommended to make a backup zip file of whole website) and remove that app from INSTALLED_APPS (run migrate command if required).
Now you have to perform all the steps again.

Some recent posts

How to deploy a django website on pythonanywhere | django Web app deploying...

Complete process of changing database from SQLite to MySQL - How to migrat...

" How to download a file in django | Making a large file downloadable from ...

How to use proxy in python requests | http & https proxies - Scraping...

Top Django Querysets, How to filter records | Fetching data from database e...

How to change base url or domain in sitemap - Django ...

How to Make a Website - Everything you should know about web development...

What is Javascript - Features and usages ...

Top 5 Interview Questions : Tips for HR round ...

How to get job in IT : Perfect resume guide for IT job ...

View all...

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

Some recent posts

Popular Posts