Saturday, 10 November 2018

Caching/reusing a DB connection for later view usage

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.



from Caching/reusing a DB connection for later view usage

No comments:

Post a Comment