How to use schemas in Django?

Question

I would like to use postgreSQL schemas with django, how can I do this?

Answer 1

I used:

db_table = '"schema"."tablename"' 

in the past, not realizing that they only work for read-only work. When you try to add a new record, it will not work, because the sequence will look like "schema.tablename" _column_id_seq.

 db_table = 'schema\".\"tablename' 

still working. Thank.

Answer 2

Perhaps this will help.

 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'OPTIONS': { 'options': '-c search_path=your_schema' }, 'NAME': 'your_name', 'USER': 'your_user', 'PASSWORD': 'your_password', 'HOST': '127.0.0.1', 'PORT': '5432', } } 

I get the answer at the following link: http://blog.amvtek.com/posts/2014/Jun/13/accessing-multiple-postgres-schemas-from-django/

Answer 3

This is a bit more complicated than complex shielding. Look at Ticket # 6148 in Django, maybe a solution, or at least a patch. It makes some minor changes in the depth of the django.db core, but hopefully will be officially included in django. After that, it's just a question

 db_schema = 'whatever_schema' 

in the Meta class or for a global set of changes

 DATABASE_SCHEMA = 'default_schema_name' 

in settings.py

UPDATE: 2015-01-08

The corresponding problem in django has been open for 7 years, and the fix there will no longer work. The correct answer to this question should be ...

At the moment, you cannot use postgreSQL schemas in django out of the box.

Answer 4

As indicated in the following ticket: https://code.djangoproject.com/ticket/6148 , we could set the search_path for the django user.

One way to achieve this is to set search_path through the psql client, for example

 ALTER USER my_user SET SEARCH_PATH TO path; 

Another way is to modify the django application so that if we rebuild the database, django will not re-rip all tables in the public schema.

To achieve this, you can override the DatabaseWrapper defined in django.db.backends.postgresql_psycopg2.base

• Create the following directory:

   app/pg/ ├── __init__.py └── base.py 

• Here is the content of base.py

   from django.db.backends.postgresql_psycopg2.base import DatabaseWrapper class DatabaseWrapper(DatabaseWrapper): def __init__(self, *args, **kwargs): super(DatabaseWrapper, self).__init__(*args, **kwargs) def _cursor(self): cursor = super(DatabaseWrapper, self)._cursor() cursor.execute('SET search_path = path') return cursor 

• In settings.py add the following database configuration:

   DATABASES = { 'default': { 'ENGINE': 'app.pg', 'NAME': 'db', 'USER': 'user', 'PASSWORD': '', 'HOST': '', 'PORT': '', } } 

Answer 5

I just developed a package for this problem: https://github.com/ryannjohnson/django-schemas .

After some configuration, you can simply call set_db() on your models:

 model_cls = UserModel.set_db(db='db_alias', schema='schema_name') user_on_schema = model_cls.objects.get(pk=1) 

The package uses the methods described in https://stackoverflow.com/questions/75241/... and overflow.site/questions/75241/... then wrap them for use with Django models.

Answer 6

There is no explicit Django support for postgreSQL schemas.

When using Django (0.95), we had to add the search_path to the Django database connector for PostgreSQL, since Django did not support specifying the schema that the tables managed by the ORM used.

Taken from:

http://nxsy.org/using-postgresql-schemas-with-sqlalchemy-and-elixir

The general answer is to use SQLAlchemy to build SQL correctly.

Oh, and here is another link with some suggestions on what you can do with the Django base, expanding it to try to support your schema:

http://news.ycombinator.com/item?id=662901

Answer 7

I had some success just by saying

 db_table = 'schema\".\"tablename' 

in the Meta class, but it's really ugly. And I used it only in limited scenarios - it can break if you try something complicated. And, as mentioned earlier, it is not really supported ...

Answer 8

I know this is a pretty old question, but another solution is to change SEARCH_PATH.

Example

Let's say you have three tables.

• schema1.table_name_a
• schema2.table_name_b
• table_name_c

You can execute the command:

 SET SEARCH_PATH to public,schema1,schema2; 

And refer to tables by their table names only in django.

See 5.7.3. Schema Search Path

Answer 9

For a SQL Server database:

 db_table = "[your_schema].[your_table]"