Performing database operations

Flask is a popular web framework for Python, and it's often used with databases to store and retrieve information. In this tutorial, we're going to learn how to perform basic database operations using Flask. We'll go over how to setup a database, how to insert data into it, how to retrieve data from it, and finally, how to update and delete data.

Let's get started!

Setup a Database

To interact with databases in Flask, we will use the Flask-SQLAlchemy extension, which is a Flask wrapper for SQLAlchemy – a powerful relational database framework that offers SQL's flexibility and high-level Pythonic interface.

First, install Flask-SQLAlchemy with pip:

pip install flask-sqlalchemy

Next, we'll need to configure the database:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db' # SQLite database file in /tmp directory
db = SQLAlchemy(app)

Define a Model

Models in SQLAlchemy are Python classes that define tables in the database. Here's an example of a User model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username

Creating Tables

After defining your models, create the corresponding tables in the database using db.create_all().

db.create_all()

Inserting Data

To insert data into the table, you create an instance of the model and then add it to the database session. Finally, you commit the session to write the changes to the database.

admin = User(username='admin', email='[email protected]')
db.session.add(admin)
db.session.commit()

Querying Data

To query data from the database, you use the query attribute of the model class. The following retrieves all users from the database:

User.query.all()

To get a single user by username:

User.query.filter_by(username='admin').first()

Updating Data

To update a record, you query for the record, make your changes, and then commit the changes.

user = User.query.filter_by(username='admin').first()
user.email = '[email protected]'
db.session.commit()

Deleting Data

To delete a record, you query for the record, then call the delete method on the session and commit the changes.

user = User.query.filter_by(username='admin').first()
db.session.delete(user)
db.session.commit()

That's it for this tutorial on performing database operations in Flask. We covered how to setup a database, define a model, create tables, insert data, query data, update data, and delete data. With this knowledge, you should be able to perform basic database operations in your Flask applications. Happy coding!