PageRenderTime 44ms CodeModel.GetById 32ms app.highlight 5ms RepoModel.GetById 1ms app.codeStats 0ms

/docs/topics/db/multi-db.txt

https://code.google.com/p/mango-py/
Plain Text | 578 lines | 435 code | 143 blank | 0 comment | 0 complexity | 46582c9628a86f2b21026747c78d81a1 MD5 | raw file
  1==================
  2Multiple databases
  3==================
  4
  5.. versionadded:: 1.2
  6
  7This topic guide describes Django's support for interacting with
  8multiple databases. Most of the rest of Django's documentation assumes
  9you are interacting with a single database. If you want to interact
 10with multiple databases, you'll need to take some additional steps.
 11
 12Defining your databases
 13=======================
 14
 15The first step to using more than one database with Django is to tell
 16Django about the database servers you'll be using. This is done using
 17the :setting:`DATABASES` setting. This setting maps database aliases,
 18which are a way to refer to a specific database throughout Django, to
 19a dictionary of settings for that specific connection. The settings in
 20the inner dictionaries are described fully in the :setting:`DATABASES`
 21documentation.
 22
 23Databases can have any alias you choose. However, the alias
 24``default`` has special significance. Django uses the database with
 25the alias of ``default`` when no other database has been selected. If
 26you don't have a ``default`` database, you need to be careful to
 27always specify the database that you want to use.
 28
 29The following is an example ``settings.py`` snippet defining two
 30databases -- a default PostgreSQL database and a MySQL database called
 31``users``:
 32
 33.. code-block:: python
 34
 35    DATABASES = {
 36        'default': {
 37            'NAME': 'app_data',
 38            'ENGINE': 'django.db.backends.postgresql_psycopg2',
 39            'USER': 'postgres_user',
 40            'PASSWORD': 's3krit'
 41        },
 42        'users': {
 43            'NAME': 'user_data',
 44            'ENGINE': 'django.db.backends.mysql',
 45            'USER': 'mysql_user',
 46            'PASSWORD': 'priv4te'
 47        }
 48    }
 49
 50If you attempt to access a database that you haven't defined in your
 51:setting:`DATABASES` setting, Django will raise a
 52``django.db.utils.ConnectionDoesNotExist`` exception.
 53
 54Synchronizing your databases
 55============================
 56
 57The :djadmin:`syncdb` management command operates on one database at a
 58time. By default, it operates on the ``default`` database, but by
 59providing a :djadminopt:`--database` argument, you can tell syncdb to
 60synchronize a different database. So, to synchronize all models onto
 61all databases in our example, you would need to call::
 62
 63    $ ./manage.py syncdb
 64    $ ./manage.py syncdb --database=users
 65
 66If you don't want every application to be synchronized onto a
 67particular database, you can define a :ref:`database
 68router<topics-db-multi-db-routing>` that implements a policy
 69constraining the availability of particular models.
 70
 71Alternatively, if you want fine-grained control of synchronization,
 72you can pipe all or part of the output of :djadmin:`sqlall` for a
 73particular application directly into your database prompt, like this::
 74
 75    $ ./manage.py sqlall sales | ./manage.py dbshell
 76
 77Using other management commands
 78-------------------------------
 79
 80The other ``django-admin.py`` commands that interact with the database
 81operate in the same way as :djadmin:`syncdb` -- they only ever operate
 82on one database at a time, using :djadminopt:`--database` to control
 83the database used.
 84
 85.. _topics-db-multi-db-routing:
 86
 87Automatic database routing
 88==========================
 89
 90The easiest way to use multiple databases is to set up a database
 91routing scheme. The default routing scheme ensures that objects remain
 92'sticky' to their original database (i.e., an object retrieved from
 93the ``foo`` database will be saved on the same database). The default
 94routing scheme ensures that if a database isn't specified, all queries
 95fall back to the ``default`` database.
 96
 97You don't have to do anything to activate the default routing scheme
 98-- it is provided 'out of the box' on every Django project. However,
 99if you want to implement more interesting database allocation
100behaviors, you can define and install your own database routers.
101
102Database routers
103----------------
104
105A database Router is a class that provides up to four methods:
106
107.. method:: db_for_read(model, **hints)
108
109    Suggest the database that should be used for read operations for
110    objects of type ``model``.
111
112    If a database operation is able to provide any additional
113    information that might assist in selecting a database, it will be
114    provided in the ``hints`` dictionary. Details on valid hints are
115    provided :ref:`below <topics-db-multi-db-hints>`.
116
117    Returns None if there is no suggestion.
118
119.. method:: db_for_write(model, **hints)
120
121    Suggest the database that should be used for writes of objects of
122    type Model.
123
124    If a database operation is able to provide any additional
125    information that might assist in selecting a database, it will be
126    provided in the ``hints`` dictionary. Details on valid hints are
127    provided :ref:`below <topics-db-multi-db-hints>`.
128
129    Returns None if there is no suggestion.
130
131.. method:: allow_relation(obj1, obj2, **hints)
132
133    Return True if a relation between obj1 and obj2 should be
134    allowed, False if the relation should be prevented, or None if
135    the router has no opinion. This is purely a validation operation,
136    used by foreign key and many to many operations to determine if a
137    relation should be allowed between two objects.
138
139.. method:: allow_syncdb(db, model)
140
141    Determine if the ``model`` should be synchronized onto the
142    database with alias ``db``. Return True if the model should be
143    synchronized, False if it should not be synchronized, or None if
144    the router has no opinion. This method can be used to determine
145    the availability of a model on a given database.
146
147A router doesn't have to provide *all* these methods - it omit one or
148more of them. If one of the methods is omitted, Django will skip that
149router when performing the relevant check.
150
151.. _topics-db-multi-db-hints:
152
153Hints
154~~~~~
155
156The hints received by the database router can be used to decide which
157database should receive a given request.
158
159At present, the only hint that will be provided is ``instance``, an
160object instance that is related to the read or write operation that is
161underway. This might be the instance that is being saved, or it might
162be an instance that is being added in a many-to-many relation. In some
163cases, no instance hint will be provided at all. The router checks for
164the existence of an instance hint, and determine if that hint should be
165used to alter routing behavior.
166
167Using routers
168-------------
169
170Database routers are installed using the :setting:`DATABASE_ROUTERS`
171setting. This setting defines a list of class names, each specifying a
172router that should be used by the master router
173(``django.db.router``).
174
175The master router is used by Django's database operations to allocate
176database usage. Whenever a query needs to know which database to use,
177it calls the master router, providing a model and a hint (if
178available). Django then tries each router in turn until a database
179suggestion can be found. If no suggestion can be found, it tries the
180current ``_state.db`` of the hint instance. If a hint instance wasn't
181provided, or the instance doesn't currently have database state, the
182master router will allocate the ``default`` database.
183
184An example
185----------
186
187.. admonition:: Example purposes only!
188
189    This example is intended as a demonstration of how the router
190    infrastructure can be used to alter database usage. It
191    intentionally ignores some complex issues in order to
192    demonstrate how routers are used.
193
194    This example won't work if any of the models in ``myapp`` contain
195    relationships to models outside of the ``other`` database.
196    :ref:`Cross-database relationships <no_cross_database_relations>`
197    introduce referential integrity problems that Django can't
198    currently handle.
199
200    The master/slave configuration described is also flawed -- it
201    doesn't provide any solution for handling replication lag (i.e.,
202    query inconsistencies introduced because of the time taken for a
203    write to propagate to the slaves). It also doesn't consider the
204    interaction of transactions with the database utilization strategy.
205
206So - what does this mean in practice? Say you want ``myapp`` to
207exist on the ``other`` database, and you want all other models in a
208master/slave relationship between the databases ``master``, ``slave1`` and
209``slave2``. To implement this, you would need 2 routers::
210
211    class MyAppRouter(object):
212        """A router to control all database operations on models in
213        the myapp application"""
214
215        def db_for_read(self, model, **hints):
216            "Point all operations on myapp models to 'other'"
217            if model._meta.app_label == 'myapp':
218                return 'other'
219            return None
220
221        def db_for_write(self, model, **hints):
222            "Point all operations on myapp models to 'other'"
223            if model._meta.app_label == 'myapp':
224                return 'other'
225            return None
226
227        def allow_relation(self, obj1, obj2, **hints):
228            "Allow any relation if a model in myapp is involved"
229            if obj1._meta.app_label == 'myapp' or obj2._meta.app_label == 'myapp':
230                return True
231            return None
232
233        def allow_syncdb(self, db, model):
234            "Make sure the myapp app only appears on the 'other' db"
235            if db == 'other':
236                return model._meta.app_label == 'myapp'
237            elif model._meta.app_label == 'myapp':
238                return False
239            return None
240
241    class MasterSlaveRouter(object):
242        """A router that sets up a simple master/slave configuration"""
243
244        def db_for_read(self, model, **hints):
245            "Point all read operations to a random slave"
246            return random.choice(['slave1','slave2'])
247
248        def db_for_write(self, model, **hints):
249            "Point all write operations to the master"
250            return 'master'
251
252        def allow_relation(self, obj1, obj2, **hints):
253            "Allow any relation between two objects in the db pool"
254            db_list = ('master','slave1','slave2')
255            if obj1._state.db in db_list and obj2._state.db in db_list:
256                return True
257            return None
258
259        def allow_syncdb(self, db, model):
260            "Explicitly put all models on all databases."
261            return True
262
263Then, in your settings file, add the following (substituting ``path.to.`` with
264the actual python path to the module where you define the routers)::
265
266    DATABASE_ROUTERS = ['path.to.MyAppRouter', 'path.to.MasterSlaveRouter']
267
268The order in which routers are processed is significant. Routers will
269be queried in the order the are listed in the
270:setting:`DATABASE_ROUTERS` setting . In this example, the
271``MyAppRouter`` is processed before the ``MasterSlaveRouter``, and as a
272result, decisions concerning the models in ``myapp`` are processed
273before any other decision is made. If the :setting:`DATABASE_ROUTERS`
274setting listed the two routers in the other order,
275``MasterSlaveRouter.allow_syncdb()`` would be processed first. The
276catch-all nature of the MasterSlaveRouter implementation would mean
277that all models would be available on all databases.
278
279With this setup installed, lets run some Django code::
280
281    >>> # This retrieval will be performed on the 'credentials' database
282    >>> fred = User.objects.get(username='fred')
283    >>> fred.first_name = 'Frederick'
284
285    >>> # This save will also be directed to 'credentials'
286    >>> fred.save()
287
288    >>> # These retrieval will be randomly allocated to a slave database
289    >>> dna = Person.objects.get(name='Douglas Adams')
290
291    >>> # A new object has no database allocation when created
292    >>> mh = Book(title='Mostly Harmless')
293
294    >>> # This assignment will consult the router, and set mh onto
295    >>> # the same database as the author object
296    >>> mh.author = dna
297
298    >>> # This save will force the 'mh' instance onto the master database...
299    >>> mh.save()
300
301    >>> # ... but if we re-retrieve the object, it will come back on a slave
302    >>> mh = Book.objects.get(title='Mostly Harmless')
303
304
305Manually selecting a database
306=============================
307
308Django also provides an API that allows you to maintain complete control
309over database usage in your code. A manually specified database allocation
310will take priority over a database allocated by a router.
311
312Manually selecting a database for a ``QuerySet``
313------------------------------------------------
314
315You can select the database for a ``QuerySet`` at any point in the
316``QuerySet`` "chain." Just call ``using()`` on the ``QuerySet`` to get
317another ``QuerySet`` that uses the specified database.
318
319``using()`` takes a single argument: the alias of the database on
320which you want to run the query. For example::
321
322    >>> # This will run on the 'default' database.
323    >>> Author.objects.all()
324
325    >>> # So will this.
326    >>> Author.objects.using('default').all()
327
328    >>> # This will run on the 'other' database.
329    >>> Author.objects.using('other').all()
330
331Selecting a database for ``save()``
332-----------------------------------
333
334Use the ``using`` keyword to ``Model.save()`` to specify to which
335database the data should be saved.
336
337For example, to save an object to the ``legacy_users`` database, you'd
338use this::
339
340    >>> my_object.save(using='legacy_users')
341
342If you don't specify ``using``, the ``save()`` method will save into
343the default database allocated by the routers.
344
345Moving an object from one database to another
346~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
347
348If you've saved an instance to one database, it might be tempting to
349use ``save(using=...)`` as a way to migrate the instance to a new
350database. However, if you don't take appropriate steps, this could
351have some unexpected consequences.
352
353Consider the following example::
354
355    >>> p = Person(name='Fred')
356    >>> p.save(using='first')  # (statement 1)
357    >>> p.save(using='second') # (statement 2)
358
359In statement 1, a new ``Person`` object is saved to the ``first``
360database. At this time, ``p`` doesn't have a primary key, so Django
361issues a SQL ``INSERT`` statement. This creates a primary key, and
362Django assigns that primary key to ``p``.
363
364When the save occurs in statement 2, ``p`` already has a primary key
365value, and Django will attempt to use that primary key on the new
366database. If the primary key value isn't in use in the ``second``
367database, then you won't have any problems -- the object will be
368copied to the new database.
369
370However, if the primary key of ``p`` is already in use on the
371``second`` database, the existing object in the ``second`` database
372will be overridden when ``p`` is saved.
373
374You can avoid this in two ways. First, you can clear the primary key
375of the instance. If an object has no primary key, Django will treat it
376as a new object, avoiding any loss of data on the ``second``
377database::
378
379    >>> p = Person(name='Fred')
380    >>> p.save(using='first')
381    >>> p.pk = None # Clear the primary key.
382    >>> p.save(using='second') # Write a completely new object.
383
384The second option is to use the ``force_insert`` option to ``save()``
385to ensure that Django does a SQL ``INSERT``::
386
387    >>> p = Person(name='Fred')
388    >>> p.save(using='first')
389    >>> p.save(using='second', force_insert=True)
390
391This will ensure that the person named ``Fred`` will have the same
392primary key on both databases. If that primary key is already in use
393when you try to save onto the ``second`` database, an error will be
394raised.
395
396Selecting a database to delete from
397-----------------------------------
398
399By default, a call to delete an existing object will be executed on
400the same database that was used to retrieve the object in the first
401place::
402
403    >>> u = User.objects.using('legacy_users').get(username='fred')
404    >>> u.delete() # will delete from the `legacy_users` database
405
406To specify the database from which a model will be deleted, pass a
407``using`` keyword argument to the ``Model.delete()`` method. This
408argument works just like the ``using`` keyword argument to ``save()``.
409
410For example, if you're migrating a user from the ``legacy_users``
411database to the ``new_users`` database, you might use these commands::
412
413    >>> user_obj.save(using='new_users')
414    >>> user_obj.delete(using='legacy_users')
415
416Using managers with multiple databases
417--------------------------------------
418
419Use the ``db_manager()`` method on managers to give managers access to
420a non-default database.
421
422For example, say you have a custom manager method that touches the
423database -- ``User.objects.create_user()``. Because ``create_user()``
424is a manager method, not a ``QuerySet`` method, you can't do
425``User.objects.using('new_users').create_user()``. (The
426``create_user()`` method is only available on ``User.objects``, the
427manager, not on ``QuerySet`` objects derived from the manager.) The
428solution is to use ``db_manager()``, like this::
429
430    User.objects.db_manager('new_users').create_user(...)
431
432``db_manager()`` returns a copy of the manager bound to the database you specify.
433
434Using ``get_query_set()`` with multiple databases
435~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
436
437If you're overriding ``get_query_set()`` on your manager, be sure to
438either call the method on the parent (using ``super()``) or do the
439appropriate handling of the ``_db`` attribute on the manager (a string
440containing the name of the database to use).
441
442For example, if you want to return a custom ``QuerySet`` class from
443the ``get_query_set`` method, you could do this::
444
445    class MyManager(models.Manager):
446        def get_query_set(self):
447            qs = CustomQuerySet(self.model)
448            if self._db is not None:
449                qs = qs.using(self._db)
450            return qs
451
452Exposing multiple databases in Django's admin interface
453=======================================================
454
455Django's admin doesn't have any explicit support for multiple
456databases. If you want to provide an admin interface for a model on a
457database other than that specified by your router chain, you'll
458need to write custom :class:`~django.contrib.admin.ModelAdmin` classes
459that will direct the admin to use a specific database for content.
460
461``ModelAdmin`` objects have five methods that require customization for
462multiple-database support::
463
464    class MultiDBModelAdmin(admin.ModelAdmin):
465        # A handy constant for the name of the alternate database.
466        using = 'other'
467
468        def save_model(self, request, obj, form, change):
469            # Tell Django to save objects to the 'other' database.
470            obj.save(using=self.using)
471
472        def delete_model(self, request, obj):
473            # Tell Django to delete objects from the 'other' database
474            obj.delete(using=self.using)
475
476        def queryset(self, request):
477            # Tell Django to look for objects on the 'other' database.
478            return super(MultiDBModelAdmin, self).queryset(request).using(self.using)
479
480        def formfield_for_foreignkey(self, db_field, request=None, **kwargs):
481            # Tell Django to populate ForeignKey widgets using a query
482            # on the 'other' database.
483            return super(MultiDBModelAdmin, self).formfield_for_foreignkey(db_field, request=request, using=self.using, **kwargs)
484
485        def formfield_for_manytomany(self, db_field, request=None, **kwargs):
486            # Tell Django to populate ManyToMany widgets using a query
487            # on the 'other' database.
488            return super(MultiDBModelAdmin, self).formfield_for_manytomany(db_field, request=request, using=self.using, **kwargs)
489
490The implementation provided here implements a multi-database strategy
491where all objects of a given type are stored on a specific database
492(e.g., all ``User`` objects are in the ``other`` database). If your
493usage of multiple databases is more complex, your ``ModelAdmin`` will
494need to reflect that strategy.
495
496Inlines can be handled in a similar fashion. They require three customized methods::
497
498    class MultiDBTabularInline(admin.TabularInline):
499        using = 'other'
500
501        def queryset(self, request):
502            # Tell Django to look for inline objects on the 'other' database.
503            return super(MultiDBTabularInline, self).queryset(request).using(self.using)
504
505        def formfield_for_foreignkey(self, db_field, request=None, **kwargs):
506            # Tell Django to populate ForeignKey widgets using a query
507            # on the 'other' database.
508            return super(MultiDBTabularInline, self).formfield_for_foreignkey(db_field, request=request, using=self.using, **kwargs)
509
510        def formfield_for_manytomany(self, db_field, request=None, **kwargs):
511            # Tell Django to populate ManyToMany widgets using a query
512            # on the 'other' database.
513            return super(MultiDBTabularInline, self).formfield_for_manytomany(db_field, request=request, using=self.using, **kwargs)
514
515Once you've written your model admin definitions, they can be
516registered with any ``Admin`` instance::
517
518    from django.contrib import admin
519
520    # Specialize the multi-db admin objects for use with specific models.
521    class BookInline(MultiDBTabularInline):
522        model = Book
523
524    class PublisherAdmin(MultiDBModelAdmin):
525        inlines = [BookInline]
526
527    admin.site.register(Author, MultiDBModelAdmin)
528    admin.site.register(Publisher, PublisherAdmin)
529
530    othersite = admin.Site('othersite')
531    othersite.register(Publisher, MultiDBModelAdmin)
532
533This example sets up two admin sites. On the first site, the
534``Author`` and ``Publisher`` objects are exposed; ``Publisher``
535objects have an tabular inline showing books published by that
536publisher. The second site exposes just publishers, without the
537inlines.
538
539Using raw cursors with multiple databases
540=========================================
541
542If you are using more than one database you can use
543``django.db.connections`` to obtain the connection (and cursor) for a
544specific database. ``django.db.connections`` is a dictionary-like
545object that allows you to retrieve a specific connection using its
546alias::
547
548    from django.db import connections
549    cursor = connections['my_db_alias'].cursor()
550
551Limitations of multiple databases
552=================================
553
554.. _no_cross_database_relations:
555
556Cross-database relations
557------------------------
558
559Django doesn't currently provide any support for foreign key or
560many-to-many relationships spanning multiple databases. If you
561have used a router to partition models to different databases,
562any foreign key and many-to-many relationships defined by those
563models must be internal to a single database.
564
565This is because of referential integrity. In order to maintain a
566relationship between two objects, Django needs to know that the
567primary key of the related object is valid. If the primary key is
568stored on a separate database, it's not possible to easily evaluate
569the validity of a primary key.
570
571If you're using Postgres, Oracle, or MySQL with InnoDB, this is
572enforced at the database integrity level -- database level key
573constraints prevent the creation of relations that can't be validated.
574
575However, if you're using SQLite or MySQL with MyISAM tables, there is
576no enforced referential integrity; as a result, you may be able to
577'fake' cross database foreign keys. However, this configuration is not
578officially supported by Django.