CODEWITHSUNDEEP
pythonmysqlflask
Giri Annamalai M
Giri Annamalai M

Reputation: 809

Flask - prevent multiple open mysql connections?

I have created a flask app with mysql as backend. To initiate DB connection, I have used the following:

@app.before_request
def db_connect():    
    try:
        g.db = MySQLdb.connect(host="localhost", user="root", passwd="", db="ddb",cursorclass=MySQLdb.cursors.DictCursor,autocommit=True)        
    except Exception as e:                
        print(e)

@app.teardown_request
def db_disconnect(exception=None):
    try:
        if g.db is not None:
            g.db.close()
    except Exception as e:
        print(e)

Load this in the init_py. But I have checked how many time this mysql is opening and close, Its lot. Because the connection is open and close, even if a css/js file request calls. I calls the g.db in all functions. How do avoid this?

Upvotes: 1

Views: 1587

Answers (1)

Roelant
Roelant

Reputation: 5119

Good question. Basically, the code you provided starts a db connection before each request. Each visited url is one request - including retrieved images or stylesheets -, so that's quite often.

What to do?

  • This snippet might be a bit more efficient, but will still create a database at each request. You could check it out.

  • Its advisable to route directly to your statics. That will remove a lot of request when the database connection for sure is not required. There are some options to do this with flask itself - see this post. I personally use flask + WSGI, and add this little script above the line WSGIDaemonProcess in my httpd.conf file.

    Alias "/static/" "/home/user/webapps/flask_app/flask_app/static/"
<Directory "/home/user/webapps/flask_app/flask_app/static/">
      Require all granted
</Directory>

  • Do all your views need the db? Otherwise only get the connection on the required views. 

    from functools import wraps
from flask import g

def make_db_connection(func): 
    """ decorate views that need a database connection """
    @wraps(func)
    def decorated_function(*args, **kwargs):
         if 'db' not in g:
             g.db = connect_to_database()
         return func(*args, **kwargs)
     return decorated_function

@app.teardown_appcontext
def teardown_db():
    """ close if opened """
    db = g.pop('db', None)
    if db is not None:
        db.close()

  • You can make a small class / API around your database and cache the most common request. Only works for select of course. See this pretty project.

    class Database():

     @threaded_cached_property_with_ttl(ttl=60*60)  # in seconds 
     def some_popular_query():
          connection = MySQLdb.connect(host="localhost", user="", passwd="", db="")
          return connection.get_query()

Illustration

To illustrate, Let's say we have a simple website with template/main.html.

<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"</script>
    <script src="static/style/test.css"></script>
</head>
<body> 
    {{message}}
    <img src="static/img/test.png' />
</body>

And index.py

 from flask import Flask
 app = Flask(__name__)

 @app.route('/')
 def hello_world():
     return render_template('index.html', message='Hello world')

With the flask site you describe, you will have one request for the page and template, another request for the stylesheet and another one for the images. That's 3 calls to your database!

Upvotes: 2

Related Questions