In this Flask Tutorial we want to learn about Flask SQLAlchemy Tutorial for Database Management, so Flask is popular Python web framework, it means that using Flask you can build Python Web Applications easliy. one of the key features of Flask is its ability to integrate with different third party libraries like SQLAlchemy, so SQLAlchemy is powerful Object Relational Mapping or ORM library for database management.
In this Flask Tutorial we want to learn how to use Flask and SQLAlchemy to manage databases in Flask web application. We will cover the following topics:
Creating Flask Application
Before that we talk about database management in Flask, we need to create our simple boilerplate code for Flask applications, make sure that you have already installed Python and Flask. if not, you can follow the Flask documentation to install Flask.
First of all we need to create a new directory for our Flask project and navigate to it. and then we need to create a new Python file called app.py. inside app.py add this code:
|
1 2 3 4 5 6 7 8 9 10 11 |
from flask import Flask app = Flask(__name__) @app.route("/") def hello(): return "Hello from geekscoders.com" if __name__ == "__main__": app.run(debug=True) |
This code creates a new Flask application and defines a route for the root URL. when the user navigates to the root URL hello() function will be called and return a simple greeting.
If you got http://127.0.0.1:5000/, this will be the result.
Setup Flask Database Connection
Now that we have our Flask application, we can configure our database connection. in this tutorial we want to use SQLite as our database. so SQLite is lightweight and file based database system, it is easy to set up and use then MySQL.
First of all we need to install the Flask-SQLAlchemy extension because it provides a simple interface for using SQLAlchemy in Flask. you can install it by running pip install flask-sqlalchemy in your terminal.
After that we need to configure our database connection. add this code in your app.py, this code configures our Flask application to use SQLite and creates a new SQLAlchemy object called db. we also set SQLALCHEMY_TRACK_MODIFICATIONS to False to turn off SQLAlchemy modification tracking system. this is recommended optimization for performance reasons, but may not be necessary for small applications.
|
1 2 |
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.db" app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False |
Flask Models and Database Table
Now that we have our database connection, we can define our database models. in SQLAlchemy a model is a Python class that represents a database table. each attribute of the class represents a column in the table.
For this tutorial, we want to create a simple User model with id, name and email attributes. add this code to app.py:
|
1 2 3 4 5 6 7 8 9 |
class User(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) def __repr__(self): return "<User %r>" % self.name db.create_all() |
Until this point, this will be our complete code for app.py.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.db" app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False db = SQLAlchemy(app) class User(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) def __repr__(self): return "<User %r>" % self.name @app.route("/") def hello(): return "Hello from geekscoders.com" if __name__ == "__main__": app.run(debug=True) |
But right now we don’t have the table, we need to open Flask Shell in the terminal like this.
|
1 |
flask shell |
And after that we need to write these commands in terminal to create our database and table.
|
1 2 |
>>> from app import db >>> db.create_all() |
If we open the database in SQLiteStudio, this will be the result
Flask CRUD Operations on the Database
Now that we have defined our model and created our database tables, we can start performing Flask CRUD operations on the database. SQLAlchemy provides a powerful and easy API for working with databases, and Flask-SQLAlchemy makes it even easier to use.
First you need to open flask shell, we want to do our operations in Flask shell terminal, to create a new user, we can simply create a new User object and add it to the database session:
|
1 2 3 4 |
>>> from app import db, User >>> new_user = User(name="GeeksCoders", email="admin@geekscoders.com") >>> db.session.add(new_user) >>> db.session.commit() |
These commands create a new user and if you check your database. this will be the result
To retrieve all users from the database, we can use query.all() method:
|
1 2 3 |
>>> all_users = User.query.all() >>> for user in all_users: print(user) |
This will be the result
For updating an existing user, we can retrieve the user from the database, update its attributes and commit the changes, this code retrieves the user with the name “GeeksCoders” from the database using filter_by() and first(), and after that updates its email attribute and commits the changes to the database.
|
1 2 3 |
user = User.query.filter_by(name="GeeksCoders").first() user.email = "updated@geekscoders.com" db.session.commit() |
Also we can delete a user from the database, for that we can simply delete the User object and commit the changes:
|
1 2 3 |
user = User.query.filter_by(name="GeeksCoders").first() db.session.delete(user) db.session.commit() |
