Django2 SQLite3迁移到MySQL数据库

# Django2 SQLite3迁移到MySQL数据库 开发环境Windows+Django2.1+Python3.7，服务器环境Ubuntu16.04+Django2.1+Python3.6.6 ## Windows开发环境下 ### 创建mysql连接配置文件 该文件可保存在本机任何地方，如果项目要上传到github上或其他共享，mysql帐密不可能让别人知道呀 **ProjectConfig.ini** ```ini [mysql-djangostarmeow] host=xxx.xxx.xxx.xxx port=3306 user=sqlusername password=sqluserpassword db_name=djangostarmeow ``` ### 修改DATABASES配置 读取配置文件，设置mysql为备用数据库 ```python import configparser config = configparser.ConfigParser() config.read(r'C:\ProjectConfig.ini') DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), }, 'slave': { 'ENGINE': 'django.db.backends.mysql', 'NAME': config['mysql-djangostarmeow']['db_name'], 'USER': config['mysql-djangostarmeow']['user'], 'PASSWORD': config['mysql-djangostarmeow']['password'], 'HOST': config['mysql-djangostarmeow']['host'], 'PORT': config['mysql-djangostarmeow']['port'], } } ``` ### 安装mysqlclient Windows下需要安装mysql连接驱动。 项目运行后提示下面错误，需要安装`mysqlclient` > django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module. > Did you install mysqlclient? ``` pip install mysqlclient ``` 但Windows10安装提示 ``` error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": https://visualstudio.microsoft.com/downloads/ ``` #### whl文件下载安装（更好） 下载地址： https://www.lfd.uci.edu/~gohlke/pythonlibs/  下载和python对应的版本，再使用pip安装 ```python pip install D:\mysqlclient-1.3.13-cp37-cp37m-win_amd64.whl # 完成后安装mysqlclient pip install mysqlclient ``` 目前就可以正常启动项目了。 ### 数据导出导入 **执行makemigrations错误提示：**`django.db.utils.ProgrammingError: (1146, "Table 'djangostarmeow.django_content_type' doesn't exist")` 删除各个应用下`migrations`文件夹下00开头的文件，**仍然报错**，解决方法如下： #### 创建项目所需要的表 恢复`migrations`文件夹的文件到原目录  然后执行 ```python manage.py@StarMeow > migrate --database slave ``` 会将所有的migrations记录同步到`slave`数据库 确保每个APP的SQL过程语句生成，没有的话或者项目模型有改动，运行以下语句 ```python # 这些直接在pycharm上运行 manage.py@StarMeow > makemigrations manage.py@StarMeow > migrate manage.py@StarMeow > migrate --database slave ``` #### 导出sqlite3数据 ```python (StarMeow) E:\xxx>python manage.py dumpdata --database default > dumpdata20181024.json ``` 该命令的使用说明如下： ```python python manage.py loaddata -h usage: manage.py loaddata [-h] [--database DATABASE] [--app APP_LABEL] [--ignorenonexistent] [-e EXCLUDE] [--format FORMAT] [--version] [-v {0,1,2,3}] [--settings SETTINGS] [--pythonpath PYTHONPATH] [--traceback] [--no-color] fixture [fixture ...] Installs the named fixture(s) in the database. positional arguments: fixture Fixture labels. optional arguments: -h, --help show this help message and exit --database DATABASE Nominates a specific database to load fixtures into. Defaults to the "default" database. --app APP_LABEL Only look for fixtures in the specified app. --ignorenonexistent, -i Ignores entries in the serialized data for fields that do not currently exist on the model. -e EXCLUDE, --exclude EXCLUDE An app_label or app_label.ModelName to exclude. Can be used multiple times. --format FORMAT Format of serialized data when reading from stdin. --version show program's version number and exit -v {0,1,2,3}, --verbosity {0,1,2,3} Verbosity level; 0=minimal output, 1=normal output, 2=verbose output, 3=very verbose output --settings SETTINGS The Python path to a settings module, e.g. "myproject.settings.main". If this isn't provided, the DJANGO_SETTINGS_MODULE environment variable will be used. --pythonpath PYTHONPATH A directory to add to the Python path, e.g. "/home/djangoprojects/myproject". --traceback Raise on CommandError exceptions --no-color Don't colorize the command output. ``` #### 导入mysql数据 ```python (StarMeow) E:\xxx>python manage.py loaddata --database slave dumpdata20181024.json ``` 错误提示：`django.db.utils.IntegrityError: Problem installing fixture 'E:\Sync\OneDrive\PycharmProjects\StarMeow\dumpdata20181024.json': Could not load contenttypes.ContentType(pk=6): (1062, "Duplicate entry 'usercenter-userprofile' for key 'django_content_type_app_label_model_76bd3d3b_uniq'")` 这是因为`auth_permission`和`django_content_type`中存在数据，这些数据是在给MySQL数据库应用迁移文件的时候产生的，一般是**content_type**相关的表。 进入数据库，删除这两个表的数据 ```sql >mysql -h xxxx -P 3306 -u sqlusername -p Enter password: ****************** mysql> use djangostarmeow Database changed mysql> delete from auth_permission; Query OK, 147 rows affected (0.10 sec) mysql> delete from django_content_type; Query OK, 37 rows affected (0.01 sec) ``` 删除数据之后，再执行一次导入命令即可。基本上，导入不了数据都是MySQL存在数据导致的。 ```python manage.py@StarMeow > loaddata --database slave dumpdata20181024.json Tracking file by folder pattern: migrations Installed 475 object(s) from 1 fixture(s) ``` #### 修改默认数据库 修改默认使用mysql数据库 ```python DATABASES = { # 'default': { # 'ENGINE': 'django.db.backends.sqlite3', # 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), # }, 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': config['mysql-djangostarmeow']['db_name'], 'USER': config['mysql-djangostarmeow']['user'], 'PASSWORD': config['mysql-djangostarmeow']['password'], 'HOST': config['mysql-djangostarmeow']['host'], 'PORT': config['mysql-djangostarmeow']['port'], } } ``` ## 服务器Ubuntu安装pymysql ``` :~$ sudo apt-get install libmysqlclient-dev :~$ pip install mysqlclient ``` ### 服务器创建mysql文件 ```ini [mysql-djangostarmeow] host=xxx.xxx.xxx.xxx port=3306 user=sqlusername password=sqluserpassword db_name=djangostarmeow ``` ### 服务器加载mysql配置文件 ```python import configparser config = configparser.ConfigParser() if socket.gethostname() == 'my-svr' or socket.gethostname() == 'My-Svr': # 通过计算机名判断是否是服务器 config.read('/home/ubuntu/web/ProjectConfig.ini') # 服务器已部署环境 else: config.read(r'C:\ProjectConfig.ini') # 本机Windows环境 ```

明人不说暗话 领红包就是支持！！！ 来都来了，再到页面底部看下呗~ ↓ ↓ ↓

本文地址： http://blog.starmeow.cn/detail/a5bfc9e07964f8dddeb95fc584cd965d/

很赞哦！ (1)