Django Multiple Databases - One not always available

Question

I am developing a Django application which will use multiple database backends. I would like to put an sqlite database on the machine running the django application, and sync to a remote mysql database. The tricky part is that this machine running the application will not always have an internet connection, so the mysql database is not always availalble. There will be multiple machines running the application, each with it's own local sqlite DB, but all using the same remote mysql DB.

I haven't written the code yet, but here is what I have in mind. Every time I run an insert or update I would like to write it to both databases, unless the remote DB is unavailable, in which case I will save the sql statement in a table on the local database to run when the remote DB is available.

Can this be done with database routers, or do I need to manually implement this with each db statement?

Note on PK: Not directly related, but sure to be asked. The primary key will be generated locally on each machine. In the mysql DB there will be a field for this key, and a field with a unique identifier for each instance of the application, which together will provide a unique key.

Answer 1

Suppose you have a model called Blog then you can use the following to store it locally and remotely (assuming that you have configured access to the remote db).

blog = Blog('test') 
blog.save() #Assuming that sqlite is the default db
try:
    blog.save(using='mysql')
except NoInternetConnection:
    pass

Make sure you have defined and configured 'mysql' in settings.py and that you handle the cases when there is no Internet connection.

Side note: I am not quite sure why you actually would want to do this. If this is for backup purposes then I would use standard backup procedures. For more information about using multiple databases see: http://docs.djangoproject.com/en/dev/topics/db/multi-db/#using-raw-cursors-with-multiple-databases

Answer 2

I took DrDee's code and attached it to the post_save signal (+1 for the help).

@receiver(models.signals.post_save) #new signal decorator in Django 1.3
def save_to_remote(sender,instance,using,**kwargs):
    if using == 'default' and instance.__module__ == 'mymodel.myapp.models':
        try:
            instance.save(using='remote')
        except:
            pending_instance=Pending_Remote(
                                            pk_default=instance.pk,
                                            model_default=instance.__class__.__name__
                                            )
            pending_instance.save()

This also saves a record of what was not saved to the remote database. Note that the model Pending_Remote must not be in 'myapp'.