How to connect Flask to a database with Flask-SQLAlchemy
Flask is a framework that is widely used to create APIs in Python. Flask is a simple yet powerful web framework that is designed to help us get started quickly and easily, with the ability to scale up to complex applications.
Flask is called “micro” because it was built with a simple but extensible core.
SQLAlchemy is an Object Relational Mapper (ORM).
Flask-SQLAlchemy is fun to use, incredibly easy for basic applications, and readily extends for larger applications.
For the complete guide, check out the API documentation on the SQLAlchemy class.
Install the dependencies
First, run the commands below to install the required dependencies:
pip install pipenv
pipenv shell
pipenv install Flask
pipenv install Flask-SQLAlchemy
Create a file and name it app.py. Let’s get to the code.
app.py
from flask import Flask, request, render_template, url_for, redirect, flash
from flask_sqlalchemy import SQLAlchemy
#first step in starting a flask app
app = Flask(__name__)
#configuring the database
app.config['SQLALCHEMY_DATABASE_URI']= 'sqlite:///database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False
#initializing sqlalchemy
db = SQLAlchemy(app)
class Database(db.Model):
id= db.Column(db.Integer(), primary_key=True)
first_name = db.Column(db.String(100), unique=False, nullable=False)
last_name = db.Column(db.String(100), unique=True, nullable=False)
email = db.Column(db.String(300), unique=True)
age = db.Column(db.Integer, nullable=False)
@app.route('/')
def home():
dashboard = Database.query.all()
return render_template('home.html', dashboard=dashboard)
#function to add profiles
@app.route('/add', methods=['GET', 'POST'])
def add():
if request.method == 'POST':
first = request.form.get('first_name')
last = request.form.get('last_name')
email = request.form.get('')
age = request.form.get('age')
if first != '' and last != '' and email != '' and age is not None:
if Database.query.filter_by(email=email).count()==0:
queryset=Database(first_name=first, last_name=last, age=age)
db.session.add(queryset)
db.session.commit()
flash('data successfully added', 'success')
return redirect(url_for('home'))
else:
flash('email already exist', 'warning')
return render_template ('add.html')
else:
flash('pls input data in the required fields', 'warning')
return render_template ('add.html')
return render_template('add.html')
@app.route('/delete/<int:id>')
def delete(id):
#deletes the data on the basis of unique id and
#directs to home page
data = Database.query.get(id)
db.session.delete(data)
db.session.commit()
flash('data successfully deleted', 'danger')
return redirect(url_for('home'))
if __name__ == '__main__':
#for flashed messages to work, you have to add a secret key
app.secret_key = "heyyf"
app.run(debug=True)
Create a folder and name it “templates.”
Inside the “templates” folder, create a file and name it “home.html,” then add the below code.
home.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
rel="stylesheet"
integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
crossorigin="anonymous"
>
</head>
<body>
{% include 'extra/_flash.html' %}
<div class="container">
<h3>Database</h3>
<a href="{{url_for('add')}}">add data</a>
<br>
<table>
<thead>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>#</th>
</thead>
{% for data in dashboard %}
<tbody>
<td>{{data.id}}</td>
<td>{{data.first_name}}</td>
<td>{{data.last_name}}</td>
<td>{{data.age}}</td>
<td><a href="/delete/{{data.id}}" type="button">Delete</a></td>
</tbody>
{% endfor%}
</table>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"
integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p"
crossorigin="anonymous">
</script>
</body>
</html>
Inside the “templates” folder, create a file and name it “add.html,” then add the code below.
add.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3"
crossorigin="anonymous">
</head>
<body>
{% include 'extra/_flash.html' %}
<div class="container">
<p> <a href="{{url_for('home')}}">Back to Home</a></p>
<h3> form</h3>
<form action="/add" method="POST">
<label for="first_name" class="form-label">First Name</label>
<input type="text" class="form-control" name="first_name" placeholder="first name..." required>
<label class="form-label" for="last_name">Last Name</label>
<input type="text" class="form-control" name= "last_name" placeholder="last name..." required>
<label for="email" class="form-label">Email</label>
<input class="form-control" type="text" name= "email" placeholder="email..." required>
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" name="age" placeholder="age.." required>
<button type="submit" class="btn mt-3 btn-primary">Add</button>
</form>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"
integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p"
crossorigin="anonymous">
</script>
</body>
</html>
Inside the “templates” folder, create a folder and name it “extra,” then create a file named “_flash.html” and add the code below.
_flash.html
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
<div class="alert alert-{{ category }} text-center text-primary alert-dismissible fade show" role="alert">
{{message}}
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
In your terminal, run this code to create a database:
python
from app import db
db.create_all()
exit()
Run:
python app.py
Your app should be up and running locally at http://127.0.0.1:5000/.
Conclusion
Flask uses an ORM (SQLAlchemy) that helps to easily map out a database. This app shows us how to use Flask-Sqlalchemy to easily map out the model.
Here is a link to the GitHub repo.
RELATED TAGS
CONTRIBUTOR
Njoku Ifeanyi Gerald