Caching/reusing a DB connection for later view usage

Question

I am saving a user's database connection. On the first time they enter in their credentials, I do something like the following:

self.conn = MySQLdb.connect (
    host = 'aaa',
    user = 'bbb',
    passwd = 'ccc',
    db = 'ddd',
    charset='utf8'
)
cursor = self.conn.cursor()
cursor.execute("SET NAMES utf8")
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')

I then have the conn ready to go for all the user's queries. However, I don't want to re-connect every time the view is loaded. How would I store this "open connection" so I can just do something like the following in the view:

def do_queries(request, sql):
    user = request.user
    conn = request.session['conn']
    cursor = request.session['cursor']
    cursor.execute(sql)

---

Update: it seems like the above is not possible and not good practice, so let me re-phrase what I'm trying to do:

I have a sql editor that a user can use after they enter in their credentials (think of something like Navicat or SequelPro). Note this is NOT the default django db connection -- I do not know the credentials beforehand. Now, once the user has 'connected', I would like them to be able to do as many queries as they like without me having to reconnect every time they do this. For example -- to re-iterate again -- something like Navicat or SequelPro. How would this be done using python, django, or mysql? Perhaps I don't really understand what is necessary here (caching the connection? connection pooling? etc.), so any suggestions or help would be greatly appreciated.

Answer 1

You could use an IoC container to store a singleton provider for you. Essentially, instead of constructing a new connection every time, it will only construct it once (the first time ConnectionContainer.connection_provider() is called) and thereafter it will always return the previously constructed connection.

You'll need the dependency-injector package for my example to work:

import dependency_injector.containers as containers
import dependency_injector.providers as providers


class ConnectionProvider():
    def __init__(self, host, user, passwd, db, charset):
        self.conn = MySQLdb.connect(
            host=host,
            user=user,
            passwd=passwd,
            db=db,
            charset=charset
        )


class ConnectionContainer(containers.DeclarativeContainer):
    connection_provider = providers.Singleton(ConnectionProvider,
                                              host='aaa',
                                              user='bbb',
                                              passwd='ccc',
                                              db='ddd',
                                              charset='utf8')


def do_queries(request, sql):
    user = request.user
    conn = ConnectionContainer.connection_provider().conn
    cursor = conn.cursor()
    cursor.execute(sql)

I've hardcoded the connection string here, but it is also possible to make it variable depending on a changeable configuration. In that case you could also create a container for the configuration file and have the connection container read its config from there. You then set the config at runtime. As follows:

import dependency_injector.containers as containers
import dependency_injector.providers as providers

class ConnectionProvider():
    def __init__(self, connection_config):
        self.conn = MySQLdb.connect(**connection_config)

class ConfigContainer(containers.DeclarativeContainer):
    connection_config = providers.Configuration("connection_config")

class ConnectionContainer(containers.DeclarativeContainer):
    connection_provider = providers.Singleton(ConnectionProvider, ConfigContainer.connection_config)

def do_queries(request, sql):
    user = request.user
    conn = ConnectionContainer.connection_provider().conn
    cursor = conn.cursor()
    cursor.execute(sql)


# run code
my_config = {
    'host':'aaa',
    'user':'bbb',
    'passwd':'ccc',
    'db':'ddd',
    'charset':'utf8'
}

ConfigContainer.connection_config.override(my_config)
request = ...
sql = ...

do_queries(request, sql)