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?

Cosentino

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()

Dripps

Hi @Daniel, just wondering, is there a snippet anywhere for closing such a connection? Do we have to switch the connection back to the default after using this? – Magnify

@MichealJ.Roberts I've expanded my answer – Dripps

Ah, perfect! :) As easy as that, really appreciate you spending the time to expand on that. – Magnify

Answer 2

With modern Django you can just do:

from django.db import connections

new_connection = connections.create_connection('default')

Ti

Can you please elaborate? Once you have the new_connection, how do you run things using that new_connection? Using the above code gets the database but any code below it will use the default database. – Waldemar

Yea, that's how the API works. You can create a cursor from the connection, and you can execute raw SQL from that. – Ti