Django ManyToMany通过多个数据库 [英] Django ManyToMany through with multiple databases

问题描述

TLTR::Django不在SQL查询中包括数据库名称，我可以以某种方式强迫它这样做还是有解决方法?

TLTR: Django does not include database names in SQL queries, can I somehow force it to do this or is there a workaround?

长版:

我有两个旧版 MySQL 数据库(注意:我对数据库布局没有影响)，我为此创建一个只读API >在Django 1.11和python 3.6上使用DRF

I have two legacy MySQL databases (Note: I have no influence on the DB layout) for which I'm creating a readonly API using DRF on Django 1.11 and python 3.6

我正在使用以下建议的SpanningForeignKey字段来解决MyISAM数据库的参照完整性限制: https://stackoverflow.com /a/32078727/7933618

I'm working around the referential integrity limitation of MyISAM DBs by using the SpanningForeignKey field suggested here: https://stackoverflow.com/a/32078727/7933618

我正在尝试通过DB1上的表通过ManyToMany将DB1中的表连接到DB2中的表.那就是Django正在创建的查询:

I'm trying to connect a table from DB1 to a table from DB2 via a ManyToMany through table on DB1. That's the query Django is creating:

SELECT "table_b"."id" FROM "table_b" INNER JOIN "throughtable" ON ("table_b"."id" = "throughtable"."b_id") WHERE "throughtable"."b_id" = 12345

这当然会给我一个错误表'DB2.throughtable'不存在"，因为穿透表位于DB1上，而且我不知道如何强制Django为表加上数据库名称作为前缀.查询应为:

Which of course gives me an Error "Table 'DB2.throughtable' doesn't exist" because throughtable is on DB1 and I have no idea how to force Django to prefix the tables with the DB name. The query should be:

SELECT table_b.id FROM DB2.table_b INNER JOIN DB1.throughtable ON (table_b.id = throughtable.b_id) WHERE throughtable.b_id = 12345

app1 db1_app/models.py的模型:(DB1)

Models for app1 db1_app/models.py: (DB1)

class TableA(models.Model):
    id = models.AutoField(primary_key=True)
    # some other fields
    relations = models.ManyToManyField(TableB, through='Throughtable')

class Throughtable(models.Model):
    id = models.AutoField(primary_key=True)
    a_id = models.ForeignKey(TableA, to_field='id')
    b_id = SpanningForeignKey(TableB, db_constraint=False, to_field='id')

app2 db2_app/models.py的模型:(DB2)

Models for app2 db2_app/models.py: (DB2)

class TableB(models.Model):
    id = models.AutoField(primary_key=True)
    # some other fields

数据库路由器:

def db_for_read(self, model, **hints):
    if model._meta.app_label == 'db1_app':
        return 'DB1'

    if model._meta.app_label == 'db2_app':
        return 'DB2'

    return None

我可以强制Django在查询中包括数据库名称吗?还是对此有任何解决方法?

Can I force Django to include the database name in the query? Or is there any workaround for this?

推荐答案

广泛的编辑

针对 MySQL 和 sqlite 后端的 Django 1.6 + (包括1.11)存在一个解决方案，通过选项db_table = '"db2"."table2"')引号，则它不加引号，而点号有效查询由Django ORM编译.更好的类似解决方案是db_table = 'db2"."table2'(不仅允许连接，而且还允许跨数据库约束迁移的一个问题)

A solution exists for Django 1.6+ (including 1.11) for MySQL and sqlite backends, by option ForeignKey.db_constraint=False and explicit Meta.db_table. If the database name and table name are quoted by ' ` ' (for MySQL) or by ' " ' (for other db), e.g. db_table = '"db2"."table2"'). Then it is not quoted more and the dot is out of quoted. Valid queries are compiled by Django ORM. A better similar solution is db_table = 'db2"."table2' (that allows not only joins but it is also by one issue nearer to cross db constraint migration)

db2_name = settings.DATABASES['db2']['NAME']

class Table1(models.Model):
    fk = models.ForeignKey('Table2', on_delete=models.DO_NOTHING, db_constraint=False)

class Table2(models.Model):
    name = models.CharField(max_length=10)
    ....
    class Meta:    
        db_table = '`%s`.`table2`' % db2_name  # for MySQL
        # db_table = '"db2"."table2"'          # for all other backends
        managed = False

查询集:

>>> qs = Table2.objects.all()
>>> str(qs.query)
'SELECT "DB2"."table2"."id" FROM DB2"."table2"'
>>> qs = Table1.objects.filter(fk__name='B')
>>> str(qs.query)
SELECT "app_table1"."id"
    FROM "app_table1"
    INNER JOIN "db2"."app_table2" ON ( "app_table1"."fk_id" = "db2"."app_table2"."id" )
    WHERE "db2"."app_table2"."b" = 'B'

Django中所有数据库后端支持该查询解析，但是后端必须单独讨论其他必要步骤.我正在尝试更广泛地回答，因为我发现了一个

That query parsing is supported by all db backends in Django, however other necessary steps must be discussed individually by backends. I'm trying to answer more generally because I found a similar important question.

迁移必需使用选项'db_constraint'，因为Django无法创建参考完整性约束.
ADD foreign key table1(fk_id) REFERENCES db2.table2(id)，
但它可以为MySQL手动创建.

The option 'db_constraint' is necessary for migrations, because Django can not create the reference integrity constraint
ADD foreign key table1(fk_id) REFERENCES db2.table2(id),
but it can be created manually for MySQL.

对于特定后端的问题是，是否可以在运行时将另一个数据库连接到默认数据库，以及是否支持跨数据库外键.这些模型也是可写的.间接连接的数据库应与managed=False一起用作旧数据库(因为仅在直接连接的数据库中创建了一个用于迁移跟踪的表django_migrations.该表应仅描述同一数据库中的表.)外部索引但是，如果数据库系统支持此类索引，则可以在托管端自动创建密钥.

A question for particular backends is if another database can be connected to the default at run-time and if a cross database foreign key is supported. These models are also writable. The indirectly connected database should be used as a legacy database with managed=False (because only one table django_migrations for migrations tracking is created only in the directly connected database. This table should describe only tables in the same database.) Indexes for foreign keys can however be created automatically on the managed side if the database system supports such indexes.

Sqlite3 :必须在运行时将其附加到另一个默认的sqlite3数据库(answer

Sqlite3: It has to be attached to another default sqlite3 database at run-time (answer SQLite - How do you join tables from different databases), at best by the signal connection_created:

from django.db.backends.signals import connection_created

def signal_handler(sender, connection, **kwargs):
    if connection.alias == 'default' and connection.vendor == 'sqlite':
        cur = connection.cursor()
        cur.execute("attach '%s' as db2" % db2_name)
        # cur.execute("PRAGMA foreign_keys = ON")  # optional

connection_created.connect(signal_handler)

然后，它当然不需要数据库路由器，并且正常的django...ForeignKey可以与db_constraint = False一起使用.优点是，如果表名在数据库之间是唯一的，则不需要"db_table".

Then it doesn't need a database router of course and a normal django...ForeignKey can be used with db_constraint=False. An advantage is that "db_table" is not necessary if the table names are unique between databases.

在 MySQL 中不同数据库之间的外键很简单.诸如SELECT，INSERT，DELETE之类的所有命令都支持任何数据库名称，而无需事先附加它们.

In MySQL foreign keys between different databases are easy. All commands like SELECT, INSERT, DELETE support any database names without attaching them previously.

这个问题是关于遗留数据库的.但是，在迁移过程中，我也得到了一些有趣的结果.

This question was about legacy databases. I have however some interesting results also with migrations.

这篇关于Django ManyToMany通过多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！