How to work around lack of support for foreign keys across databases in Django

I know Django does not support foreign keys across multiple databases (originally Django 1.3 docs)

But I'm looking for a workaround.

What doesn't work

I have two models each on a separate database.

routers.py:

class NewsRouter(object):
def db_for_read(self, model, **hints):
if model._meta.app_label == 'news_app':
return 'news_db'
return None


def db_for_write(self, model, **hints):
if model._meta.app_label == 'news_app':
return 'news_db'
return None


def allow_relation(self, obj1, obj2, **hints):
if obj1._meta.app_label == 'news_app' or obj2._meta.app_label == 'news_app':
return True
return None


def allow_syncdb(self, db, model):
if db == 'news_db':
return model._meta.app_label == 'news_app'
elif model._meta.app_label == 'news_app':
return False
return None

Model 1 in fruit_app/models.py:

from django.db import models


class Fruit(models.Model):
name = models.CharField(max_length=20)

Model 2 in news_app/models.py:

from django.db import models


class Article(models.Model):
fruit = models.ForeignKey('fruit_app.Fruit')
intro = models.TextField()

Trying to add a "Article" in the admin gives the following error because it is looking for the Fruit model on the wrong database ('news_db'):

DatabaseError at /admin/news_app/article/add/


(1146, "Table 'fkad_news.fruit_app_fruit' doesn't exist")

Method 1: subclass IntegerField

I created a custom field, ForeignKeyAcrossDb, which is a subclass of IntegerField. Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/integerfield_subclass

fields.py:

from django.db import models




class ForeignKeyAcrossDb(models.IntegerField):
'''
Exists because foreign keys do not work across databases
'''
def __init__(self, model_on_other_db, **kwargs):
self.model_on_other_db = model_on_other_db
super(ForeignKeyAcrossDb, self).__init__(**kwargs)


def to_python(self, value):
# TODO: this db lookup is duplicated in get_prep_lookup()
if isinstance(value, self.model_on_other_db):
return value
else:
return self.model_on_other_db._default_manager.get(pk=value)


def get_prep_value(self, value):
if isinstance(value, self.model_on_other_db):
value = value.pk
return super(ForeignKeyAcrossDb, self).get_prep_value(value)


def get_prep_lookup(self, lookup_type, value):
# TODO: this db lookup is duplicated in to_python()
if not isinstance(value, self.model_on_other_db):
value = self.model_on_other_db._default_manager.get(pk=value)


return super(ForeignKeyAcrossDb, self).get_prep_lookup(lookup_type, value)

And I changed my Article model to be:

class Article(models.Model):
fruit = ForeignKeyAcrossDb(Fruit)
intro = models.TextField()

The problem is, sometimes when I access Article.fruit, it is an integer, and sometimes it is the Fruit object. I want it to always be a Fruit object. What do I need to do to make accessing Article.fruit always return a Fruit object?

As a workaround for my workaround, I added a fruit_obj property, but I would like to eliminate this if possible:

class Article(models.Model):
fruit = ForeignKeyAcrossDb(Fruit)
intro = models.TextField()


# TODO: shouldn't need fruit_obj if ForeignKeyAcrossDb field worked properly
@property
def fruit_obj(self):
if not hasattr(self, '_fruit_obj'):
# TODO: why is it sometimes an int and sometimes a Fruit object?
if isinstance(self.fruit, int) or isinstance(self.fruit, long):
print 'self.fruit IS a number'
self._fruit_obj = Fruit.objects.get(pk=self.fruit)
else:
print 'self.fruit IS NOT a number'
self._fruit_obj = self.fruit
return self._fruit_obj


def fruit_name(self):
return self.fruit_obj.name

Method 2: subclass ForeignKey field

As a second attempt, I tried subclassing the ForeignKey field. I modified ReverseSingleRelatedObjectDescriptor to use the database specified by forced_using on the model manager of Fruit. I also removed the validate() method on the ForeignKey subclass. This method did not have the same problem as method 1. Code on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject/tree/foreignkey_subclass

fields.py:

from django.db import models
from django.db import router
from django.db.models.query import QuerySet




class ReverseSingleRelatedObjectDescriptor(object):
# This class provides the functionality that makes the related-object
# managers available as attributes on a model class, for fields that have
# a single "remote" value, on the class that defines the related field.
# In the example "choice.poll", the poll attribute is a
# ReverseSingleRelatedObjectDescriptor instance.
def __init__(self, field_with_rel):
self.field = field_with_rel


def __get__(self, instance, instance_type=None):
if instance is None:
return self


cache_name = self.field.get_cache_name()
try:
return getattr(instance, cache_name)
except AttributeError:
val = getattr(instance, self.field.attname)
if val is None:
# If NULL is an allowed value, return it.
if self.field.null:
return None
raise self.field.rel.to.DoesNotExist
other_field = self.field.rel.get_related_field()
if other_field.rel:
params = {'%s__pk' % self.field.rel.field_name: val}
else:
params = {'%s__exact' % self.field.rel.field_name: val}


# If the related manager indicates that it should be used for
# related fields, respect that.
rel_mgr = self.field.rel.to._default_manager
db = router.db_for_read(self.field.rel.to, instance=instance)
if getattr(rel_mgr, 'forced_using', False):
db = rel_mgr.forced_using
rel_obj = rel_mgr.using(db).get(**params)
elif getattr(rel_mgr, 'use_for_related_fields', False):
rel_obj = rel_mgr.using(db).get(**params)
else:
rel_obj = QuerySet(self.field.rel.to).using(db).get(**params)
setattr(instance, cache_name, rel_obj)
return rel_obj


def __set__(self, instance, value):
raise NotImplementedError()


class ForeignKeyAcrossDb(models.ForeignKey):


def contribute_to_class(self, cls, name):
models.ForeignKey.contribute_to_class(self, cls, name)
setattr(cls, self.name, ReverseSingleRelatedObjectDescriptor(self))
if isinstance(self.rel.to, basestring):
target = self.rel.to
else:
target = self.rel.to._meta.db_table
cls._meta.duplicate_targets[self.column] = (target, "o2m")


def validate(self, value, model_instance):
pass

fruit_app/models.py:

from django.db import models




class FruitManager(models.Manager):
forced_using = 'default'




class Fruit(models.Model):
name = models.CharField(max_length=20)


objects = FruitManager()

news_app/models.py:

from django.db import models


from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
from foreign_key_across_db_testproject.fruit_app.models import Fruit




class Article(models.Model):
fruit = ForeignKeyAcrossDb(Fruit)
intro = models.TextField()


def fruit_name(self):
return self.fruit.name

Method 2a: Add a router for fruit_app

This solution uses an additional router for fruit_app. This solution does not require the modifications to ForeignKey that were required in Method 2. After looking at Django's default routing behavior in ForeignKey2, we found that even though we expected fruit_app to be on the 'default' database by default, the instance hint passed to db_for_read for foreign key lookups put it on the 'news_db' database. We added a second router to ensure fruit_app models were always read from the 'default' database. A ForeignKey subclass is only used to "fix" the ForeignKey1 method. (If Django wanted to support foreign keys across databases, I would say this is a Django bug.) Code is on github at: https://github.com/saltycrane/django-foreign-key-across-db-testproject

routers.py:

class NewsRouter(object):
def db_for_read(self, model, **hints):
if model._meta.app_label == 'news_app':
return 'news_db'
return None


def db_for_write(self, model, **hints):
if model._meta.app_label == 'news_app':
return 'news_db'
return None


def allow_relation(self, obj1, obj2, **hints):
if obj1._meta.app_label == 'news_app' or obj2._meta.app_label == 'news_app':
return True
return None


def allow_syncdb(self, db, model):
if db == 'news_db':
return model._meta.app_label == 'news_app'
elif model._meta.app_label == 'news_app':
return False
return None




class FruitRouter(object):
def db_for_read(self, model, **hints):
if model._meta.app_label == 'fruit_app':
return 'default'
return None


def db_for_write(self, model, **hints):
if model._meta.app_label == 'fruit_app':
return 'default'
return None


def allow_relation(self, obj1, obj2, **hints):
if obj1._meta.app_label == 'fruit_app' or obj2._meta.app_label == 'fruit_app':
return True
return None


def allow_syncdb(self, db, model):
if db == 'default':
return model._meta.app_label == 'fruit_app'
elif model._meta.app_label == 'fruit_app':
return False
return None

fruit_app/models.py:

from django.db import models




class Fruit(models.Model):
name = models.CharField(max_length=20)

news_app/models.py:

from django.db import models


from foreign_key_across_db_testproject.fields import ForeignKeyAcrossDb
from foreign_key_across_db_testproject.fruit_app.models import Fruit




class Article(models.Model):
fruit = ForeignKeyAcrossDb(Fruit)
intro = models.TextField()


def fruit_name(self):
return self.fruit.name

fields.py:

from django.core import exceptions
from django.db import models
from django.db import router




class ForeignKeyAcrossDb(models.ForeignKey):


def validate(self, value, model_instance):
if self.rel.parent_link:
return
models.Field.validate(self, value, model_instance)
if value is None:
return


using = router.db_for_read(self.rel.to, instance=model_instance)  # is this more correct than Django's 1.2.5 version?
qs = self.rel.to._default_manager.using(using).filter(
**{self.rel.field_name: value}
)
qs = qs.complex_filter(self.rel.limit_choices_to)
if not qs.exists():
raise exceptions.ValidationError(self.error_messages['invalid'] % {
'model': self.rel.to._meta.verbose_name, 'pk': value})

Additional information

Update

We implemented the last method after tweaking our routers some more. The whole implementation has been pretty painful which makes us think that we must be doing it wrong. On the TODO list is writing unit tests for this.

8220 次浏览
小开

您可以在数据库中创建一个包含交叉数据库查询的视图，然后在一个单独的文件中为该视图定义模型，以保持 syncdb 正常工作。

Happy programming. :)

小开

我知道 Djano-nosql 支持键，尽管 http://www.allbuttonspressed.com/projects/django-dbindexer有一些魔力。也许有些能帮上忙。

根据描述:

“您只需告诉 dbindexer 哪些模型和字段应该支持这些查询，它就会为您维护所需的索引。”

-Kerry

小开

至于 ForeignKeyAcrossDb部分，你能不能在 __init__内部对你的课程做一些调整？检查适当的字段是否为 Integer(如果不是) ，从数据库加载它，或者执行所需的任何其他操作。Python__class__es 可以在运行时进行更改，没有太多问题。

小开

外键字段意味着您可以 - 通过加入 ie Fruit _ _ name 查询关系 检查参照完整性 - 确保删除参照完整性 - 管理原始 ID 查找功能 - (更多...)

The first use case would always be problematic. 可能在代码库中还有其他一些外键特例也不能工作。

我运行一个相当大的 django 站点，我们目前使用的是一个普通的整数域。 现在，我认为对整数字段进行子类化并将 id 添加到对象转换是最简单的(在1.2中，需要修补一些 django 位，希望现在已经改进了) 会让你知道我们找到了什么解决方案。

小开

几天后，我打破了我的头，我设法得到我的外国钥匙在同一家银行！

可以作出一个改变的形式，以寻求一个外国的关键在不同的银行！

首先，在函数 _ _ _ init _ _ _ 中添加 FIELDS 的 RECHARGE，两者都直接(破解)我的表单

App.form.py

# -*- coding: utf-8 -*-
from django import forms
import datetime
from app_ti_helpdesk import models as mdp


#classe para formulario de Novo HelpDesk
class FormNewHelpDesk(forms.ModelForm):
class Meta:
model = mdp.TblHelpDesk
fields = (
"problema_alegado",
"cod_direcionacao",
"data_prevista",
"hora_prevista",
"atendimento_relacionado_a",
"status",
"cod_usuario",
)


def __init__(self, *args, **kwargs):
#-------------------------------------
#  using remove of kwargs
#-------------------------------------
db = kwargs.pop("using", None)


# CASE use Unique Keys
self.Meta.model.db = db


super(FormNewHelpDesk, self).__init__(*args,**kwargs)


#-------------------------------------
#   recreates the fields manually
from copy import deepcopy
self.fields.update(deepcopy( forms.fields_for_model( self.Meta.model, self.Meta.fields, using=db ) ))
#
#-------------------------------------


#### follows the standard template customization, if necessary


self.fields['problema_alegado'].widget.attrs['rows'] = 3
self.fields['problema_alegado'].widget.attrs['cols'] = 22
self.fields['problema_alegado'].required = True
self.fields['problema_alegado'].error_messages={'required': 'Necessário informar o motivo da solicitação de ajuda!'}




self.fields['data_prevista'].widget.attrs['class'] = 'calendario'
self.fields['data_prevista'].initial = (datetime.timedelta(4)+datetime.datetime.now().date()).strftime("%Y-%m-%d")


self.fields['hora_prevista'].widget.attrs['class'] = 'hora'
self.fields['hora_prevista'].initial =datetime.datetime.now().time().strftime("%H:%M")


self.fields['status'].initial = '0'                 #aberto
self.fields['status'].widget.attrs['disabled'] = True


self.fields['atendimento_relacionado_a'].initial = '07'


self.fields['cod_direcionacao'].required = True
self.fields['cod_direcionacao'].label = "Direcionado a"
self.fields['cod_direcionacao'].initial = '2'
self.fields['cod_direcionacao'].error_messages={'required': 'Necessário informar para quem é direcionado a ajuda!'}


self.fields['cod_usuario'].widget = forms.HiddenInput()

从视图中调用表单

App.view.py

form = forms.FormNewHelpDesk(request.POST or None, using=banco)

现在，源代码 DJANGO 中的更改

只有 ForeignKey、 ManyToManyField 和 OneToOneField 类型的字段可以使用“ using”，因此添加了 IF..。

Django.forms.models.py

# line - 133: add using=None
def fields_for_model(model, fields=None, exclude=None, widgets=None, formfield_callback=None, using=None):


# line - 159


if formfield_callback is None:
#----------------------------------------------------
from django.db.models.fields.related import (ForeignKey, ManyToManyField, OneToOneField)
if type(f) in (ForeignKey, ManyToManyField, OneToOneField):
kwargs['using'] = using


formfield = f.formfield(**kwargs)
#----------------------------------------------------
elif not callable(formfield_callback):
raise TypeError('formfield_callback must be a function or callable')
else:
formfield = formfield_callback(f, **kwargs)

更改跟踪文件

Django.db.models.base.py

改变

# line 717
qs = model_class._default_manager.filter(**lookup_kwargs)

为了

# line 717
qs = model_class._default_manager.using(getattr(self, 'db', None)).filter(**lookup_kwargs)

预备: D

小开

遇到了类似的问题，需要跨多个(5)数据库引用(主要是)静态数据。稍微更新了“反向单相关对象描述符”，以允许设置相关模型。它不实现反向关系 ATM。

class ReverseSingleRelatedObjectDescriptor(object):
"""
This class provides the functionality that makes the related-object managers available as attributes on a model
class, for fields that have a single "remote" value, on the class that defines the related field. Used with
LinkedField.
"""
def __init__(self, field_with_rel):
self.field = field_with_rel
self.cache_name = self.field.get_cache_name()


def __get__(self, instance, instance_type=None):
if instance is None:
return self


try:
return getattr(instance, self.cache_name)
except AttributeError:
val = getattr(instance, self.field.attname)
if val is None:
# If NULL is an allowed value, return it
if self.field.null:
return None
raise self.field.rel.to.DoesNotExist
other_field = self.field.rel.get_related_field()
if other_field.rel:
params = {'%s__pk' % self.field.rel.field_name: val}
else:
params = {'%s__exact' % self.field.rel.field_name: val}


# If the related manager indicates that it should be used for related fields, respect that.
rel_mgr = self.field.rel.to._default_manager
db = router.db_for_read(self.field.rel.to, instance=instance)
if getattr(rel_mgr, 'forced_using', False):
db = rel_mgr.forced_using
rel_obj = rel_mgr.using(db).get(**params)
elif getattr(rel_mgr, 'use_for_related_fields', False):
rel_obj = rel_mgr.using(db).get(**params)
else:
rel_obj = QuerySet(self.field.rel.to).using(db).get(**params)
setattr(instance, self.cache_name, rel_obj)
return rel_obj


def __set__(self, instance, value):
if instance is None:
raise AttributeError("%s must be accessed via instance" % self.field.name)


# If null=True, we can assign null here, but otherwise the value needs to be an instance of the related class.
if value is None and self.field.null is False:
raise ValueError('Cannot assign None: "%s.%s" does not allow null values.' %
(instance._meta.object_name, self.field.names))
elif value is not None and not isinstance(value, self.field.rel.to):
raise ValueError('Cannot assign "%r": "%s.%s" must be a "%s" instance.' %
(value, instance._meta.object_name, self.field.name, self.field.rel.to._meta.object_name))
elif value is not None:
# Only check the instance state db, LinkedField implies that the value is on a different database
if instance._state.db is None:
instance._state.db = router.db_for_write(instance.__class__, instance=value)


# Is not used by OneToOneField, no extra measures to take here


# Set the value of the related field
try:
val = getattr(value, self.field.rel.get_related_field().attname)
except AttributeError:
val = None
setattr(instance, self.field.attname, val)


# Since we already know what the related object is, seed the related object caches now, too. This avoids another
# db hit if you get the object you just set
setattr(instance, self.cache_name, value)
if value is not None and not self.field.rel.multiple:
setattr(value, self.field.related.get_cache_name(), instance)

还有

class LinkedField(models.ForeignKey):
"""
Field class used to link models across databases. Does not ensure referrential integraty like ForeignKey
"""
def _description(self):
return "Linked Field (type determined by related field)"


def contribute_to_class(self, cls, name):
models.ForeignKey.contribute_to_class(self, cls, name)
setattr(cls, self.name, ReverseSingleRelatedObjectDescriptor(self))
if isinstance(self.rel.to, basestring):
target = self.rel.to
else:
target = self.rel.to._meta.db_table
cls._meta.duplicate_targets[self.column] = (target, "o2m")


def validate(self, value, model_instance):
pass
小开

受@Frans 评论的启发。我的解决方案是在业务层执行此操作。在例子中给出了这个问题。我会设置水果的 IntegerField对 Article，作为“不做完整性检查在数据层”。

class Fruit(models.Model):
name = models.CharField()


class Article(models.Model):
fruit = models.IntegerField()
intro = models.TextField()

然后尊重应用程序代码(业务层)中的引用关系。以 Django 管理员为例，为了在 Article 的 add 页面中显示水果作为选项，需要手动填充一个水果选项列表。

# admin.py in App article
class ArticleAdmin(admin.ModelAdmin):
class ArticleForm(forms.ModelForm):
fields = ['fruit', 'intro']


# populate choices for fruit
choices = [(obj.id, obj.name) for obj in Fruit.objects.all()]
widgets = {
'fruit': forms.Select(choices=choices)}


form = ArticleForm
list_diaplay = ['fruit', 'intro']

当然，您可能需要注意表单字段验证(完整性检查)。

小开

我为 django v1.10提供了一个新的解决方案。

  1. 继承 ForeignKey类并创建 ForeignKeyAcrossDb，并基于此 ticket和此 邮寄重写 validate()函数。

class ForeignKeyAcrossDb(models.ForeignKey):
def validate(self, value, model_instance):
if self.remote_field.parent_link:
return
super(models.ForeignKey, self).validate(value, model_instance)
if value is None:
return
using = router.db_for_read(self.remote_field.model, instance=model_instance)
qs = self.remote_field.model._default_manager.using(using).filter(
**{self.remote_field.field_name: value}
)
qs = qs.complex_filter(self.get_limit_choices_to())
if not qs.exists():
raise exceptions.ValidationError(
self.error_messages['invalid'],
code='invalid',
params={
'model': self.remote_field.model._meta.verbose_name, 'pk': value,
'field': self.remote_field.field_name, 'value': value,
},  # 'pk' is included for backwards compatibility
)
  1. 在字段声明中，使用 db_constraint=False，例如,

album=ForeignKeyAcrossDb(Singer, db_constraint=False, on_delete=models.DO_NOTHING)

小开

这个解决方案最初是为一个带有迁移的托管数据库和一个或多个模型 Meta managed=False在数据库级连接到同一数据库的遗留数据库编写的。如果 db_table选项包含数据库名和表名 引用，’96;(MySQL)或 ' " '(其他 db)正确，例如 db_table = '"DB2"."table_b"'，则 Django 不再引用它。即使使用 JOIN，Django ORM 也能正确地编译查询:

class TableB(models.Model):
....
class Meta:
db_table = '`DB2`.`table_b`'    # for MySQL
# db_table = '"DB2"."table_b"'  # for all other backends
managed = False

查询集:

>>> qs = TableB.objects.all()
>>> str(qs.query)
'SELECT "DB2"."table_b"."id" FROM DB2"."table_b"'

Django 中的 所有数据库后端支持的。

(似乎我开始悬赏一个 重复的新问题，我的答案还在继续。)


提交答案