Some projects may involve the use of multiple databases in a simple way.
1. Set the DATABASE in settings.
For example, to use two databases:
DATABASES = {
'default': {
'NAME': 'app_data',
'ENGINE': '',
'USER': 'postgres_user',
'PASSWORD': 's3krit'
},
'users': {
'NAME': 'user_data',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'priv4te'
}
}
This identifies two databases, one with the alias default and one with the alias user. the aliases for the databases can be arbitrary.
The default alias is more specific; a Model uses the default database by default when it is not specifically selected in the route.
And of course, default can be set to empty:
DATABASES = {
'default': {},
'users': {
'NAME': 'user_data',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'superS3cret'
},
'customers': {
'NAME': 'customer_data',
'ENGINE': '',
'USER': 'mysql_cust',
'PASSWORD': 'veryPriv@ate'
}
}
In this way, since there is no more default database, it is necessary to make good database routing choices for all Models, including those in the third-party libraries used.
2. Provide app_label for Models that need to make database selections.
class MyUser():
...
class Meta:
app_label = 'users'
3. Write Database Routers
The Database Router is used to determine which database a Model uses, and defines the following four main methods:
db_for_read(model, **hints)
Specifies which database the mod uses to read from.
db_for_write(model, **hints)
Specifies which database the mod uses to write to.
allow_relation(obj1, obj2, **hints)
Determine whether the association between obj1 and obj2 can be generated, mainly used for foreign key and many to many operations.
allow_migrate(db, app_label, model_name=None, **hints)
Determines whether the migrate operation can be run on a database aliased to db.
A complete example:
Database Settings:
DATABASES = {
'default': {},
'auth_db': {
'NAME': 'auth_db',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'swordfish',
},
'primary': {
'NAME': 'primary',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'spam',
},
'replica1': {
'NAME': 'replica1',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'eggs',
},
'replica2': {
'NAME': 'replica2',
'ENGINE': '',
'USER': 'mysql_user',
'PASSWORD': 'bacon',
},
}
If the following effect is desired:
Models with app_label as auth have reads and writes done in auth_db, the rest of the Models have writes done in primary and reads done randomly in replica1 and replica2.
auth：
class AuthRouter(object):
"""
A router to control all database operations on models in the
auth application.
"""
def db_for_read(self, model, **hints):
"""
Attempts to read auth models go to auth_db.
"""
if model._meta.app_label == 'auth':
return 'auth_db'
return None
def db_for_write(self, model, **hints):
"""
Attempts to write auth models go to auth_db.
"""
if model._meta.app_label == 'auth':
return 'auth_db'
return None
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if a model in the auth app is involved.
"""
if obj1._meta.app_label == 'auth' or \
obj2._meta.app_label == 'auth':
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure the auth app only appears in the 'auth_db'
database.
"""
if app_label == 'auth':
return db == 'auth_db'
return None
This way the read and write of the Model with app_label as auth is done in auth_db, allowing for correlation, and migrate can be run only in the auth_db database.
The rest:
import random
class PrimaryReplicaRouter(object):
def db_for_read(self, model, **hints):
"""
Reads go to a randomly-chosen replica.
"""
return (['replica1', 'replica2'])
def db_for_write(self, model, **hints):
"""
Writes always go to primary.
"""
return 'primary'
def allow_relation(self, obj1, obj2, **hints):
"""
Relations between objects are allowed if both objects are
in the primary/replica pool.
"""
db_list = ('primary', 'replica1', 'replica2')
if obj1._state.db in db_list and obj2._state.db in db_list:
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
All non-auth models end up in this pool.
"""
return True
This way reads are done in random in replica1 and replica2 and writes are done using primary.
Finally set it in settings:
DATABASE_ROUTERS = ['', '']
That's all.
When performing a migrate operation:
$ ./ migrate $ ./ migrate --database=users
The migrate operation operates on the default database by default. To operate on other databases, use the --database option followed by the alias of the database.
Accordingly, the dbshell, dumpdata, and loaddata commands all have the --database option.
Routing can also be done manually:
Query:
>>> # This will run on the 'default' database.
>>> ()
>>> # So will this.
>>> ('default').all()
>>> # This will run on the 'other' database.
>>> ('other').all()
Save:
>>> my_object.save(using='legacy_users')
Moving:
>>> p = Person(name='Fred') >>> (using='first') # (statement 1) >>> (using='second') # (statement 2)
The above code creates a problem, when p is saved for the first time in the FIRST database, a primary key is generated by default, so that when it is saved using the SECOND database, p already has a primary key, which will not create a problem if it is not used, but will overwrite the original data if it was previously used.
There are two solutions; the
1. Clear the primary key before saving:
>>> p = Person(name='Fred') >>> (using='first') >>> = None # Clear the primary key. >>> (using='second') # Write a completely new object.
2. Using force_insert
>>> p = Person(name='Fred') >>> (using='first') >>> (using='second', force_insert=True)
Delete:
The database from which the object was obtained and from which it was deleted.
>>> u = ('legacy_users').get(username='fred')
>>> () # will delete from the `legacy_users` database
If you want to move an object from the legacy_users database to the new_users database:
>>> user_obj.save(using='new_users') >>> user_obj.delete(using='legacy_users')
This is the whole content of this article.