Django数据迁移

需求：将数据由Sqlite3迁移到MySQL。

本以为dumpdata后loaddata就搞定了，但是发现too simple!

---

Django 数据导入 结论：

如果你运气好的话可能会导入完成，但是往往不那么顺利，原因如下：

a) 我们在写models的时候如果用到CharField,就一定要写max_length,在sqlite3中是不检查这个最大长度的，你写最大允许长度为100，你往数据库放10000个，sqlite3都不报错，而且不截断数据的长度，这似乎是slite3的优点，但是也给从sqlite3导入其它数据库带来了困难,因为MySQL和PostgreSQL数据库都会检查最大长度，超出时就报错！

b) Django 自带的contentType会导致出现一些问题

用上面的方法只迁移一个app应该问题不大，但是如果有用户，用户组挂钩，事情往往变得糟糕！如果导入后没有对数据进行修改，你可以考虑重新导入，可能还要快一些，如果是手动在后台输入或者修改过，这种方法就不适用了

---

遇到问题：

结果python manage.py dumpdata -> data.json导出的数据，
在python manage.py loaddata data.json后报错：

Traceback (most recent call last):
  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\db\backends\
utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\db\backends\
sqlite3\base.py", line 328, in execute
    return Database.Cursor.execute(self, query, params)

sqlite3.IntegrityError: UNIQUE constraint failed: django_content_type.app_label,
 django_content_type.model

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\core\managem
ent\__init__.py", line 363, in execute_from_command_line
    utility.execute()

  ......

  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\db\backends\
sqlite3\base.py", line 328, in execute
    return Database.Cursor.execute(self, query, params)

django.db.utils.IntegrityError: Problem installing fixture 'E:\workSpace\python\
horoscope_web\data.json': Could not load contenttypes.ContentType(pk=24): UNIQUE
 constraint failed: django_content_type.app_label, django_content_type.model

Google后，帖子 Django fixture dumpdata, loaddata and Integrity Error 建议导出时加上选项：

python manage.py dumpdata --exclude=contenttypes --exclude=auth.Permission > initial_data.json

再次导入时，提示：

  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\db\backends\
utils.py", line 65, in execute
    return self.cursor.execute(sql, params)

sqlite3.IntegrityError: UNIQUE constraint failed: auth_user.username

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\core\managem
ent\__init__.py", line 363, in execute_from_command_line
    utility.execute()

  ......

  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\db\backends\
sqlite3\base.py", line 328, in execute
    return Database.Cursor.execute(self, query, params)

django.db.utils.IntegrityError: Problem installing fixture 'E:\workSpace\python\
horoscope_web\initial_data.json': Could not load auth.User(pk=4): UNIQUE constra
int failed: auth_user.username

删除"model": "auth.user"后再次loaddata，提示：

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "D:\Program Files\Python\Python35.3\lib\site-packages\django\core\managem
ent\__init__.py", line 363, in execute_from_command_line
    utility.execute()
  
  ......

    bad_row[1], referenced_table_name, referenced_column_name,
django.db.utils.IntegrityError: Problem installing fixtures: The row in table 'd
jango_admin_log' with primary key '3' has an invalid foreign key: django_admin_log.content_type_id contains a value '30' that does not have a corresponding value in django_content_type.id.

在 django_admin_log表中找不到主键为3的记录。
删除json中"model": "admin.logentry"的数据后，重试后成功导入。

记录例子：

{
    "model": "admin.logentry",
    "pk": 3,
    "fields": {
      "action_time": "2017-06-19T03:05:25.793Z",
      "user": 4,
      "content_type": 30,
      "object_id": "1",
      "object_repr": "Andrew-袁良锋",
      "action_flag": 2,
      "change_message": "[]"
    }
  },
  {
    "model": "admin.logentry",
    "pk": 4,
    "fields": {
      "action_time": "2017-06-19T03:05:43.471Z",
      "user": 4,
      "content_type": 30,
      "object_id": "1",
      "object_repr": "Andrew-袁良锋",
      "action_flag": 2,
      "change_message": "[{\"changed\": {\"fields\": [\"fortunes_keyword\"]}}]"
    }
  }

---

如果不想手动更改json数据(如删除"model": "admin.logentry"数据)，可使用分应用导出/导入：

导出app数据 python manage.py dumpdata {appname}>
导入app数据 python manage.py loaddata {appname}>

这样就只导入了应用数据，避免导入容易产生异常报错而无法正常的公共数据(如 user, auth等)。

---

导出用户数据：

python manage.py dumpdata auth > auth.json # 导出用户数据

多个数据库路由例子

---

dumpdata官方文档

databases官方文档

The contenttypes framework官方文档

Django fixture dumpdata, loaddata and Integrity Error

Migrating Django projects with fixtures

Django结构迁移