另一个模式中的Django外键 [英] Django Foreign key in another schema
问题描述
我有一个带有2个shemas(A和B)的MySQL数据库。
我的django应用程序可以读取&写入A。
它只能从B中读取。
I have a MySQL database with 2 shemas (A & B).
My django app can read & write into A.
It can just read from B.
我的应用管理了A中的所有表。
B我已经想在表(b)中包含一些数据。
My app managed all the tables in A.
B already contains some data in a table (b).
我想在A和B之间的一个字段中添加一个。
这样的东西:
I want to add a one to one field between A and B.
Something like this :
class SchemaBTableB(models.Model):
class Meta:
managed = False
db_schema = 'B'
db_table = 'b'
[...]
class SchemaATableA(models.Model):
class Meta:
db_schema = 'A'
db_table = 'a'
id = models.OneToOneField(
SchemaBTableB,
on_delete=models.DO_NOTHING,
primary_key=True
)
[...]
不幸的是, db_schema
不存在。
有人知道解决方案吗?
Unfortunately, db_schema
does not exist.
Does someone know a solution ?
推荐答案
我已尝试使用两个数据库来模拟您的案例,并找出解决方案
I have been tried to use two databases to simulate your case, and find out the solution below:
- 数据库
schema1
,由django管理(读和写) ) - 数据库
schema2
,由django管理 NOT
- database
schema1
, which is managed by django (read & write) - database
schema2
, which is NOT managed by django
2。步骤:
2. Steps:
- 为模型创建迁移
python manage.py makemigrations
- 为您的迁移生成SQL:
python manage.py sqlmigrate应用0001
。
(假定从步骤1生成的迁移文件名为0001_initial.py
)
- create migrations
python manage.py makemigrations
for your models - Generate SQL for your migration:
python manage.py sqlmigrate app 0001
. (suppose the generated migration file name is0001_initial.py
from step 1)
此迁移的sql应该看起来像这样:
The sql for this migration should look like this:
CREATE TABLE `user_info` (`id_id` integer NOT NULL PRIMARY KEY, `name` varchar(20) NOT NULL);
ALTER TABLE `user_info` ADD CONSTRAINT `user_info_id_id_e8dc4652_fk_schema2.user_extra_info_id` FOREIGN KEY (`id_id`) REFERENCES `user_extra_info` (`id`);
COMMIT;
如果直接运行上面的sql,您将得到如下错误:
If you run the above sql directly, you will be ended up with error like this:
django.db.utils.OperationalError: (1824, "Failed to open the referenced table 'user_extra_info'")
这是因为django假定所有迁移步骤都在同一数据库中执行。因此它无法在 schema1
数据库中找到 user_extra_info
。
That's because django assumes that all your migration steps are executed in the same database. So it can not find out the user_extra_info
in schema1
database.
-
明确指定数据库
schema2
对于表user_extra_info
:
ALTER TABLE `user_info` ADD CONSTRAINT `user_info_id_id_e8dc4652_fk_schema2.user_extra_info_id` FOREIGN KEY (`id_id`) REFERENCES schema2.user_extra_info (`id`);
在 schema1 $ c $中手动运行修改后的sql c>数据库。
告诉Django我自己运行了迁移: python manage.py migration --fake
Tell django I have run the migration myself: python manage.py migrate --fake
完成!
源代码供您参考:
models.py
Source code For your reference:
models.py
from django.db import models
class UserExtraInfo(models.Model):
# table in schema2, not managed by django
name = models.CharField('name', max_length=20)
class Meta:
managed = False
db_table = 'user_extra_info'
class UserInfo(models.Model):
# table in schema1, managed by django
id = models.OneToOneField(
UserExtraInfo,
on_delete=models.CASCADE,
primary_key=True
)
name = models.CharField('user name', max_length=20)
class Meta:
db_table = 'user_info'
settings.py
settings.py
# Database
# https://docs.djangoproject.com/en/2.1/ref/settings/#databases
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'schema1',
'USER': 'USER',
'PASSWORD': 'PASSWORD',
'HOST': 'localhost',
'PORT': 3306,
},
'extra': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'schema2',
'USER': 'USER',
'PASSWORD': 'PASSWORD',
'HOST': 'localhost',
'PORT': 3306,
}
}
DATABASE_ROUTERS = ['two_schemas.router.DBRouter']
< h3> router.py
router.py
class DBRouter(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.db_table == 'user_extra_info':
# specify the db for `user_extra_info` table
return 'extra'
if model._meta.app_label == 'app':
return 'default'
return None
def db_for_write(self, model, **hints):
"""
Attempts to write auth models go to auth_db.
"""
if model._meta.db_table == 'user_extra_info':
# specify the db for `user_extra_info` table
return 'extra'
if model._meta.app_label == 'app':
return 'default'
return None
def allow_relation(self, obj1, obj2, **hints):
"""
Relations between objects are allowed if both objects are
in the primary/replica pool.
"""
db_list = ('default', 'extra')
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):
"""
Make sure the auth app only appears in the 'auth_db'
database.
"""
if app_label == 'app':
return db == 'default'
return None
这篇关于另一个模式中的Django外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!