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.
See also
Additional examples and walkthroughs of getting started with Flask application deployment on RStudio Connect:
Comments