How to create new database connection in django

Question

I need to create a new database connection(session) to avoid an unexpected commit from a MySql procedure in my django transaction. How to set up it in django?

I have tried to duplicate the database configuration in setting file. It worked for me but it seems not a good solution. See my code for more detail.

@classmethod
def get_sequence_no(cls, name='', enable_transaction=False):
    """
        return the sequence no for the given key name
    """
    if enable_transaction:
        valobj = cls.objects.using('sequence_no').raw("call sp_get_next_id('%s')" % name)
        return valobj[0].current_val
    else:
        valobj = cls.objects.raw("call sp_get_next_id('%s')" % name)
        return valobj[0].current_val

Does anyone know how to use a custom database connection to call the procedure?

Answer 1

If you have a look at the django.db module, you can see that django.db.connection is a proxy for django.db.connections[DEFAULT_DB_ALIAS] and django.db.connections is an instance of django.db.utils.ConnectionHandler.

Putting this together, you should be able to get a new connection like this:

from django.db import connections
from django.db.utils import DEFAULT_DB_ALIAS, load_backend
    

def create_connection(alias=DEFAULT_DB_ALIAS):
    connections.ensure_defaults(alias)
    connections.prepare_test_settings(alias)
    db = connections.databases[alias]
    backend = load_backend(db['ENGINE'])
    return backend.DatabaseWrapper(db, alias)

Note that this function will open a new connection every time it is called and you are responsible for closing it. Also, the APIs it uses are probably considered internal and might change without notice.

To close the connection, it should be enough to call .close() on the object return by the create_connection function:

conn = create_connection()
# do some stuff
conn.close()