Django Database Switch and Data Migration Solution

Introduction

I was so busy shopping during Double 11 that I barely wrote any articles. There are still a dozen unfinished draft articles in my notes…

Today I'll share a solution for switching databases and migrating data in a Django project. Articles found online vary in their methods, and you're likely to run into all kinds of errors when using them. This article is based on Django's official documentation and work experience—stable and reliable, and I'll keep it updated on the blog~

If you're facing the same problem, reading this article should give you a pretty good solution.

Basic Steps

Django uses SQLite by default for convenient development, and its ORM supports multiple databases—you just need to install the corresponding driver.

Switching databases typically means moving from SQLite in the development environment to MySQL (MariaDB) or PostgreSql. This article only tested switching from SQLite to MySQL / PostgreSQL; similarly, other switch paths should work as well.

The database table structure isn't really a problem—just use Django's migrate feature.

The key is data migration. You can use database tools like Navicat for data sync, but it often fails due to constraint relationships between tables (requiring data to be imported in a specific order).

So the best approach is to use Django's dumpdata feature to export the database as a json or xml file, then switch databases and import.

The steps are as follows:

• Export existing data: python manage.py dumpdata -o db.json
• Create an empty database in the target database (MySQL / PostgreSql)
• Switch to the new database in settings.py
• Set up the new database table structure python manage.py migrate
• Import existing data: python manage.py loaddata db.json

Done~

Here are a few database configurations for convenience

db_config = {
    'sqlite': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        'OPTIONS': {
            'timeout': 20,
        }
    },
    'pgsql': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '数据库名称',
        'USER': '用户名',
        'PASSWORD': '密码',
        'HOST': '数据库服务器地址',
        'PORT': 5432,
    },
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '数据库名称',
        'USER': '用户名',
        'PASSWORD': '密码',
        'HOST': '数据库服务器地址',
        'PORT': 3306,
    }
}
# 这里可以方便切换不同数据库
DATABASES = {'default': db_config['pgsql']}

Notes:

• MySQL requires installing the mysqlclient package
• PostgreSql requires installing the psycopg2 package

Then again, things often aren't that simple and smooth. You may run into some issues during export and import—please read on~

Export Errors

Error message

CommandError: Unable to serialize database: 'gbk' codec can't encode character '\u30fb' in position 4: illegal multibyte sequence

The cause is related to encoding

Solution

Exporting data with Python's UTF-8 mode will work fine

Use this command to export the file

(Do not export auth.permission and contenttypes; these two are auto-generated during migrate, which avoids conflicts when importing the original data)

python -Xutf8 manage.py dumpdata --exclude auth.permission --exclude contenttypes > db.json

Or

python -Xutf8 manage.py dumpdata -o db.json

Fixing Errors During Import

Error 1: Duplicate entry

Error message

django.db.utils.IntegrityError: Problem installing fixture 'db.json'  Could not load contenttypes.ContentType(pk=15): (1062, "Duplicate entry 'xxx' for key 'django_content_type.django_content_type_app_label_model_76bd3d3b_uniq'")

Solution 1: Re-export the data

Add these two parameters

• --natural-primary: Omits the primary key in the serialized data of this object since it can be calculated during deserialization.
• --natural-foreign: Uses the natural_key() model method to serialize any foreign key and many-to-many relationship to objects of the type that defines the method.

The effect is to strip some constraints on export; they will be handled automatically on import, reducing issues caused by constraint relationships between tables during import.

python3 manage.py dumpdata --natural-primary --natural-foreign -o db.json

Solution 2: Delete content_type data

Another approach: delete the initialization data produced by the migrate process to avoid conflicts with the original data on import.

First enter the python shell

python3 manage.py shell

Enter and run the following Python code

from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()

Error 2: Encoding error

Error message

UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xff in position 0: invalid start byte in Django

Solution 1: Use Python's UTF8 mode (recommended)

Add the -Xutf8 parameter before the import command

python -Xutf8 manage.py loaddata db.json

Solution 2: Hack Django's code

It works, but it's not recommended. Only try this method if you really have no other choice.

Modify the file

lib\site-packages\django\core\serializers\json.py

Find this line of code in the Deserializer method

stream_or_string = stream_or_string.decode()

Change it to this

stream_or_string = stream_or_string.decode('UTF-16')

Then proceed with the import

References

• https://docs.djangoproject.com/en/4.1/ref/django-admin/
• https://www.shubhamdipt.com/blog/django-transfer-data-from-sqlite-to-another-database/
• https://javaatpoint.com/solved-unicodedecodeerror-utf-8-codec-cant-decode-byte-0xff-in-position-0-invalid-start-byte/
• https://counter2015.com/2020/01/15/django-migration-sqlite-to-postgre/
• https://stackoverflow.com/questions/64457733/django-dumpdata-fails-on-special-characters