Django 和 postgresql 模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50819748/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django and postgresql schemas
提问by Spatial Digger
I've been trying to solve this one all week, help very much appreciated.
我整个星期都在努力解决这个问题，非常感谢帮助。
I have various schemas in a postgres db and I would like to be able to map to them from within the same or across different django apps.
我在 postgres 数据库中有各种模式，我希望能够从相同或不同的 django 应用程序中映射到它们。
Some of the schemas are :
一些模式是：
samples
样品
excavation
挖掘
geophysics
地球物理学
...
...
I have tried the recommended way, but I'm not getting any data to display from the schemas, I can only connect to the public schema with managed tables. Here is the database connections from the settings.py file.
我已经尝试了推荐的方法，但我没有从模式中获取任何数据来显示，我只能连接到带有托管表的公共模式。这是 settings.py 文件中的数据库连接。
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=django,public'
},
'NAME': 'gygaia',
'USER': 'appuser',
'PASSWORD': 'secret',
},
'samples': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=samples,public'
},
'NAME': 'gygaia',
'USER': 'appuser',
'PASSWORD': 'secret',
},
}
source: https://www.amvtek.com/blog/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/
来源：https: //www.amvtek.com/blog/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/
In the model.py I add:
在model.py中我添加：
from django.db import models
# Create your models here.
class Storage(models.Model):
#id = models.IntegerField(default=0)
storage_id = models.AutoField(primary_key=True)
store_name = models.CharField(max_length=200, default='')
address_1 = models.CharField(max_length=200, default='')
address_2 = models.CharField(max_length=200, default='')
region = models.CharField(max_length=200, default='')
city = models.CharField(max_length=200, default='')
zip = models.CharField(max_length=200, default='')
country = models.CharField(max_length=200, default="Turkey")
user = models.CharField(max_length=200, default="Gygaia")
datestamp = models.DateTimeField(auto_now=True)
class Meta():
managed=False
db_table = 'samples\".\"store'
I don't want to restrict schemas to users, and the database was created a few years ago so I'm not allowed to bring it all under one schema. I know there are various solutions posted on stackoverflow and other coreners of the internet, I have tried these, but I'm unable to get this to work. Any ideas how to solve thos one??
我不想将模式限制为用户，数据库是几年前创建的，所以我不允许将其全部置于一个模式下。我知道在 stackoverflow 和互联网的其他核心上发布了各种解决方案，我已经尝试过这些，但我无法让它发挥作用。任何想法如何解决那个？
回答by tarikki
Because Django does not support Postgres database schemas out of the box, in order to get this to work, use a database router.
因为 Django 不支持开箱即用的 Postgres 数据库模式，为了让它工作，请使用数据库路由器。
I created a test database to try this out with, here's how to reproduce it:
我创建了一个测试数据库来尝试这个，这里是如何重现它：
Create a test database with psql:
使用 psql 创建一个测试数据库：
CREATE USER tester WITH PASSWORD 'lol so easy';
CREATE DATABASE multi_schema_db WITH OWNER tester;
CREATE SCHEMA samples AUTHORIZATION tester;
CREATE TABLE samples.my_samples (
id INTEGER NOT NULL PRIMARY KEY,
description CHAR(255) NOT NULL
);
Add the schemas to the settings as different database connections, remember to add HOSTto avoid the “Peer authentication failed” error.
将架构作为不同的数据库连接添加HOST到设置中，记得添加以避免“Peer authentication failed”错误。
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=django,public'
},
'NAME': 'multi_schema_db',
'USER': 'tester',
'PASSWORD': 'lol so easy',
'HOST': 'localhost'
},
'samples': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'OPTIONS': {
'options': '-c search_path=samples,public'
},
'NAME': 'multi_schema_db',
'USER': 'tester',
'PASSWORD': 'lol so easy',
'HOST': 'localhost'
},
}
}
Next create the MySamplemodel:
接下来创建MySample模型：
from django.db import models
class MySample(models.Model):
description = models.CharField(max_length=255, null=False)
class Meta:
managed = False
db_table = 'my_samples'
Create a database router to direct all sample-related queries to the sample database:
创建一个数据库路由器，将所有与样本相关的查询定向到样本数据库：
from database_test.models import MySample
ROUTED_MODELS = [MySample]
class MyDBRouter(object):
def db_for_read(self, model, **hints):
if model in ROUTED_MODELS:
return 'samples'
return None
def db_for_write(self, model, **hints):
if model in ROUTED_MODELS:
return 'samples'
return None
Basically, the router will route all the models specified in ROUTED_MODELS to the database connection samplesand return None for all the other models. This will route them to the defaultdatabase connection.
基本上，路由器会将 ROUTED_MODELS 中指定的所有模型路由到数据库连接，samples并为所有其他模型返回 None 。这会将它们路由到default数据库连接。
Finally add the router to your settings.py
最后将路由器添加到您的 settings.py
DATABASE_ROUTERS = ('database_test.db_router.MyDBRouter',)
And now when doing a query for the MySamplemodel, it will fetch data from the samplesschema.
现在在对MySample模型进行查询时，它将从samples模式中获取数据。
回答by Josué Carballo Ba?os
I also consulted that source, but I could not solve it like you, but by performing tests I achieved the following.
我也咨询了该来源，但我无法像您一样解决它，但是通过执行测试，我实现了以下目标。
If we have for example, the schemas foo and bar, writing in the Meta:
例如，如果我们有模式 foo 和 bar，在 Meta 中写入：
class MySample1 (models.Model):
description = models.CharField (max_length = 255, null = False)
class Goal:
managed = True
db_table = 'fo\".\"my_samples1'
class MySample2 (models.Model):
description = models.CharField (max_length = 255, null = False)
class Goal:
managed = True
db_table = 'bar\".\"my_samples2'
Then we can redirect each model to the scheme we want provided we have the variable managed in True. The limitation is that we have to name the table ourselves.
然后我们可以将每个模型重定向到我们想要的方案，前提是我们在 True 中管理了变量。限制是我们必须自己命名表。
