db2

How does the database handling work in the app?

In this section, we will investigate how Flask connects to the database, because it's a little different than writing a standalone Python project.

• Scroll down to the database section of the code, and look at the get_db() function. Notice that get_db() is the first function called by our init_db() function that we pasted in a minute ago. Read through the get_db() function, and notice how it calls connect_db() to connect to the database, then stores what that function returns in the g object. What on earth is this "g" thing?

What is the g object?

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

It is possible for a Flask app to have multiple users connect to it at once. Indeed, this is the point of many web apps: to serve multiple users/connections 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 are what the functions in this section do:

• connect_db() is in charge of explicitly making a connection to our PostgreSQL database. This function uses the psycopg2 library to connect to the database, and returns a connection object.

• 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 below 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.

Adding another database function

Create a second SQL file called populate.sql (alongside schema.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 database area of main.py, following the model of init_db() and add a function called populate_db() 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 pgAdmin) that your entries table now has three rows.

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

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

@app.cli.command('populate')
def populate_db():
    conn = get_db()
    cur = conn.cursor()
    with current_app.open_resource("populate.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("Populated DB with sample data.")

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