db2

Adding command-line hooks to Flask

Running psql from the command-line can be clunky, especially if the psql command isn't installed on the computer in question. A more common way to initialize the database is to add a command-line hook into the Flask app's Python code.

Let's get started by creating the Python code for the app. Inside the blog folder, make a new Python file called blog.py (this is the file that will hold all of our Python code). Your directory structure should now be:

blog/
  +--- blog.py
  +--- schema.sql
  +--- templates/

Inside blog.py, put the code below CHANGING THE IP_ADDR VARIABLE:

# Blog Flask application

import os
import psycopg2
import psycopg2.extras
from flask import Flask, request, render_template, g

# PostgreSQL IP address
IP_ADDR = "YOUR.IP.ADDRESS"

# Create the application
app = Flask(__name__)

#####################################################
# Database handling 
  
def connect_db():
    """Connects to the database."""
    debug("Connecting to DB.")
    conn = psycopg2.connect(host=IP_ADDR, user="postgres", password="rhodes", dbname="blogdb", 
        cursor_factory=psycopg2.extras.DictCursor)
    return conn
    
def get_db():
    """Opens a new database connection if there is none yet for the
    current application context.
    """
    if not hasattr(g, 'pg_db'):
        g.pg_db = connect_db()
    return g.pg_db
    
@app.teardown_appcontext
def close_db(error):
    """Closes the database automatically when the application
    context ends."""
    debug("Disconnecting from DB.")
    if hasattr(g, 'pg_db'):
        g.pg_db.close()

######################################################
# Command line utilities 
        
def init_db():
    db = get_db()
    with app.open_resource('schema.sql', mode='r') as f:
        db.cursor().execute(f.read())
    db.commit()

@app.cli.command('initdb')
def init_db_command():
    """Initializes the database."""
    print("Initializing DB.")
    init_db()
    
    
#####################################################
# Debugging

def debug(s):
    """Prints a message to the screen (not web browser) 
    if FLASK_DEBUG is set."""
    if app.config['DEBUG']:
        print(s)

Let's go through this section by section.

What is the g object?

Look at the import Flask line; we are now importing something called g. What is that?

Remember how multiple users may be accessing your Flask app simultaneously? The g object is a place to store "global-ish" variables. Global variables are the usual mechanism in programming languages to have a variable be shared among all your functions without having to explicitly pass it as an argument. Flask applications that use databases typically want all functions to be able to share the same database connection object, to prevent having to open and close the database repeatedly. However, if multiple users are accessing the app at once, each one needs their own database connection! So we need some sort of variable that is a compromise between a global variable (which all website users would share, which we can't use for database connections), and a local variable (which all website users would have their own copy of, but then we have to pass it around as an argument to every function, which gets old). Therefore, Flask apps have a special object called g (for global) that allows you to store variables that are global only to the current website user. In the Flask documentation, this is sometimes called the application context.

Database handling section of the code

Here we create three functions.

• connect_db() is in charge of explicitly making a connection to our blogdb PostgreSQL database. This function gets the folder (path) our application lives in, connects to the blogdb database, and returns a connection object. Your Flask code will never call this function directly. PostgreSQL connection objects are a part of a Python library called psycopg2, you can read about them here if you want more information: http://initd.org/psycopg/docs/usage.html

• get_db() is the function your application code will use to retrieve the database connection object. You can see how the code asks the g object if it already has an attribute representing the database connection. If it doesn't, it explicitly connects to the database.

• close_db() is the function that closes the database. Just like files, databases should be properly opened and closed (or connected to and disconnected from). The only new part of this code is the @app.teardown_appcontext part. This is another kind of decorator Flask has; it tells Flask to automatically run this function when the application context is being "torn down," which happens at the end of a webpage request. So the effect is that the database connection is automatically closed at the end of every individual webpage request.

Note that I added a debug() function here so you can see in the terminal when the database is opened/closed.

Also note that Python uses triple quotes (""") for creating documentation strings. If your function begins with a triple-quoted string, that string can be automatically used in IDEs or documentation guides to show what a function does.

Command line section of the code

Here we write two functions that interact with the database:

• init_db() is called to initialize the database by running our code in schema.sql. The funky syntax (the with part) is used to make sure we open and close schema.sql correctly. db.commit() is used to commit the changes to the database. This function must be always be called for any database operation that changes the database, or else the changes will not take effect.

• init_db_command() just calls init_db(), but we link it to the command line with the @app.cli.command('initdb') decorator. cli stands for command-line interface. This allows you to let parts of your Flask app be run from the command line rather from the web browser --- very handy for testing.

On the command line, type the following (Windows users will use set instead of export:

export FLASK_APP=blog.py
export FLASK_ENV=development
flask initdb

The database initializes! Note that you only have to type the export lines once per command line session.

You try it

Create a second SQL file called populate.sql with the following:

delete from entries;

insert into entries(date, title, content) values (now() - interval '10 days', 'First post!', 'This is my first post.  It is exciting!');
insert into entries(date, title, content) values (now() - interval '1 day', 'I love Flask', 'I am finding Flask incredibly fun.');
insert into entries(title, content) values ('Databases', 'My databases class is a lot of work, but I am enjoying it.');

This SQL code uses the INSERT SQL statement, which adds a row to a table. You can see how it works pretty easily by looking at the code. The only hard part is the timestamp computations, which are specific to PostgreSQL---this code does date and time calculations; we use it here to have our blog post entries be dated 10 days ago, 1 day ago, and the current timestamp from whenever you run the SQL statements.

Furthermore, also notice that we are only inserting into the date, title, and content attributes---we left out id! PostgreSQL has a special feature that if you have an SERIAL attribute, that attribute can be generated for you automatically in an INSERT statement if you leave it out.

The purpose of populate.sql is to have some sample data in our database to get started with. It is common to include some kind of script to automatically populate the database with some startup data for testing.

What you should do: To the command-line area of blog.py, following the model of init_db() and init_db_command(), add two functions called populate_db() and populate_db_command() that will populate the database with the sample data in populate.sql. When you're all done, you should be able to run flask populate from the command line.

Verify (using sqlite3 or the graphical SQLite browser) that your entries table now has three rows.

If you get stuck, scroll down for the solution: .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

def populate_db():
    db = get_db()
    with app.open_resource('populate.sql', mode='r') as f:
        db.cursor().execute(f.read())
    db.commit()

@app.cli.command('populate')
def populate_db_command():
    """Populates the database with sample data."""
    print("Populating DB with sample data.")
    populate_db()

Verify the data has been added by using either psql or the graphical pgAdmin tool to look at the entries table.