很多网站有多数据库联用的文章，如自强学堂http://code.ziqiangxuetang.com/django/django-multi-database.html
大都只讲解如何让不同的app对应不同的数据库，而没有谈到如何让同一个app里的不同class对应不同的数据库。
经过N多次试验，历经好几天时间，终于找出如下的简便易行的途径，而不需要自己造轮子，现总结如下：
方式一：通过数据库路由自动分发Model，无需手动using
settings配置如下：
1、增加了DATABASE_ROUTERS，用于指定路由路径
2、增加了DATABASE_APPS_MAPPING，指定app_label对应的数据库
DBNAME = 'multi_test'
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
},
'db1': {
'ENGINE': 'django.db.backends.mysql',
'NAME': DBNAME,
'USER': 'root',
'PASSWORD': 'xxx',
'HOST': '127.0.0.1',
'PORT': 3306,
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
},
},
'db2': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': DBNAME,
'USER': 'root',
'PASSWORD': 'xxx',
'HOST': '127.0.0.1',
'PORT': 5432,
},
'db3': {
'ENGINE': 'django.db.backends.dummy',
},
}
connect(DBNAME) #connect to mongodb
SUB_DIR = os.path.basename(os.path.dirname(__file__))
DATABASE_ROUTERS = [
'{}.database_router.DatabaseAppsRouter'.format(SUB_DIR)
]
DATABASE_APPS_MAPPING = {
# example:
#'app_label':'database_name',
'mysql': 'db1',
'postgres': 'db2',
}
# 'mysql', 'postgres'需加到INSTALLED_APPS中，它们是通过startapp创建的两个空app
在settings同目录下增加database_router.py 文件，内容如下：
（直接从自强学堂拷贝，只注释掉print语句，无需做其他更改）
# -*- coding: utf-8 -*-
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
# for Django 1.4 - Django 1.6
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
# Django 1.7 - Django 1.11
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure that apps only appear in the related database.
根据app_label的值只在相应的数据库中创建一个表，如果删除该def或
不指定过滤条件，则一个Model会在每个数据库里都创建一个表。
"""
if db in DATABASE_MAPPING.values():
return DATABASE_MAPPING.get(app_label) == db
elif app_label in DATABASE_MAPPING:
return False
return None
models里在class Meta中指定app_label就可以指定它要使用的数据库
例如：settings的DATABASE_APPS_MAPPING中指定app1对应db1、app2对应db2，
那么app1中的Model默认使用db1，而当指定Meta中的app_label为app2时，则改用db2。
注：app_label指定的值须包含在settings的INSTALLED_APPS中，否则makemigrations不会自动创建表。
app1/models.py内容如下：
#coding=utf-8
from django.db import models
from django.utils.encoding import python_2_unicode_compatible
@python_2_unicode_compatible
class Animal(models.Model):
name = models.CharField(max_length=20)
def __str__(self):
return self.name
class Meta:
# 通过app_label来指定要使用的数据库
# 需指定db_table，否则该class的表名会是mysql_animal
# 如果不指定Meta的app_label，会使用默认数据库
app_label = 'mysql'
db_table = 'app1_animal'
@python_2_unicode_compatible
class Plant(models.Model):
name = models.CharField(max_length=20)
def __str__(self):
return self.name
class Meta:
app_label = 'postgres'
db_table = 'app1_plant'
app2/models.py
#coding=utf-8
from django.db import models
from django.utils.encoding import python_2_unicode_compatible
@python_2_unicode_compatible
class Fruit(models.Model):
name = models.CharField(max_length=20)
def __str__(self):
return self.name
class Meta:
app_label = 'mysql' #使用mysql数据库
db_table = 'app2_fruit' #指定表名为app2_fruit
@python_2_unicode_compatible
class Nut(models.Model):
name = models.CharField(max_length=20)
def __str__(self):
return self.name
class Meta:
app_label = 'postgres' #使用postgres数据库
db_table = 'app2_nut' #指定表名为app2_fruit
app1/test.py
from django.test import TestCase
from app1.models import Animal, Plant
from app2.models import Fruit, Nut
class ModelsTestCase(TestCase):
# db1 -- mysql
# db2 -- postgres
def setUp(self):
Animal.objects.create(name='db1')
Plant.objects.create(name='db2')
Fruit.objects.create(name='mysql_fruit')
Nut.objects.create(name='post_nut')
def test_methods_auto_choose_db(self):
self.assertEqual(Animal.objects.all()[0],
Animal.objects.using('db1').all()[0])
self.assertEqual(Plant.objects.all()[0],
Plant.objects.using('db2').all()[0])
self.assertEqual(Fruit.objects.all()[0],
Fruit.objects.using('db1').all()[0])
self.assertEqual(Nut.objects.all()[0],
Nut.objects.using('db2').all()[0])
self.assertNotEqual(Animal.objects.all()[0],
Fruit.objects.using('db1').all()[0])
self.assertNotEqual(Plant.objects.all()[0],
Nut.objects.using('db2').all()[0])
self.assertNotEqual(Fruit.objects.all()[0],
Plant.objects.using('db2').all()[0])
然后在命令行中运行
python manage.py makemigrations
python manage.py migrate --database=db1
python manage.py migrate --database=db2
python manage.py migrate
python manage.py test
完整代码放在coding上：
git clone https://git.coding.net/zhengwenjie/multi_dbs.git
cd multi_dbs
virtualenv venv
source venv/bin/activate
pip install -r requirements.txt
vi multi_dbs/settings.py #修改数据库名和密码等
alias mg='python manage.py'
mg makemigrations
mg migrate --database=db1
mg migrate --database=db2
mg migrate
mg test
方式二：通过using手动指定数据库
例如settings中的数据库配置如下：
# Database
# https://docs.djangoproject.com/en/1.11/ref/settings/#databases
import pymysql
pymysql.install_as_MySQLdb()
from mongoengine import connect
DBNAME = 'multi_test'
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
},
'mysql': {
'ENGINE': 'django.db.backends.mysql',
'NAME': DBNAME,
'USER': 'root',
'PASSWORD': 'xxx',
'HOST': '127.0.0.1',
'PORT': 3306,
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
},
},
'postgres': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': DBNAME,
'USER': 'root',
'PASSWORD': 'xxx',
'HOST': '127.0.0.1',
'PORT': 5432,
},
'mongo': {
'ENGINE': 'django.db.backends.dummy',
},
}
则查询和创建数据语句如下（不加using为采用default数据库）
from app1.models import House
House.objects.using('mysql').create(name='Tom')
House.objects.using('mysql').get(name='Tom')
House.objects.using('postgres').get_or_create(name='Jim')
网友评论