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 SQLite to build a simple blogging application.

Set up application folders

Create a new folder for your application. Call it blog. Under that folder, create a subfolder called templates for your templates.

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 and put the following in it:

drop table if exists entries;
create table entries (
  id integer primary key not null,
  date datetime not null,
  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

Creating the empty database from the command line

At the command line, type conda install sqlite. This is an Anaconda command that tells anaconda to download and install the SQLite Python API and command line tools. The tool will ask if you want to download and install these new packages; answer yes.

After the installation is done, try typing sqlite3 at the command line. If it works, the SQLite command-line browser should start. This program is similar to the graphical SQLite browser, but it's all text-based. For now, type .help to see the various commands you can use, then type .quit to exit the browser and put you back on the command line.

You can use the sqlite3 command-line browser to run SQL commands from a file. Let's do this to create our starting database. Make sure you are in the blog folder (the same folder where schema.sql is) and type:

sqlite3 blog.db < schema.sql

This tells sqlite3 to open a database called blog.db (in this case, it is created new because it doesn't exist already) and run the commands in schema.sql on it. The command will finish and dump you back to the command line. Now you can try opening blog.db in your graphical SQLite browser. If you look at the list of tables, you'll see the new entries table, though it will be empty. Running a command like SELECT * from entries will work, but you won't get any results.