how to use after_request in flask to close database connection and python?

Question

How to use after_request decorator to close a connection after the request is processed? I use the before_request for opening the connection for each api request as follows: Using sqlalchemy core 1.0.8 and postgresql 9.5:

#engine = create_engine(os.environ.get("DB_URL"))
DB_URL="postgresql://mowner:passwd@localhost/mydb"

@app.before_request
def before_request():
    engine = create_engine(DB_URL, strategy='threadlocal')
    conn = engine.connect()


@app.after_request
def after_request(conn):
     if conn is not None:
         print 'closing connection'
         conn.close()

sample api call:

@app.route('/api/v1.0/categories', methods=['GET'])
def categories_list():
    '''
    Return categories list
    '''
    if 'id' in session:
        categories_list = []
        s = select([categories])
        rs = conn.execute(s)
        if rs.rowcount > 0:
            for r in rs:
                categories_list.append(dict(r))
            rs.close()
        # print 'this doesnt execute'
        return jsonify({'categories list': categories_list}), 200

    return jsonify({'message': "UNAUTHORIZED"}), 401

The views are api calls which only return a list of objects, added, or edit object and a message. How exactly to pass the connection object to the after_request decorator? I couldn't really follow the documentation Exact code will help me.

Using conn inside categories_list doesn't throw any error? — Sohaib Farooqi
– Sohaib Farooqi, Commented Dec 29, 2017 at 10:42
@GarbageCollector there is a before_request, it didn't besides I have used it in init.py — user956424
– user956424, Commented Dec 29, 2017 at 10:43
You can use flask.g to create a global database connection object. — Sohaib Farooqi
– Sohaib Farooqi, Commented Dec 29, 2017 at 10:45
I think I found something here :stackoverflow.com/questions/16311974/… — user956424
– user956424, Commented Dec 29, 2017 at 10:47

user956424 · Accepted Answer · 2019-03-08 07:26:21Z

6

You can use flask.g to refer to create a global db connection object and use it everywhere

from flask import Flask, g

#engine = create_engine(os.environ.get("DB_URL"))
DB_URL="postgresql://mowner:passwd@localhost/mydb"

@app.before_request
def before_request():
   engine = create_engine(DB_URL, strategy='threadlocal')
   conn = engine.connect()
   g.db = conn

Then use connection in your route like this

@app.route('/api/v1.0/categories', methods=['GET'])
def categories_list():
    '''
    Return categories list
    '''
    if 'id' in session:
        categories_list = []
        s = select([categories])
        rs = g.db.execute(s) # change in variable
        if rs.rowcount > 0:
           for r in rs:
             categories_list.append(dict(r))
        rs.close()
        # print 'this doesnt execute'
       return jsonify({'categories list': categories_list}), 200
    return jsonify({'message': "UNAUTHORIZED"}), 401

Then finally close it like this:

@app.after_request
def after_request(response):
    if g.db is not None:
        print 'closing connection'
        g.db.close()
     return response

edited Mar 8, 2019 at 7:26

user956424

1,6192 gold badges38 silver badges75 bronze badges

answered Dec 29, 2017 at 11:02

Sohaib Farooqi

5,6944 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddhartha Over a year ago

One catch with this is that g cannot be used outside of the request context, say if you want to spin up an async thread after returning a response, then the thread can't work with the db this way.

Collectives™ on Stack Overflow

how to use after_request in flask to close database connection and python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related