Django和只读数据库连接

Question

假设一个Django应用程序应该使用两个MySQL数据库：

• default-用于存储由模型A和B表示的数据（读写访问）
• support-用于导入由模型C和D表示的数据（只读访问）

support数据库是外部应用程序的一部分，不能修改。

由于Django应用程序对模型A和B使用内置的ORM，我认为它应该对模型C和D使用相同的ORM，即使它们映射到外部数据库中的表（support）

为了实现这一点，我定义了模型C和D，如下所示：

from django.db import models


class ExternalModel(models.Model):
    class Meta:
        managed = False
        abstract = True


class ModelC(ExternalModel):
    some_field = models.TextField(db_column='some_field')

    class Meta(ExternalModel.Meta):
        db_table = 'some_table_c'


class ModelD(ExternalModel):
    some_other_field = models.TextField(db_column='some_other_field')

    class Meta(ExternalModel.Meta):
        db_table = 'some_table_d'

然后我定义了一个数据库路由器：

from myapp.myapp.models import ExternalModel


class DatabaseRouter(object):
    def db_for_read(self, model, **hints):
        if issubclass(model, ExternalModel):
            return 'support'

        return 'default'

    def db_for_write(self, model, **hints):
        if issubclass(model, ExternalModel):
            return None

        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        return (isinstance(obj1, ExternalModel) == isinstance(obj2, ExternalModel))

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return (db == 'default')

最后调整settings.py：

# (...)

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': os.path.join(BASE_DIR, 'resources', 'default.cnf'),
        },
    },
    'support': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': os.path.join(BASE_DIR, 'resources', 'support.cnf'),
        },
    },
}

DATABASE_ROUTERS = ['myapp.database_router.DatabaseRouter']

# (...)

在support.conf中为support数据库指定的用户已被分配只读权限。

但是当我运行python manage.py makemigrations时，它失败了，输出如下：

    Traceback (most recent call last):
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 112, in execute
    return self.cursor.execute(query, args)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 217, in execute
    res = self._query(query)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 378, in _query
    rowcount = self._do_query(q)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 341, in _do_query
    db.query(q)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1142, "CREATE command denied to user 'somedbuser'@'somehost' for table 'django_migrations'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/migrations/recorder.py", line 57, in ensure_schema
    editor.create_model(self.Migration)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 295, in create_model
    self.execute(sql, params or None)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 112, in execute
    cursor.execute(sql, params)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 112, in execute
    return self.cursor.execute(query, args)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 217, in execute
    res = self._query(query)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 378, in _query
    rowcount = self._do_query(q)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 341, in _do_query
    db.query(q)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1142, "CREATE command denied to user 'somedbuser'@'somehost' for table 'django_migrations'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line
    utility.execute()
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/core/management/__init__.py", line 359, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/core/management/base.py", line 305, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/core/management/base.py", line 356, in execute
    output = self.handle(*args, **options)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/core/management/commands/makemigrations.py", line 100, in handle
    loader.check_consistent_history(connection)
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/migrations/loader.py", line 276, in check_consistent_history
    applied = recorder.applied_migrations()
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/migrations/recorder.py", line 65, in applied_migrations
    self.ensure_schema()
  File "/Users/username/Development/stuff/myapp/lib/python3.5/site-packages/django/db/migrations/recorder.py", line 59, in ensure_schema
    raise MigrationSchemaMissing("Unable to create the django_migrations table (%s)" % exc)
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table ((1142, "CREATE command denied to user 'somedbuser'@'somehost' for table 'django_migrations'"))

看起来Django试图在只读数据库中创建django_migrations表。

有什么干净的方法可以防止迁移机制尝试这样做吗？或者我必须使用另一个ORM库来对support数据库进行只读访问吗？

Answer 1

似乎在Django 1.10.1的时间范围内，Tim Graham（Django的主要维护者）接受了一个抑制此特定异常的补丁，但后来撤回了该补丁，转而（大致）使用以下方法来解决此问题并支持使用Django ORM的只读数据库。

1. 定义一个数据库路由器，如Django documentation on routers中所述，我在下面附加了一个示例路由器，它将路由到 基于模型元中的“app”标志的不同数据库。

2. 在路由器的allow_migrations方法中，对任何db参数返回False 对应于只读数据库的。这样可以防止 模型表，不管它们将被路由到哪里。

3. 下一部分有点奇怪，但是橡胶碰到路的地方 实际上回答了最初的问题。保持 试图在只读中创建django_migrations表 数据库，不应路由数据库通信。在这个例子中 路由器，这意味着“只读”在数据库应用程序映射中是而不是。

4. 因此，只读数据库是用“using”显式访问的（例如MyReadOnlyModel.objects.using（'Read\u only'）.all（）

Django database apps router

Answer 2

我遇到了同样的问题（使用Django 1.11），这个问题在我的Google搜索结果中占据了首位。

你最初的解决方案只遗漏了一个关键部分。您需要告诉Django'C'和'D'正在使用什么数据库模型。对我有用的是：

class ExternalModel(models.Model):
    class Meta:
        managed = False
        abstract = True    
        app_label = 'support'

然后告诉您的数据库路由器在allow_migrate（）部分中遇到应用程序标签时的行为：

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label == 'support':
            return False
        return (db == 'default')

我不确定这是Django团队眼中最正确的解决方案，但其效果是，对于使用app_label属性值定义的任何模型，allow_migrate（）都返回False。

Djangodocumentation on routers没有显式地提到这一点（或者，至少在模型代码示例中，可以清楚地说明ORM如何传递'db'的值以允许'u migrate（）），但是在'app_label'和'managed'属性之间，您可以让它工作*。

*在我的例子中，默认值是postgres，只读数据库是通过cx_Oracle的Oracle 12。

Answer 3

也有同样的问题。 Django正在尝试在所有DBs中创建“Django_migrations”表。 即使没有与只读数据库关联的模型，也会发生这种情况 所有路由器都指向不同的数据库。

我也用了peewee。