Flask is "a lightweight WSGI web application framework. It is designed to make getting started quick and easy, with the ability to scale up to complex applications". It has become one of the most popular Python web application frameworks.

As opposed to more opinionated frameworks like Django, Flask does not include a built-in ORM or database API. Flask is commonly used with the SQLAlchemy ORM and/or DB API. However, Flask can also be used with bit.io through other popular DB APIs like psycopg2.

This guide will demonstrate configuration of Flask and SQLAlchemy for a partial CRUD app that stores and returns Marathon race data.

Connecting a Flask app to bit.io

First, if needed, install the Flask, Flask-SQLAlchemy, and psycopg2 Python packages with your preferred environment/package manager. psycopg2 is a Postgres database adapter required by SQLAlchemy for connecting to bit.io. Here is an example installing both with venv and pip:

$ python3 -m venv venv
$ source venv/bin/activate
$ python3 -m pip install --upgrade pip Flask Flask-SQLAlchemy psycopg2

As explained in the Flask SQLALchemy patterns documentation, there are multiple ways to configure a SQLAlchemy engine with a Flask app. One simple way is to add a Postgres connection string to your app configuration and then construct a SQLAlchemy integration instance as follows:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgresql://<USERNAME>:<v2_YOUR_PASSWORD_HERE>@db.bit.io/<DATABASE_NAME>'
)
# bit.io closes idle connections after 60 seconds, ensure pool_recycle is set to < 60 to handle that
db = SQLAlchemy(app, engine_options={"pool_recycle": 55})

...

🚧

bit.io connection timeouts

Connections to bit.io databases are automatically closed after a period of inactivity (currently 60 seconds). Ensure you set the SQLAlchemy engine's pool_recycle option to less than 60 seconds to avoid unexpected disconnects during request handling.

You can retrieve a pre-constructed connection string from the bit.io Connect Tab for your database.

That's it! The following section shows this connection in the context of a partial CRUD app that stores and returns marathon race data.

Using Flask with bit.io

Using the connection method shown above, we can build a very simple, partial CRUD app that stores and retrieves marathon race data. The full application code is provided below:

import json

from flask import Flask, request, Response
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    'postgresql://<USERNAME>:<v2_YOUR_PASSWORD_HERE>@db.bit.io/<DATABASE_NAME>'
)
# bit.io closes idle connections after 60 seconds, ensure pool_recycle is set to < 60 to handle that
db = SQLAlchemy(app, engine_options={"pool_recycle": 55})


# Defines a SQLAlchemy model class for marathon race records
class Marathon(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(100))
    city = db.Column(db.String(50))
    inception_year = db.Column(db.Integer)

    def __repr__(self):
        return f"Marathon {self.name}"

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


# Allow posting of marathon race records and retrieval of a list of all records
@app.route("/marathon/", methods=["GET", "POST"])
def marathon_list():
    if request.method == "POST":
        content = request.json
        new_marathon = Marathon(**content)
        db.session.add(new_marathon)
        db.session.commit()
        return Response(
            response=json.dumps({"id": new_marathon.id}),
            status=201,
            mimetype="application/json"
            )
    # Treats all other methods as an implicit 'GET' for simplicity
    else:
        marathons = Marathon.query.all()
        return {"marathon_list": [m.to_dict() for m in marathons]}

We are using a declarative approach to SQLAlchemy where the Python class Marathon generates a table schema for bit.io. To generate the initial table in bit.io, we can enter the Flask shell and use the SQLAlchemy create_all method:

$ flask shell
from app import db
db.create_all()

The table is now created in bit.io, as shown below:

11551155

You can now run the demo app in the Flask development server as follows:

flask run 

And you can test the demo app using an HTTP client like cURL:

$ curl localhost:5000/marathon/
{"marathon_list":[]}
$ curl -X POST http://localhost:5000/marathon/ \
> -H 'Content-Type: application/json' \
> -d '{"name":"Boston Marathon","city":"Boston", "inception_year": 1897}'
{"id": 4}
$ curl localhost:5000/marathon/
{"marathon_list":[{"city":"Boston","id":1,"inception_year":1897,"name":"Boston Marathon"}]}

And see the same data in bit.io.

14071407

👍

Time to Build

From here, you can learn more about Flask or SQLAlchemy through their respective tutorials, or build your own custom application.


Did this page help you?