So we have a very basic web app in action. The next step will be to set up our database to store our data. We’re cgoing to use SQL, and particularly sqlite3 as it’s available with Python.
5. Setting up the database
5.1 The schema.sql file
The first thing that we need will be a table. SQL data is stored in tables, columns and rows. The table will be called ITEMS, and then we will have a few columns for the data for each item. The rest thing we do is create a file called schema.sql
In this file, we put:
DROP TABLE IF EXISTS items;
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
item TEXT NOT NULL,
description TEXT NOT NULL
store TEXT NOT NULL
price INT NOT NULL
purchased BOOL NOT NULL
);
Just a brief explanation of what is happening here. The first thing that happens is that it deletes any existing table called items it if exists. Then it creates the table items, along with the key fields that will hold our data.
5.2 Initialising the database.
Now that we have a schema, we can use to to create the database. This will generate an SQLite .db database file. We will do it in init_db.py:
import sqlite3
connection = sqlite3.connect(‘database.db’)
with open(‘schema.sql’) as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute(“INSERT INTO items (item, description) VALUES (?, ?)”,
(‘First Item’, ‘Description for first item’)
)
cur.execute(“INSERT INTO items (item, description) VALUES (?, ?)”,
(‘Second Item’, ‘Description for second item’)
)
connection.commit()
connection.close()
What this does is import the sqlite3 model and then opens a connection to database.db. This will be created once we run the python file. We use open to open the schema.sql file, and execute it – this runs the SQL statements in that file. Finally use a cursor object to insert to blog posts into the posts table. Then we commit and close the connection
Finally, we need to run this init_db.py file. We do this by calling:
python init_db.py