Python- creating a student records system in Python, Flask and SQLite – Part 1
- Subject: Python Tutorials
- Learning Time:
This tutorial assumes some basic familiarity with Flask and SQLite.
Create database and table
First, you need somewhere to save all your data. You can create an SQLite database from Python code as below. The program below creates a SQLite database ‘students.db ‘ where the student tables are created, this should be created in a setupDB.py or similar file, separate to your flask app.
import sqlite3
conn = sqlite3.connect('students.db')
print("Opened database successfully");
conn.execute('CREATE TABLE students (name TEXT, addr TEXT, city TEXT)')
print("Table created successfully");
conn.close()
Once the database is setup with the right fields, we can create a web app that uses it, import the things we need and create a new student function that enters some data. We do this in a new file. I’ve called mine app.py.
from flask import Flask, render_template
app= Flask(__name__)
@app.route('/enternew')
def new_student():
return render_template('student.html')
The corresponding student.html file is
Add record
As can be seen, the form data is published to the ‘/addrec’ (standing for add record) endpoint mapped to the addrec () function.
This means that the addrec () function gets the html form’s data from the POST method and inserts the student table. The message corresponding to the success or error in the insert operation will be rendered as ‘result.html’. Below the function you created above, in the main app file, add the following. Before you do however, you will need to add request into the list of things imported from flask.
@app.route('/addrec',methods = ['POST', 'GET'])
def addrec():
if request.method == 'POST':
try:
name = request.form['name']
addr = request.form['add']
city = request.form['city']
with sqlite3.connect("students.db") as con:
cur = con.cursor()
cur.execute("INSERT INTO students (name,addr,city) VALUES (?,?,?)",(name,addr,city) )
con.commit()
msg = "Record successfully added"
except:
con.rollback()
msg = "error in insert operation"
finally:
con.close()
return render_template("result.html",msg = msg)
We also need to add a template for result.html this uses the output from the result operation to display it.
<!doctype html>
<html>
<body>
result of addition : {{ msg }}
<h2><a href = "\">go back to home page</a></h2>
</body>
</html>
List students in the db
One of the things we might need is a way to showcase all the students in the records system. We can put this in the /liststudents endpoint and run a query to get that info passing it to the html.
@app.route('/liststudents')
def listStudents():
con = sqlite3.connect("students.db")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select * from students")
rows = cur.fetchall();
return render_template("studentlist.html",rows = rows)
The next step is to write the html template for this, making use of the ‘rows’ data we bring in.
And finally, the home page function and html:
@app.route('/')
def home():
return render_template('home.html')
HTML:
<!doctype html>
Name Address city
{% for row in rows %}
{% endfor %}</pre>
<table border="1">
<thead></thead>
<tbody>
<tr>
<td>{{row["name"]}}</td>
<td>{{row["addr"]}}</td>
<td>{{ row["city"]}}</td>
</tr>
</tbody>
</table>
<a href="/">Go back to home page</a>
Don’t forget- in order to test your page you want to run it on the debug server, you can add
app.debug = True app.run()