python-django 配置多个数据库

一、配置数据库

       默认一个app只使用一个数据库，不同app使用不同数据库，并且需要配置 settings 文件和添加 database_router.py 文件。但是对一个app配置多个数据库不友好。

       配置 settings.py 文件

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'ly1_mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db0806',
        'USER': 'root',
        'PASSWORD': '123',
        'HOST': '127.0.0.1',
        'PORT': '3308',
        'OPTIONS': {
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    },
    'ly11_mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db0806',
        'USER': 'root',
        'PASSWORD': '123',
        'HOST': '127.0.0.1',
        'PORT': '3308',
        'OPTIONS': {
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    },
    'ly2_mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db0920',
        'USER': 'root',
        'PASSWORD': '123',
        'HOST': '127.0.0.1',
        'PORT': '3308',
        'OPTIONS': {
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    },
    'ly22_mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db0920',
        'USER': 'root',
        'PASSWORD': '123',
        'HOST': '127.0.0.1',
        'PORT': '3308',
        'OPTIONS': {
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    },
}

二、给应用配置数据库

1、单个应用配置单个数据库

（1）在 settings.py 中填写如下对应关系，将 app 对应到数据库

DATABASE_ROUTERS = [
    'djangotest1.database_router.DatabaseAppsRouter',
]

DATABASE_APPS_MAPPING = {
    # example:
    # 'app_name':'database_name',
    # 'admin': 'defualt',
    'app01': 'ly1_mysql',
    'app02': 'ly2_mysql',
}

（2）新建 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:
            print(DATABASE_MAPPING[model._meta.app_label])
            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

（3）编写 model 时在 meta 中添加  app_label 字段，指定为，不写默认为 当前应用名字

       app_label 可以随意指定一个名字，只要在 DATABASE_APPS_MAPPING 中配置就可以连接到不同的数据库，但是会影响迁移，因为每次迁移是对一个 app 进行的，会去找一个 app 目录下的 model，并且 app_label 是当前的 app（默认是）才会创建迁移，而随意指定的名字的 app 并不存在；如果不需要迁移，则可以随意指定名字。

       应当将 app_label 指定为当前应用名字，如果指定为其他应用的名字，则在操作数据库时会连接应用对应的数据库。如下为在 app01 中的 model 的配置：

from django.db import models

# Create your models here.
class Article(models.Model):
    id = models.AutoField(db_column='id', primary_key=True)
    article_name = models.CharField(max_length=100, db_column='article_name')
    author_id = models.CharField(max_length=4, db_column='author_id')

    class Meta:
        db_table = 'article'
        required_db_vendor = 'mysql'
        verbose_name = '文章'
        verbose_name_plural = verbose_name
        app_label = 'app01'

class Book(models.Model):
    id = models.AutoField(db_column='id', primary_key=True)
    name = models.CharField(max_length=191, db_column='name')
    author = models.CharField(max_length=191, db_column='author')
    publish_year = models.CharField(max_length=191, db_column='publish_year')
    created_at = models.DateTimeField(db_column='created_at')
    updated_at = models.DateTimeField(db_column='updated_at')
    status = models.IntegerField(db_column='status')

    class Meta:
        db_table = 'book'
        required_db_vendor = 'mysql'
        verbose_name = '书'
        verbose_name_plural = verbose_name
        app_label = 'app01'

（4）使用

from django.shortcuts import render, get_object_or_404
from .models import Article, Book
from django.http import HttpResponse

# Create your views here.
def home(request):
    return render(request, 'home.html', {})

def article_show(request):
    # res = Article.objects.using('ly_mysql').filter(id=1).first()   # 手动指定
    res = Article.objects.filter(id=1).first()
    print(res.id, res.article_name, res.author_id)
    return render(request, "article.html", dict())

def book_show(request):
    # res = Book.objects.using('ly2_mysql').filter(id=1).first()
    res = Book.objects.filter(id=1).first()
    print(res.id, res.name, res.author, res.publish_year)
    return render(request, "article.html", dict())

def hello(request):
    return HttpResponse("<p>HttpResponse HELLO！</p>")

2、单个应用配置多个数据库

       方法一：直接使用在方法链中使用 using('数据库')；

    res = Article.objects.using('ly_mysql').filter(id=1).first()   # 手动指定

       方法二：是给 app 中的模型指定不同的 app_label，并配置 DATABASE_APPS_MAPPING；

       方法三：在 model 中添加一个表示所连接的数据库的字段，改写上面的 DatabaseAppsRouter 中的方法，通过判断该字段返回数据库。

       改写 DatabaseAppsRouter 中的方法：

    def db_for_read(self, model, **hints):
        """"Point all read operations to the specific database."""
        if hasattr(model, 'db_connection'):
            return model.db_connection
        return None

    def db_for_write(self, model, **hints):
        """Point all write operations to the specific database."""
        if hasattr(model, 'db_connection'):
            return model.db_connection
        return None

       在 model 中添加 db_connection 字段

from django.db import models

# Create your models here.
class Article(models.Model):

    db_connection = 'ly11_mysql'

    id = models.AutoField(db_column='id', primary_key=True)
    article_name = models.CharField(max_length=100, db_column='article_name')
    author_id = models.CharField(max_length=4, db_column='author_id')

    class Meta:
        db_table = 'article'
        required_db_vendor = 'mysql'
        verbose_name = '文章'
        verbose_name_plural = verbose_name

class Book(models.Model):

    db_connection = 'ly22_mysql'

    id = models.AutoField(db_column='id', primary_key=True)
    name = models.CharField(max_length=191, db_column='name')
    author = models.CharField(max_length=191, db_column='author')
    publish_year = models.CharField(max_length=191, db_column='publish_year')
    created_at = models.DateTimeField(db_column='created_at')
    updated_at = models.DateTimeField(db_column='updated_at')
    status = models.IntegerField(db_column='status')

    class Meta:
        db_table = 'book'
        required_db_vendor = 'mysql'
        verbose_name = '书'
        verbose_name_plural = verbose_name