I'm trying to build a simple Flask web application that query a postgres db and inserting/deleting data. I'm even not sure about updating/inserting, but at minimum I'll just query a db. I don't want to use any ORM because it's quite simple. I want to use psycopg2 since I've used it before in my simple python application. Now I want to know how and when I should open and close a db connection. I've seen this: http://flask.pocoo.org/docs/0.12/tutorial/dbcon/ but it didn't find an answer in it.
Add a comment
|
1 Answer
http://flask.pocoo.org/docs/0.12/patterns/sqlite3/ provides a better example. Its for sqllite, but all that is different is how the initial connection is established. So where it uses sqllite3, put your psycopg2 calls in:
import sqlite3
from flask import g
DATABASE = '/path/to/database.db'
@app.before_request
def get_db():
db = getattr(g, '_database', None)
if db is None:
db = g._database = sqlite3.connect(DATABASE)
return db
@app.teardown_appcontext
def close_connection(exception):
db = getattr(g, '_database', None)
if db is not None:
db.close()
You can find a fully working code example showing use of the db here: https://github.com/pallets/flask/blob/master/examples/flaskr/flaskr/flaskr.py. Note that the example establishing the db connection manually per request in the @app.route methods, whereas the example above uses the @app.before_request hook to establish it before the route methods are called.
You said you didn't want an ORM, but you can also use SQLAlchemy in a non-orm fashion: https://docs.sqlalchemy.org/en/latest/core/tutorial.html. The benefit of this is that there are specific session objects for managing the db connectio: http://flask.pocoo.org/docs/0.12/patterns/sqlalchemy/
6 Comments
Jodooomi
where should
get_db be called?
user783836
The docs suggest the call should be tied to the current context, which in this case is the request, so putting the
@app.before_request attribute on the method should do it. I've updated the code sample.Jodooomi
what's the "g", is it like a session object where I can store variables?
Jodooomi
also, should I close a connection in "@app.teardown_request" or "@app.teardown_appcontext"?
user783836
g is the application context: flask.pocoo.org/docs/0.12/appcontext/#the-application-context. According to the docs app context is created before the request comes in and is destroyed (torn down) whenever the request finishes so @app.teardown_context should be the right place. |