Intro to using databases with Flask

So far, we have seen how to use Python with the psycopg2 library to connect to a PostgreSQL database to browse, add, edit, and delete content. Applications that do these four operations are typically called "CRUD" applications, for CREATE, READ, UPDATE, and DELETE. We have also seen how to use Flask to build a basic web app with HTML templates. Today we will put these two concepts together to build a very simple CRUD web app that lets you maintain a simple blog.

To get started

• On replit, fork the code for today (see link on class website).
• You may want to open pgAdmin and connect to your database so that you can browse the tables manually, if necessary. Remember, the login details are host=dbclass.rhodescs.org, username=[your Rhodes username], password=[same as username], database name = practice.

Make sure the web app works out-of-the-box.

• The file main.py contains a basic web app in Flask. Click the green "run" button and make sure it runs.
• The code is broken into a few sections. At the very top is a section for the "routes," which you will remember are the Python functions that are connected to URLs. Below that is a section for functions that will handle the database connection, a section for a debugging function, and a section that starts the Flask app running.

Database connection code

• The Flask app comes with some basic code for handling the database connection. Edit the DBUSER/DBPASS lines at the top to use your username.

Write SQL code for creating tables

• Typically, Flask apps contain an initialization file called schema.sql that contains SQL commands that create the database tables needed.
• Find the file called schema.sql in the code editor and put the following SQL in it:

drop table if exists entries;
create table entries (
  id serial primary key not null,
  date timestamp with time zone not null default now(),
  title varchar(80) not null,
  content text not null
);

Breaking down the SQL commands

You probably remember most of this from past lessons, but here's a refresher:

• The DROP TABLE command deletes a table from the database. All data in the table is lost forever. The IF EXISTS part makes it so we won't get an error message if the table doesn't already exist. This command is necessary because the following command, CREATE TABLE, will create the entries table from scratch.

• The CREATE TABLE command creates a table in the database. Each one of the four lines specifies an attribute in the table. The first part of the line specifies the name of the attribute (e.g., id, date, title, content), the second part specifies the datatype (serial, timestamp, varchar(80), text), and any parts after the data type are extra pieces of information. NOT NULL specifies that an attribute may not be null. PRIMARY KEY sets the attribute as the primary key of the table. The DEFAULT part specifies the default value for a column. This means that when we add data to the table using INSERT we can leave out any columns that have DEFAULT values.

• The now() part is a special function that exists in PostgreSQL. DEFAULT now() means that the default value for the date column will be the current time.

Creating the empty database from the command line

Recall that during the Python/PostgreSQL (day 1) of this lab, we wrote Python code to read in the schema.sql file and have Python execute it. The code basically looked like this:

conn = psycopg2.connect(...)
cur = conn.cursor()  # make a cursor (allows us to execute queries)

file = open("schema.sql", "r") # open the file
alltext = file.read() # read all the text
cur.execute(alltext) # execute all the SQL in the file
conn.commit()  # Actually make the changes to the db

cur.close()  
conn.close() # close everything

Because we are writing a web app now, the main.py file automatically runs the web app rather than doing anything text-based. However, Flask has a mechanism where we can run the web app from the command line and call specific functions.

Add the following code to the "database handling" section of main.py:

@app.cli.command("init")
def init_db():
    """Clear existing data and create new tables."""
    conn = get_db()
    cur = conn.cursor()
    with current_app.open_resource("schema.sql") as file: # open the file
        alltext = file.read() # read all the text
        cur.execute(alltext) # execute all the SQL in the file
    conn.commit()
    print("Initialized the database and cleared tables.")

The special part to pay attention to is the @app.cli.command("init") part. This makes it so the web app will recognize the init command on the command line.

To test it out, do the following:

• In the console area (the black-and-white text area) click "Shell" to get us to the command line.
• Type export FLASK_APP=main
  • This tells Flask which Python file holds our code (main.py)
• Type flask init. This tells Flask to run the code tagged with the @app.cli.command("init") tag, which is the init_db() function.
• The function runs, and you should see Initialized the database. Let me know if this doesn't work.