Creating a sqlite database connection for a flask app

Question

I am working on a web back-end which reads from a database file, processes the data and returns a json object. I am not really informed about flask and the way the variables life in a flask app.

As you can see below, i am calling the flaskApp from a wsgi file. I created the "get_db()" function according to the flask documentation, but there is no improvement by using this function.

Is there a way to connect to the database only once and not every time the URL is called?

#file flaskApp.py
#!/usr/bin/env python3.6
from flask import Flask
...

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect("database.db")
    return g.db

app = Flask(__name__)

@app.route('/getResourceUsage/<string:buildingNumber>')
def getResource(buildingNumber):
    cursor = get_db().cursor()

    cursor.execute("SELECT * FROM room WHERE building='" + buildingNumber + "'")
    ...
    return json

#file wsgi.py
#!/usr/bin/env python3.6

from flaskApp import app

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=40800)

Orestis Zekai · Accepted Answer · 2019-11-15 12:15:14Z

1

You can do it like this:

#file flaskApp.py
#!/usr/bin/env python3.6
from flask import Flask
...


app = Flask(__name__)

g.db = sqlite3.connect("database.db")
cursor = get_db().cursor()

@app.route('/getResourceUsage/<string:buildingNumber>')
def getResource(buildingNumber):
    cursor.execute("SELECT * FROM room WHERE building='" + buildingNumber + "'")
    ...
    return json

Now you can access cursor to make queries in all endpoints

answered Nov 15, 2019 at 12:15

Orestis Zekai

9221 gold badge15 silver badges31 bronze badges

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

2 Comments

J.Doe Over a year ago

And this will not recreate a new database connection on every url call?

Orestis Zekai Over a year ago

No. It will create just one

Collectives™ on Stack Overflow

Creating a sqlite database connection for a flask app

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related