1

Previously i had confused how to handle this problem, this problem also already asked by someone on stackoverflow (Cross Database foreign key error) and has the best answered by someone http://stackoverflow.com/a/23273703/6396981 with quote:

If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.
This is because of referential integrity. In order to maintain a relationship between two objects, Django needs to know that the primary key of the related object is valid. If the primary key is stored on a separate database, it’s not possible to easily evaluate the validity of a primary key.

yap, isn’t possible coz about has a different table.

Other solution by someone at the gist Django Custom Model ForeignKey Field for Spanning Databases with related ticket https://code.djangoproject.com/attachment/ticket/17875/foreignkey-db-using.patch

I already try all of it. But still has an IntegrityError. I think, this problem maybe solve to create the new integer field that reference to the pk/id from other database (field). and thank you so much to god, i have been solved with this solution:

This screenshot bellow is sample result of it…

How to implement the ForeignKey for multi databases Django


1. Makesure you read first how to use the multi databases in Django with database router: https://docs.djangoproject.com/en/dev/topics/db/multi-db.

For example in this config below, i setup it with the postgres in my yourproject/settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'db_2',
        'USER': 'db_user',
        'PASSWORD': 'db_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    },
    'other_db': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'db_1',
        'USER': 'db_user',  
        'PASSWORD': 'db_password',  
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}
DATABASE_ROUTERS = ['yourproject.routers.DBRouter']

2. Create new file example: yourproject/routers.py, please read more about it: https://docs.djangoproject.com/en/1.9/topics/db/multi-db/#an-example

class DBRouter(object):
    """
    Docs: https://docs.djangoproject.com/en/1.9/topics/db/multi-db/#an-example
    A router to control all database operations on models in the
    auth application.
    $ ./manage.py migrate
    $ ./manage.py migrate --database=other_db  
    """

    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to 'other_db'.  
        """
        if model._meta.app_label == 'auth':
            return 'other_db'  
        return 'default'

    def db_for_write(self, model, **hints):
        """
        Attempts to write auth models go to 'other_db'.  
        """
        if model._meta.app_label == 'auth':
            return 'other_db'  
        return 'default'

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth app is involved.
        """
        if obj1._meta.app_label == 'auth' or 
           obj2._meta.app_label == 'auth':
            return True
        return 'default'

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth app only appears in the 'other_db'  
        database.
        """

        if app_label == 'auth':
            return db == 'other_db'  
        return 'default'

3. This field user_id in the yourapp/models.py to save the id that refference to the another databases model (e.g: User)

from django.db import models
from django.contrib.auth.models import User

class Author(models.Model):
    user_id = models.PositiveIntegerField(
        help_text='Type username to search the user id.'
    )
    date_approved = models.DateTimeField(auto_now_add=True)

    def get_user(self):
        return User.objects.get(pk=self.user_id)
    get_user.allow_tags = True
    get_user.short_description = 'user'

    def __str__(self):
        return self.get_user().username

Example for other model that having a ForeignKey to the Author.

class Post(models.Model):
    author = models.ForeignKey(Author, related_name='author_post')
    title = models.CharField(max_length=200)
    ....

4. For the next, i handle the user_id to can auto search the id by typing the username with EasyAutocomplete. but first, you need to rendering the json file before accessing EasyAutocomplete.

Example in this configuration allowing the permission only for superuser.

File: yourapp/views.py

import json
from django.http import HttpResponse
from django.contrib.auth.decorators import user_passes_test
from django.contrib.auth.models import User

@user_passes_test(lambda u: u.is_superuser)
def json_users(request):
    """
    Json authors by `username` & `pk/id`
    only allowed  for 'Developer/Superuser'
    """

    data = [
        {
            'username': user.username,
            'pk': str(user.pk)
        } for user in User.objects.all()
    ]
    return HttpResponse(
        json.dumps(data, sort_keys=True),
        content_type='application/json'
    )

5. And then, in yourapp/urls.py

from django.conf.urls import url
from yourapp.views import json_users

urlpatterns = [
  url(
      r'^admin/json_users.json$',
      json_users,
      name='json_users_page'
  ),
]

6. This takken an easy if you need to render to the custom dashboard with https://cdnjs.com/libraries/easy-autocomplete, but if you work with default admin dashboard? you need to custom it.

File: yourapp/admin.py

from django.contrib import admin
from yourapp.models import Author

class AuthorAdmin(admin.ModelAdmin):
    list_display = ('get_user', 'date_approved')  
    list_filter = ['created']

    class Media:
        js = (
            '/static/js/easy-autocomplete.min.js',
            '/static/js/get_users.js'
        )
        css = {
            'all': (
                '/static/css/easy-autocomplete.min.css',
            )
        }

admin.site.register(Author, AuthorAdmin)

7. Create new file at /static/js/get_users.js to implement the EasyAutocomplete. and the id_user_id is the thml id from field user_id above.

$(document).ready(function() {
  var options = {
    url: "/admin/json_users.json",
    getValue: "username",
    template: {
      type: "description",
      fields: {
        description: "pk"
      }
    },
    list: {
      maxNumberOfElements: 100,
      match: {
        enabled: true
      },
      onSelectItemEvent: function() {
        var index = $("#id_user_id").getSelectedItemData().pk;
        $("#id_user_id").val(index).trigger("change");
      }
    }
  };
  $("#id_user_id").easyAutocomplete(options);
});

Finally, spend a few cups of coffee to solved this problem :D Thank to Fahri Reza for sugested to use integer field.

solution database python django problem multi databases settings

Your Answer

blog comments powered by Disqus