Database driven Web App using Flask and SQLite (Introduction)
Subject: Flask: Python Web Development
Most web application are database driven; that is, have interaction with a Database to store or provide access to data, from a feedback form that save use enquiry to website with registration/login function, blogs and online forum web application they all have Database in common.
RDBMS, Relational Database and SQL
The common type of Database system (ways of storing data) use for typical web app is the
Relational Database System and a program called
Relational Database Management System (RDBMS) is use to manage such kind of database.
RDBMS is a type of database management system (DBMS) that stores data in a row-based table structure which connects related data elements. That is, RDBMS is a program used to maintain a relational database.
RDBMS uses a standard data oriented language called Structured Query Language (SQL) to access the relational database. A relational database defines database relationships in the form of tables. The tables are related to each other based on data common to each.
Popular RDBMS program in use includes MySQL, SQLite, Microsoft SQL Server and Oracle.
Example 1
We can have a relational database for "TEA Learn grades" this database will have the following structure.
DATABASE: tealearn_grades
TABLES: students, instructors and grade
The tables will have the following columns or fields. Note that the fields will also have a data type.
Field name (Data type) [optional Key]
<![CDATA[
STUDENT table:
student_id (integer or number) [Primary Key]
surname_name (text)
other_name (text)
address_address(text)
INSTRUCTORS table:
instructor_id (integer) [Primary Key]
surname_name (text)
other_name (text)
address_address(text)
qualification (text)
SUBJECTS table:
subject_id (integer) [Primary Key]
instructor_id (integer) [Foreign Key]
subject_title (text)
more_detail (text)
maximum_score_obtainable (integer)
STUDENTS_SCORE table:
score_id (integer) [Primary Key]
score_value(integer)
student_id (integer) [Foreign Key]
subject_id (integer) [Foreign Key]
]]>
Primary Keys
It is worth to note that is it expected that every table in a relational database system have a unique key called Primary Key field, that will store a unique value that is specific to every record that will to stored in the tables.
Although students email or matriculation number is unique to a particular individual student, for example benjamin.onuorah@gmail.com is unique to only me but we have decided to follow the usual easy way of using a field ID e.g "student_id", "instructor_id" etc that will later be set to contain auto sequential number (1, 2, 3 etc) as record is entered into the table as we will see later.
Relationship between tables
The idea of Primary is also essential for creating a relationship between two or more tables. Which is what relationship database system is known for.
Foreign Key
As you can see in example 1. There are tables e.g SUBJECTS table that has another ID "instructor_id" as a Foreign Key.
This signifies that there is a relationship between the "instructors" table and the "subjects" tables, as the instructors or teachers is associated with the subject they are teaching on TEA Learn.
So this relationship will help us to know in the SUBJECTS table "using the instructor_id that appear as foreign key" the teaching that is teaching a particular subject in that "subject" table.
Structured Query Language (SQL)
We will be using SQLite which is a RDBMS to create the database "tealearn_grades" and it's tables "students, instructors and grade" with their respective fields and data type, as well as populate some of the tables.
CREATE: create database and it's tables
READ: Select or retrieve record or data from a database table.
UPDATE: the edit or modify record or data from a database table.
DELETE: the remove a record from a database table.
In the next topic will be doing the CRUD operation using Python/Flask to create and manage the "tealearn_grades" database and it's tables.
By:
Benjamin Onuorah
Login to comment or ask question on this topic
Previous Topic Next Topic
