db1

Intro to using databases with Flask

Python already includes packages for using pretty much every major DBMS in your Python code. We will illustrate using PostgreSQL to build a simple blogging application.

Log into GCP and get back into the linux shell.

• Go to console.cloud.google.com/home/dashboard.
• Click the Activate Cloud Shell icon in the upper right corner. (Looks like a greater-than sign and an underscore).
• Then click the pencil icon do open the code editor. You should have access now to your linux machine and your code editor.

Set up the directories.

Create a new directory inside flask-apps for the blog application. Issue the following commands:

• cd (return to your home directory)
• cd flask-apps (change to the flask-apps directory)
• mkdir blog (make a directory for the blog application)
• mkdir blog/templates (make a sub-directory for the HTML templates)

Setup a new SQL database on your PostgreSQL server.

A single PostgreSQL server can handle multiple databases. Let's create a database called "blogdb." First, we will need the IP address of our PostgreSQL server. This probably flashed by when you logged in, or you can get it with the command:

echo $POSTGRESQL_IP

An IP address will be four numbers separated by periods, and uniquely identifies a computer connected to the internet (with some caveats that aren't important here.)

We will use the command createdb to create a database on our PostgreSQL server:

createdb -h YOUR.IP.ADDRESS -U postgres blogdb (the -h option specifies the server to connect to [the hhost], and the -U option specifies our username to use, which is postgres)

You will be prompted for the PostgreSQL server password, which we set up to be rhodes.

If the command completes with no errors, you succeeded! To prove it to yourself, you can use the graphical pgAdmin tool to connect to your new server and see it:

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. Inside your blog folder, create a 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

• 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 if we don't specify it; in this case, it means that when we add an entry to this table, the date will be filled in automatically for us.

Creating the empty database from the command line

To connect to our PostgreSQL server, we can use the psql command. Try typing the following:

• cd To return to our home directory
• cd flask-apps/blog To get into our blog directory, where the schema.sql file is.

Now type:

psql -h YOUR.IP.ADDRESS -U postgres -d blogdb -f schema.sql (the -d option at the end specifies the database name to use)

If you see DROP TABLE and CREATE TABLE and maybe a message about NOTICE, then everything worked!

What this command did is sent the SQL code in the schema.sql file to your PostgreSQL server and ran it.

Let's connect to our server to verify it. Type:

psql -h YOUR.IP.ADDRESS -U postgres -d blogdb

You will be presented with a different command prompt that looks like blogdb=#. From here, you can type SQL commands. Try typing:

SELECT * from entries; <----note the semicolon

You will get nothing back, because of course there are no entries yet. So let's put one in. Type:

INSERT INTO entries(title, content) VALUES('My first blog entry', 'This is some great content!'); (note the semicolon at the end --- if you forget the semicolon, type it manually and press ENTER)

You should see INSERT 0 1. If you do, your insert succeeded!

Now let's try retrieving it:

Type: SELECT * from entries; Again, note the semicolon!

 id |             date              |        title        |           content
----+-------------------------------+---------------------+-----------------------------
  1 | 2019-10-09 19:18:03.215925+00 | My first blog entry | This is some great content!
(1 row)

Pretty cool, right?

Let's remove this entry now:

Type: DELETE from entries; Again, note the semicolon!

When you are done with SQL, type \q to quit the SQL program and bring you back to Linux.