mysql5.0以上版本支持三种sql_mode：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

  • ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
  • TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
  • STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。
1、连接操作单数据库
  • django默认使用的是sqlite3数据库,settings.py
# 修改前
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}
  • 修改后
DATABASES = {
    'books': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST':'172.20.25.1',
        'PORT':'3306',
        'USER':'bmanager', 
        'PASSWORD':'MyNewPass0!',
        'NAME': 'books',   # 库名, 要连接的数据库，连接前需要创建好
        'OPTIONS': {
           'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
        },
    }
}

注意1：
我们需要的驱动是PyMySQL 所以，我们只需要找到app项目名文件下的__init__.py,在里面写入：

import pymysql
pymysql.install_as_MySQLdb()
  • 设置表结构，models.py
class Books_info(models.Model):
    id = models.AutoField(primary_key=True)
    createtime = models.DateTimeField('创建时间', auto_now = True)
    name = models.CharField(max_length=32)
    price = models.IntegerField()
    author = models.CharField(max_length=32)
    publishing_house = models.CharField(max_length=32)
    publication_time = models.IntegerField()
    num = models.IntegerField()

最后通过两条数据库迁移命令即可在指定的数据库中创建表

  • 生成数据库对应关系，生成指定appname的models.py里边的映射
python3 manage.py makemigrations  appname
  • 依据映射关系生成对应的表
python3 manage.py migrate   appname

注意2：
    确保配置文件中的INSTALLED_APPS中写入我们创建的app名称

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    "book"
]

注意3：
    如果报错如下：

django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.3 or newer is required; you have 0.7.11.None

MySQLclient目前只支持到python3.4，因此如果使用的更高版本的python，需要修改如下：

通过查找路径C:\Programs\Python\Python36-32\Lib\site-packages\Django-2.0-py3.6.egg\django\db\backends\mysql
这个路径里的文件把

if version < (1, 3, 3):
     raise ImproperlyConfigured("mysqlclient 1.3.3 or newer is required; you have %s" % Database.__version__)

注释掉 就OK了。

注意4：
    如果想打印orm转换过程中的sql，需要在settings中进行如下配置：

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}
2、连接操作多数据库
  • 添加新库，修改settings.py
DATABASES = {
    'books': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST':'172.20.25.1',
        'PORT':'3306',
        'USER':'bmanager', 
        'PASSWORD':'MyNewPass0!',
        'NAME': 'books',   # 库名
        'OPTIONS': {
           'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
        },
        
    'users': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST':'172.20.25.2',
        'PORT':'3306',
        'USER':'umanager', 
        'PASSWORD':'MyNewPass1!',
        'NAME': 'users',   # 库名
        'OPTIONS': {
           'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
        },

    }
}
  • 添加数据库的路由，修改settings.py
DATABASE_ROUTERS = ['mysite.database_router.DatabaseAppsRouter']  # mysite项目目录名

## app指定数据库
DATABASE_APPS_MAPPING = {
     # example:
     # 'app_name':'database_name',
       'booksModel':'books',
       'usersModel':'users',
}
  • 在settings.py同级目录下新建路由文件，database_router.py
from django.conf import settings

DATABASE_MAPPING = settings.DATABASE_APPS_MAPPING


class DatabaseAppsRouter(object):
    """
    A router to control all database operations on models for different
    databases.

    In case an app is not set in settings.DATABASE_APPS_MAPPING, the router
    will fallback to the `default` database.

    Settings example:

    DATABASE_APPS_MAPPING = {'app1': 'db1', 'app2': 'db2'}
    """

    def db_for_read(self, model, **hints):
        """"Point all read operations to the specific database."""
        if model._meta.app_label in DATABASE_MAPPING:
            return DATABASE_MAPPING[model._meta.app_label]
        return None

    def db_for_write(self, model, **hints):
        """Point all write operations to the specific database."""
        if model._meta.app_label in DATABASE_MAPPING:
            return DATABASE_MAPPING[model._meta.app_label]
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """Allow any relation between apps that use the same database."""
        db_obj1 = DATABASE_MAPPING.get(obj1._meta.app_label)
        db_obj2 = DATABASE_MAPPING.get(obj2._meta.app_label)
        if db_obj1 and db_obj2:
            if db_obj1 == db_obj2:
                return True
            else:
                return False
        return None

    def allow_syncdb(self, db, model):
        """Make sure that apps only appear in the related database."""

        if db in DATABASE_MAPPING.values():
            return DATABASE_MAPPING.get(model._meta.app_label) == db
        elif model._meta.app_label in DATABASE_MAPPING:
            return False
        return None

    def allow_migrate(self, db, app_label, model=None, **hints):
        """
        Make sure the auth app only appears in the 'auth_db'
        database.
        """
        if db in DATABASE_MAPPING.values():
            return DATABASE_MAPPING.get(app_label) == db
        elif app_label in DATABASE_MAPPING:
            return False
        return None
  • 情况一：远程数据库中表结构及数据已存在
# 拉取表结构
python3 manage.py inspectdb --database=users >> usersModel/models.py
python3 manage.py migrate usersModel --fake
  • 情况二：远程数据库表结构及数据不存在，则需要修改配置
  • 修改 usersModel/models.py 文件
class Users_info(models.Model):
    id = models.AutoField(primary_key=True)
    createtime = models.DateTimeField('创建时间', auto_now = True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    sex = models.CharField(max_length=10)
    identification_numbers = models.IntegerField()
    identity = models.CharField(max_length=32)
    phone_number = models.IntegerField()
    job = models.CharField(max_length=32)
    address = models.CharField(max_length=128)
  • 删除该app名字下的migrations文件
rm -fr migrations/*
rm __pycache__/*
  • 进入数据库，找到django_migrations的表，删除该app名字的所有记录。
truncate table  django_migrations where app='appname';
  • 创建新表
python3 manage.py makemigrations
python3 manage.py migrate
  • 指定数据库
class Users_info(models.Model):
    id = models.AutoField(primary_key=True)
    createtime = models.DateTimeField('创建时间', auto_now = True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    sex = models.CharField(max_length=10)
    identification_numbers = models.IntegerField()
    identity = models.CharField(max_length=32)
    phone_number = models.IntegerField()
    job = models.CharField(max_length=32)
    address = models.CharField(max_length=128)
    class Meta:
        app_label = 'users'