Technology. Education. Art (TEA) TEAlearn.org

Home > Courses > Flask: Python Web Development > Database driven Web App using Flask and SQLite (Web Form insert to database)

Database driven Web App using Flask and SQLite (Web Form insert to database)

Subject: Flask: Python Web Development

In this topic we will be:
1. Create a web form (Student Record Setup) using HTML
2. Make the form to submit to a URL that will process or insert (store) the record into the student table in our tealearn_grades.db database.

Here is the update I made to the app.py file


from flask import Flask, render_template, request 
import sqlite3, os 
app = Flask(__name__) 

#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.route('/') 
def index(): 
  return render_template('index.html') 

@app.route('/student') 
def student(): 
  return render_template('student.html')

@app.route('/addstudent', methods=['GET', 'POST']) 
def addstudent(): 
  if request.method == 'POST': 
    surname = request.form['surname'] 
    othernames = request.form['othernames'] 
    address = request.form['address'] 
    
    conn.execute('INSERT INTO student (surname_name,other_name,address_address) VALUES (?,?,?)',(surname, othernames, address)) 
    conn.commit() 
    output="Student record saved"
  return render_template('student.html', msg=output) 

#other code continue here...
#@app.route('/dbsetup') 
#def dbsetup(): 
#	sql_create_student_table....

if __name__ == '__main__': 
       app.run(debug=True)

Notice that I have to bring the sqlite3.connect('tealearn_grades.db'.... up here, so that all functions in this file that need database access can share just this one connect.

And we have the student.html created in the templates folder as usual.


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Students Record</title>
</head>

<body>
<h2>School Grading System</h2>
    <hr/>
    <a href="/student">Students</a> | 
    <a href="#">Subjects</a> | 
    <a href="#">Instructor</a> | 
    <a href="#">Score Sheet</a> 
    <hr/>
    <h2>Setup Student Record</h2>
  
<a href="/">Back to home</a><br/>
  
{% if msg %}
  <p><b>{{ msg }}</b></p>
{% endif %}
    <form method="POST" action ="/addstudent">
      Surname: <br/>
      <input type="text" name="surname" />
      <br/>
      
      Other names: <br/>
      <input type="text" name="othernames" />
      <br/>
      
      Address: <br/>
      <textarea name="address"></textarea>
      
      <br/>
      <input type="submit" name="Save" value="Submit"/>
  </form>
</body>
</html>

I have to create an index.html for this "School Grading System"


<!DOCTYPE html>
<html>
<head>
    <title>School Grading System</title>
</head>
<body>
    <h2>School Grading System</h2>
    <hr/>
    <a href="/student">Students</a> | 
    <a href="#">Subjects</a> | 
    <a href="#">Instructor</a> | 
    <a href="#">Score Sheet</a> 
    <p>
      Welcome to my web app
    </p>
 </body>
 </html>

We I ran the app.py I navigated from the index or home to the student URL then completed the form twice, and I used the SQLite views to see if the record is insert successful.

Site structure with templates and static folder with files