Technology. Education. Art (TEA) TEAlearn.org

Home > Courses > Flask: Python Web Development > Database driven Web App using Flask and SQLite (Create and Connect to Database)

Database driven Web App using Flask and SQLite (Create and Connect to Database)

Subject: Flask: Python Web Development

SQLite3 module is used to integrate the SQLite database with Python. It is a standardized Python DBI API and provides a simple-to-use interface for interacting with our SQLite databases.

Connecting our Web App to SQLite and Creating our database and tables
In this example we will connect Python and Flask to SQLite and create the (tealearn_grades) database and it's tables (students, subjects, instructors and score) we had conceptualized and designed in previous topic.

Step 1.
Open the Python app.py files, and have this code in it.


from flask import Flask, render_template, request 
import sqlite3, os 

#get the current path name our app
dir_path = os.path.dirname(os.path.realpath(__file__))

#create and connect to the database in our current folder		
conn = sqlite3.connect(dir_path+'/tealearn_grades.db', check_same_thread=False) 

app = Flask(__name__) 

@app.route('/dbsetup') 
def dbsetup(): 

	sql_create_student_table='CREATE TABLE  IF NOT EXISTS student (student_id INTEGER PRIMARY KEY, surname_name TEXT, other_name TEXT, address_address TEXT)'

	sql_create_instructor_table='CREATE TABLE IF NOT EXISTS instructor (instructor_id INTEGER PRIMARY KEY, surname_name TEXT, other_name TEXT, address_address TEXT, qualification TEXT)'

	sql_create_subject_table='CREATE TABLE IF NOT EXISTS subject (subject_id INTEGER PRIMARY KEY, instructor_id INTEGER, subject_title TEXT, more_detail TEXT, max_score INTEGER)'

	sql_create_score_table='CREATE TABLE IF NOT EXISTS student_score (score_id INTEGER PRIMARY KEY, score_value INTEGER, student_id  INTEGER, subject_id INTEGER)'

	try:		
		
		
		#use the connection to execute the queries to create the tables
		conn.execute(sql_create_student_table)
		conn.commit()
		
		conn.execute(sql_create_instructor_table)
		conn.commit()
		
		conn.execute(sql_create_subject_table)
		conn.commit()
		
		conn.execute(sql_create_score_table)
		conn.commit()
		
		output="db setup successful"
	except sqlite3.Error as er:
		output=er.sqlite_errorname
		
	return render_template('dbsetup.html', msg=output) 
if __name__ == '__main__': 
       app.run(debug=True)

Note:
1. We ensure we imported the SQLite 3 module import sqlite3
2. This line sqlite3.connect('tealearn_grades.db') connected to our database, if the database does not exist sqlite3 will create the database for us, before connecting to it.
3. We have to use the SQL statement CREATE TABLE to create the four tables by:
a. First we store the SQL queries to some variables e.g sql_create_student_table
b. Then we use conn.execute to execute or run it before we commit it to the database we had connected to.
4. The templates then rendered the result in dbsetup.html

Step 2.
Create the dbsetup.html in the templates folder as usual. And it's connect as follows:


<!DOCTYPE html>
<html>
<head>
    <title>TEA Learn DB</title>
</head>
<body>
<h2>{{ msg }}</h2>
</body>
</html>

Step 3.
Run the app.py file and visit the URL
localhost:5000/dbsetup this will call the function in the route, execute it's code as render a message for us, if all went well.

Step 4.
You will see the database file "tealearn_grades.db" create in the same location where your app.py is located, and the four tables are all embedded into it.

View the tables in the database
If you wondering like myself, how can I see the tables in my database to be sure if they are really created.

You can use this online "SQLite Viewer Web App" to do this visit https://sqliteviewer.app/ click the "Open File" button to select the database file you want to view e.g tealearn_grades.db and you will see the tables, it's schema or structure and it's data if it has any.

See the attached screenshots.

http://localhost:5000/dbsetup