Using Multiple Databases with django

Question

I have to use multiple databases in my project but there is a problem. Model can't see intended database accounts.db.

when i go localhost:port/admin/app/accounts it says me no such table: accounts. Model is looking for default database not accounts.db how can i do that?

I tried multiple things that I found on internet but nothing worked.

settings.py :
.....
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    
    'accounts': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'accounts.db')
    }
}
.....


models.py:
.....
class Accounts(models.Model):     
    
    email = models.TextField(blank=True, null=True)                                                                                                                    
    phone = models.TextField(blank=True, null=True)                                                                                                                    
    name = models.TextField(blank=True, null=True)                                                                                                                     
    password = models.TextField(blank=True, null=True)                                                                                                                 
    verify = models.IntegerField(blank=True, null=True)                                                                                                                
                                                                                                                                                                       
    class Meta:                                                                                                                                                        
        managed = False
        
        db_table = 'accounts'
....


Environment:


Request Method: GET
Request URL: http://localhost:port/admin/hede/accounts/

Django Version: 1.11.23
Python Version: 2.7.15
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'hede']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/exception.py" in inner
  41.             response = get_response(request)

File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/options.py" in wrapper
  552.                 return self.admin_site.admin_view(view)(*args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapped_view
  149.                     response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/views/decorators/cache.py" in _wrapped_view_func
  57.         response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/sites.py" in inner
  224.             return view(request, *args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapper
  67.             return bound_func(*args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapped_view
  149.                     response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in bound_func
  63.                 return func.__get__(self, type(self))(*args2, **kwargs2)

File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/options.py" in changelist_view
  1564.                 self.list_max_show_all, self.list_editable, self,

File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/views/main.py" in __init__
  79.         self.get_results(request)

File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/views/main.py" in get_results
  177.         result_count = paginator.count

File "/usr/local/lib/python2.7/dist-packages/django/utils/functional.py" in __get__
  35.         res = instance.__dict__[self.name] = self.func(instance)

File "/usr/local/lib/python2.7/dist-packages/django/core/paginator.py" in count
  79.             return self.object_list.count()

File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py" in count
  364.         return self.query.get_count(using=self.db)

File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.py" in get_count
  499.         number = obj.get_aggregation(using, ['__count'])['__count']

File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.py" in get_aggregation
  480.         result = compiler.execute_sql(SINGLE)

File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py" in execute_sql
  899.             raise original_exception

Exception Type: OperationalError at /admin/hede/accounts/
Exception Value: no such table: accounts

Covalence

Answer 1

To use multiple databases you have to tell Django about the database server you will be using, but adding them in the settings.py.

Multiple databases

    'default': {
        'NAME': 'app_data',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': 'postgres_user',
        'PASSWORD': 's3krit'
    },
    'users': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'priv4te'
    }
}

The migrate management command operates on one database at a time. By default, it operates on the default database, but by providing the --database option, you can tell it to synchronize a different database.

$ ./manage.py migrate --database=users
$ ./manage.py migrate --database=customers

You can manually select the database in your queries queries e.g

user = User(....)
user.save(using='users')

Customer.objects.all().using('users')

Using raw cursor

with connections['users'].cursor() as cursor:
     cursor.execute("SELECT * FROM users__users")

Carce

I think that in the example of getting all objects Customer.objects.all().using('customers') should actually be Customer.objects.all().using('users') – Edwards

That's true @BrankoRadojevic, I have updated the answer. Thank you – Carce

Answer 2

As far as I know, your models can see the database, but aren't using 'accounts' database because it's set to use 'default' db. You can change this by creating custom database router.

Here, I am assuming that your directory structure for django project is:

    ./mainapp/
    |--- mainapp/
    |    |--- settings.py
    |    |___ # .....
    |
    |--- app/
    |    |--- apps.py
    |    |--- dbrouters.py # [new file], we will be creating soon.
    |    |--- models.py
    |    |--- views.py
    |    |___ # .....
    |
    |--- manage.py
    |___ # .....

Full example:

    # app/models.py:
    class Accounts (models.Model):
       # .....
    
    # app/dbrouters.py: # [new file]
    from .models import Accounts
    
    class AccountsDBRouter:
       def db_for_read (self, model, **hints):
          if (model == Accounts):
             # your model name as in settings.py/DATABASES
             return 'accounts'
          return None
       
       def db_for_write (self, model, **hints):
          if (model == Accounts):
             # your model name as in settings.py/DATABASES
             return 'accounts'
          return None
    
    # mainapp/settings.py:
    # .....
    
    INSTALLED_APPS = [
       'app.apps.AppConfig',
       # .....
    ]
    
    # .....
    
    DATABASE_ROUTERS = (
       'app.dbrouters.AccountsDBRouter',
    )
    
    DATABASES = {
       # .....
       'accounts' : {
          # .....
       },
    }
    
    # .....

Change the above code as per your requirement or specifications.

Then, do the following:

$ python3 manage.py makemigrations
$ python3 manage.py migrate --database=accounts
$ python3 manage.py migrate

Now, you should be able to use it without using('accounts') in code as follows:

    # app/views.py:
    # .....
    from .models import Accounts
    
    def someview (request):
       accounts = Accounts.objects.all()
       # See, in above line, you're not using:
       # accounts = Accounts.objects.using('accounts').all()
       # as your 'AccountsDBRouter' is already routing read and write
       # db requests to 'accounts' database.
       # Though you can still use "using('accounts')", as it will
       # also provide same results.
    
    def someotherview (request):
       # You can even use the following:
       account = Account(
          email="<email>",
          phone="<phone>",
          # .....
       )
       account.save()
       # Again, this will save directly in 'accounts'.

References:

• DBRouter and code - https://stackoverflow.com/a/47896031
• DBRouters django docs - https://docs.djangoproject.com/en/3.2/topics/db/multi-db/#using-routers
• DBRouters methods django docs - https://docs.djangoproject.com/en/3.2/topics/db/multi-db/#database-routers

Hope that answers you. :)

Whitener

Answer 3

You have to route queries to the database too (emphasis is mine):

The easiest way to use multiple databases is to set up a database routing scheme. The default routing scheme ensures that objects remain ‘sticky’ to their original database (i.e., an object retrieved from the foo database will be saved on the same database).

The default routing scheme ensures that if a database isn’t specified, all queries fall back to the default database.

https://docs.djangoproject.com/en/2.1/topics/db/multi-db/#automatic-database-routing

NB: the db_table in your model's Meta is for the name of the database table, not the name of the database itself !-)

Jeffereyjefferies

Err... that's the section just below: docs.djangoproject.com/en/2.1/topics/db/multi-db/#using-routers – Jeffereyjefferies

1/ define a router class that routes queries to the proper database for your account model (cf the doc for how to do so), 2/ declare the path (python qualified name in the ` app.module.RouterClass` form) in your settings. – Jeffereyjefferies