Using Flask-SQLAlchemy with Flask Applications on RStudio Connect

Beginning in RStudio Connect 1.8.2+, it's possible to deploy and manage runtime settings for Flask applications.

Not all data science application requires full create, read, update, and destroy (C.R.U.D.) operations. However, many use cases require some level of data access. The Flask-SQLAlchemy extension extends the capability of SQLAlchemy within a Flask application by leveraging the Flask extension interface.

This example will step through a generic setup of access to local and remote data using the Flask-SQLAlchemy extension by:

Creating a minimal application based on the Flask-SQLAlchemy quickstart guide
Define the database model to use
Initialize a SQLite database
Commit data to the database
Deploy the application to RStudio Connect with rsconnect-python

And, optionally:

Switch to a PostgreSQL database server
Add pyscopg2 dependency to the Python environment
Add the database server connection string as an environment variable in RStudio Connect
Redeploy the application

Requirements

For this example to work, the prerequisite configurations and setup include:

A working installation of Python, Flask, and required dependencies on the machine used for development. This includes the installation and configuration of rsconnect-python using an RStudio Connect API key.
A running instance of RStudio Connect 1.8.2+ configured for use with Python.

`rsconnect-python`

If needed, rsconnect-python is available on PyPI. To install with pip, run the following command:

pip install rsconnect-python

Example application

The example application is the minimal Flask example that will leverage an index template and also a remote database query.

The initial structure of the application:

 .
 ├─ templates
 │  └─ index.html
 ├─ app.py
 └─ .gitignore

Add the following snippet to app.py:

# app.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
import os

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

# set the database to be use
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URI') or \
 'sqlite:///' + os.path.join(basedir, 'users.db')

app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI

# initialize the extension
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String, unique=True, nullable=False)


@app.route("/")
def index():
    users = User.query.all()
    return render_template('index.html', users=users)

The basedir definition and SQLALCHEMY_DATABASE_URI work in conjunction to determine:

An environment variable, DATABASE_URI, exists, or
Set the SQLALCHEMY_DATABASE_URI to use a SQLite database in the current working directory.

And in index.html:

<!-- index.html -->
<html>
 <head>
 </head>
 <body>
   <div>
     {% for user in users %}
       Hello, {{ user.username }} from RStudio Connect! <br />
     {% endfor %}
   </div>
 </body>
</html>

This template loops through the received users data and displays a message for each record.

Initializing the database

For a successful database connection, the database must exist. These steps will assume that your working environment is configured to work with the database connection strings.

The database, and tables, need to exist to use the database in the above application. Once the above app.py file is saved, open a Python shell in the environment that is used to test the application. This environment should be where the dependencies are installed.

Note: If there are errors associated with unknown tables or database, it's likely that the database and tables need to be created in the target database.

Create the database

Within the Python REPL, import the database and User model from the app:

>>> from app import db, User

Then create all the tables in this database. Right now, there is only one table - the Users table.

>>> db.create_all()

This will create the database.

  .
  ├─ templates
  │  └─ index.html
  ├─ app.py
  ├─ .gitignore
+ └─ users.db

Now records can be committed to the table. We'll create a single row as and example with a username and email.

>>> db.session.add(User(username="Flask", email="example@example.com"))

Commit the data:

>>> db.session.commit()

Now exit the Python REPL and run the application locally. The data should into the index.html template that is rendered when the base route (/) is accessed.

Deploying the application

We'll deploy the app right from the terminal using rsconnect-python.

Before deployment, it's recommended that a requirements.txt file is created in the working environment. This file will list all the Python packages that exist in the environment. It's good practice to validate this environment before deployment. If the file doesn't exist, rsconnect-python will create the file during deployment.

pip freeze > requirements.txt

The directory before deployment will be structured as below:

  .
  ├─ templates
  │  └─ index.html
  ├─ app.py
  ├─ .gitignore
  ├─ users.db
+ └─ requirements.txt

Now, deploy the application using the rsconnect-python CLI.

rsconnect deploy api . -n <server-name> templates/* users.db

The rsconnect deploy api command instructs RStudio Connect that this is a Flask application. The <server-name> placeholder is a reference to the target RStudio Connect server. Additionally, the templates directory and the SQLite database, users.db is sent to the server.

The database (and database tables) must be created for the application to work with the Flask-SQLAlchemy extension when the application is started. The initial database structure must be deployed alongside the application when using SQLite. This is not the case when using a remote database server (below).

Extending with more data

Following the process above, open the Python REPL again in the local development environment and add another row into the database.

Since the database and table already exist, it doesn't need to be created again:

>>> from app import db, User
>>> db.session.add(User(username="User 1", email="user1@example.com"))
>>> db.session.commit()

Reloading the application should now display a line for each database record.

To deploy the updated application, use the same rsconnect-python command as before:

rsconnect deploy api . -n <server-name> templates/* users.db

Applying database migrations to a deployed SQLite database

Download the application from the source versions in RStudio Connect
Apply the database changes/migrations to the downloaded database version
Test the changes locally
Redeploy the application with the updated SQLite database file

Switching to a database server

The above example uses a SQLite database to manage and display data within the application. The accepted databases and connection string patterns for SQLAlchemy are documented here.

The URI formats follow the below structure:

dialect+driver://username:password@host:port/database

Be aware that the database and table create in the previous section will need to be created again in the database server. The same Python REPL commands are required.

Use with PostgreSQL (optional)

If working with remote database server, the above steps can be followed and adjusted for the database that is used.

Following the SQLAlchemy and Flask-SQLAlchemy documentation, add the PostgreSQL dialect library, pyscopg2, into the Python environment that is being used.

pip install pyscopg2

Note, the above command may differ if a package manager other than pip is used.

Adding environment variables

Once the application is deployed, it will error out when visited in RStudio Connect. The reason is that the application expects the environment variable DATABASE_URI. Add the appropriate connection string as an Environment Variable in the settings pane for the application and confirm. The addition of this variable will automatically restart the application.

# app.py
...

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URI') or \
 'sqlite:///' + os.path.join(basedir, 'users.db')

...

The above snippet in the app.py file looks for an environment variable by the name of DATABASE_URI. If it does not exist then the SQLite database is used.

The application will throw an error if neither of these options returns a database URI. Also, the database and table(s) must exist.

After the environment variable is saved, refresh the page, and the correct data should show.

Support