Flask doesn’t have a built-in way to handledatabases, so it relies on SQLAlchemy, a powerful library that makes working with databases easier. SQLAlchemy provides anObject Relational Mapper (ORM), allowing developers to interact with databases usingPython code instead of raw SQL.
This brings several advantages:
- Simplifies database management.
- Improves security.
- Supports multiple database systems likeSQLite,MySQL andPostgreSQL.
- Easily integrates with Flask using the Flask - SQLAlchemy extension.
In this article, we’ll build a Flask app where users submit a form, view the data on another page, and delete it if needed. The focus will be on the backend, not the front end.
Installation
Create the project folder and open the command line in the directory. Create a pythonvirtual environmentusing the command below.
python -m venv venv
After creating virtual environment, activate it by using the following command-
For windows:
venv\scripts\activate
For Mac/Linux:
source venv/bin/activate
Now, install Flask and Flask-SQLAlchemy in it using command:
pip install flask flask-sqlalchemy
File Structure
Below is the snapshot of the file structure of the project after its completed.
File structure after completionCreating app.py
Once the installation is complete, we can create our main Flask application file, app.py. To verify that everything is installed and running correctly, paste the basic app code below and start the application by running python app.py in the terminal (this code will be updated later).
PythonfromflaskimportFlaskapp=Flask(__name__)'''If everything works fine you will get amessage that Flask is working on the firstpage of the application'''@app.route('/')defcheck():return'Flask is working'if__name__=='__main__':app.run()Output:
Setting Up SQLAlchemy
To create a database we need to importSQLAlchemy inapp.py, set up SQLite configuration, and create a database instance as shown below.
PythonfromflaskimportFlask,render_template,request,redirectfromflask_sqlalchemyimportSQLAlchemyapp=Flask(__name__)# Configure SQLite databaseapp.config['SQLALCHEMY_DATABASE_URI']='sqlite:///site.db'app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False# Avoids a warning# Create SQLAlchemy instancedb=SQLAlchemy(app)# Run the app and create databaseif__name__=='__main__':withapp.app_context():# Needed for DB operationsdb.create_all()# Creates the database and tablesapp.run(debug=True)
We set up Flask, connect it to aSQLite database (site.db), and usedb.create_all() to create the database when the app runs. Theapp_context() ensures SQLAlchemy works properly.
Creating Model
In sqlalchemy we use classes to create our database structure. In our application, we will create a Profile table that will be responsible for holding the user's id, first name, last name, and age.
PythonclassProfile(db.Model):id=db.Column(db.Integer,primary_key=True)first_name=db.Column(db.String(20),unique=False,nullable=False)last_name=db.Column(db.String(20),unique=False,nullable=False)age=db.Column(db.Integer,nullable=False)# repr method represents how one object of this datatable# will look likedef__repr__(self):returnf"Name :{self.first_name}, Age:{self.age}"The table below explains some of the keywords used in the model class.
| Column | used to create a new column in the database table |
|---|
| Integer | An integer data field |
|---|
| primary_key | If set to True for a field ensures that the field can be used to uniquely identify objects of the data table. |
|---|
| String | An string data field. String(<maximum length>) |
|---|
| unique | If set to True it ensures that every data in that field in unique. |
|---|
| nullable | If set to False it ensures that the data in the field cannot be null. |
|---|
| __repr__ | Function used to represent objects of the data table. |
|---|
Display data on Index Page
Create an "index.html" file int the Templates folder. This will be our root page, it will display all the saved profiles in the database. Jinja templating will dynamically render the data in the HTML file. The delete function will be added later.
HTML<!DOCTYPE html><html><head><title>Index Page</title></head><body><h3>Profiles</h3><ahref="/add_data">ADD</a><br><table><thead><th>Id</th><th>First Name</th><th>Last Name</th><th>Age</th><th>#</th></thead> {% for data in profiles %}<tbody><td>{{data.id}}</td><td>{{data.first_name}}</td><td>{{data.last_name}}</td><td>{{data.age}}</td><td><ahref="/delete/{{data.id}}"type="button">Delete</a></td></tbody> {% endfor%}</table></body></html>
We loop through every object in profiles that we pass down to our template in our index function and print all its data in a tabular form. The index function in ourapp.py is updated as follows.
Python@app.route('/add_data')defadd_data():returnrender_template('add_profile.html')Creating add_profile.html
In the templates folder, create file "add_profile.html", it will erender the form that taes user input for the profile details and the form will be linked to a "/add" route.
HTML<!DOCTYPE html><html><head><title>Add Profile</title></head><body><h3>Profile form</h3><formaction="/add"method="POST"><label>First Name</label><inputtype="text"name="first_name"placeholder="first name..."><label>Last Name</label><inputtype="text"name="last_name"placeholder="last name..."><label>Age</label><inputtype="number"name="age"placeholder="age.."><buttontype="submit">Add</button></form></body></html>
Creating "/add" route in app.py
This route will receive the form data, create a user object and add it to the databse.
Python@app.route('/add',methods=["POST"])defprofile():first_name=request.form.get("first_name")last_name=request.form.get("last_name")age=request.form.get("age")iffirst_name!=''andlast_name!=''andageisnotNone:p=Profile(first_name=first_name,last_name=last_name,age=age)db.session.add(p)db.session.commit()returnredirect('/')else:returnredirect('/')
To check whether the code is working fine or not, we can run the following command to start the local server.
python app.py
Now, visithttp://localhost:5000/add_data and you will be able to see the form.
Output:
Function to add data to the database
Create a "/add" route in app.py. In this route will use request objects to get form data inside the function then create an object of theProfileclass and store it in our database using database sessions.
Python# function to add profiles@app.route('/add',methods=["POST"])defprofile():first_name=request.form.get("first_name")last_name=request.form.get("last_name")age=request.form.get("age")iffirst_name!=''andlast_name!=''andageisnotNone:p=Profile(first_name=first_name,last_name=last_name,age=age)db.session.add(p)db.session.commit()returnredirect('/')else:returnredirect('/')Once the function is executed it redirects us back to the index page of the application.
Deleting data from our database
To delete data we have already used an anchor tag in our table and now we will just be associating a function with it.
Python@app.route('/delete/<int:id>')deferase(id):# Deletes the data on the basis of unique id and# redirects to home pagedata=Profile.query.get(id)db.session.delete(data)db.session.commit()returnredirect('/')The function queries data on the basis of id and then deletes it from our database.
Complete Final Code
The entire code forapp.py,index.html, andadd-profile.html is given below.
app.py
PythonfromflaskimportFlask,request,redirectfromflask.templatingimportrender_templatefromflask_sqlalchemyimportSQLAlchemyapp=Flask(__name__)app.debug=Trueapp.config['SQLALCHEMY_DATABASE_URI']='sqlite:///site.db'app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False# Avoids a warningdb=SQLAlchemy(app)# ModelclassProfile(db.Model):id=db.Column(db.Integer,primary_key=True)first_name=db.Column(db.String(20),unique=False,nullable=False)last_name=db.Column(db.String(20),unique=False,nullable=False)age=db.Column(db.Integer,nullable=False)# repr method represents how one object of this datatable# will look likedef__repr__(self):returnf"Name :{self.first_name}, Age:{self.age}"fromflaskimportFlask,request,redirectfromflask.templatingimportrender_templatefromflask_sqlalchemyimportSQLAlchemyapp=Flask(__name__)app.debug=True# adding configuration for using a sqlite databaseapp.config['SQLALCHEMY_DATABASE_URI']='sqlite:///site.db'# Creating an SQLAlchemy instancedb=SQLAlchemy(app)# ModelsclassProfile(db.Model):id=db.Column(db.Integer,primary_key=True)first_name=db.Column(db.String(20),unique=False,nullable=False)last_name=db.Column(db.String(20),unique=False,nullable=False)age=db.Column(db.Integer,nullable=False)def__repr__(self):returnf"Name :{self.first_name}, Age:{self.age}"# function to render index page@app.route('/')defindex():profiles=Profile.query.all()returnrender_template('index.html',profiles=profiles)@app.route('/add_data')defadd_data():returnrender_template('add_profile.html')# function to add profiles@app.route('/add',methods=["POST"])defprofile():first_name=request.form.get("first_name")last_name=request.form.get("last_name")age=request.form.get("age")iffirst_name!=''andlast_name!=''andageisnotNone:p=Profile(first_name=first_name,last_name=last_name,age=age)db.session.add(p)db.session.commit()returnredirect('/')else:returnredirect('/')@app.route('/delete/<int:id>')deferase(id):data=Profile.query.get(id)db.session.delete(data)db.session.commit()returnredirect('/')if__name__=='__main__':withapp.app_context():# Needed for DB operations outside a requestdb.create_all()# Creates the database and tablesapp.run(debug=True)index.html
HTML<!DOCTYPE html><html><head><title>Index Page</title></head><body><h3>Profiles</h3><ahref="/add_data">ADD</a><br><table><thead><th>Id</th><th>First Name</th><th>Last Name</th><th>Age</th><th>#</th></thead> {% for data in profiles %}<tbody><td>{{data.id}}</td><td>{{data.first_name}}</td><td>{{data.last_name}}</td><td>{{data.age}}</td><td><ahref="/delete/{{data.id}}"type="button">Delete</a></td></tbody> {% endfor%}</table></body></html>add_profile.html
HTML<!DOCTYPE html><html><head><title>Add Profile</title></head><body><h3>Profile form</h3><formaction="/add"method="POST"><label>First Name</label><inputtype="text"name="first_name"placeholder="first name..."><label>Last Name</label><inputtype="text"name="last_name"placeholder="last name..."><label>Age</label><inputtype="number"name="age"placeholder="age.."><buttontype="submit">Add</button></form></body></html>
Output:
