更多内容请点击 我的博客 查看,欢迎来访。
Django2 SQLite3迁移到MySQL数据库
开发环境Windows+Django2.1+Python3.7,服务器环境Ubuntu16.04+Django2.1+Python3.6.6
Windows开发环境下
创建mysql连接配置文件
该文件可保存在本机任何地方,如果项目要上传到github上或其他共享,mysql帐密不可能让别人知道呀
ProjectConfig.ini
[mysql-djangostarmeow]
host=xxx.xxx.xxx.xxx
port=3306
user=sqlusername
password=sqluserpassword
db_name=djangostarmeow
修改DATABASES配置
读取配置文件,设置mysql为备用数据库
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安装
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
文件夹的文件到原目录
然后执行
manage.py@StarMeow > migrate --database slave
会将所有的migrations记录同步到slave
数据库
确保每个APP的SQL过程语句生成,没有的话或者项目模型有改动,运行以下语句
# 这些直接在pycharm上运行
manage.py@StarMeow > makemigrations
manage.py@StarMeow > migrate
manage.py@StarMeow > migrate --database slave
导出sqlite3数据
(StarMeow) E:\xxx>python manage.py dumpdata --database default > dumpdata20181024.json
该命令的使用说明如下:
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数据
(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相关的表。
进入数据库,删除这两个表的数据
>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存在数据导致的。
manage.py@StarMeow > loaddata --database slave dumpdata20181024.json
Tracking file by folder pattern: migrations
Installed 475 object(s) from 1 fixture(s)
修改默认数据库
修改默认使用mysql数据库
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文件
[mysql-djangostarmeow]
host=xxx.xxx.xxx.xxx
port=3306
user=sqlusername
password=sqluserpassword
db_name=djangostarmeow
服务器加载mysql配置文件
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环境