Querying Databases with Flask

Welcome to our comprehensive guide on querying databases using Flask! In this tutorial, we'll walk you through the process of setting up a database, connecting it to a Flask application, and executing queries to retrieve and manipulate data.

By the end of this lesson, you'll have a solid understanding of how to work with databases in a practical, real-world context. Let's dive in!

Why Query Databases? 💡

Databases are essential for storing and managing data in web applications. They help us organize, search, and manipulate data efficiently. In this tutorial, we'll be using Flask, a popular Python web framework, to interact with databases.

Getting Started 🎯

Before we start, ensure you have Python and Flask installed on your system. If not, you can find the installation guide here.

Setting Up Our Flask Application 📝

Create a new folder for your project and navigate into it:

bash

mkdir flask-database-tutorial
cd flask-database-tutorial

Next, let's create a basic Flask application:

bash

pip install flask
touch app.py

Open app.py and write the following code:

python

from flask import Flask, render_template

app = Flask(__name__)

@app.route('/')
def home():
    return render_template('home.html')

if __name__ == '__main__':
    app.run(debug=True)

This code creates a simple Flask application with a home route that serves a HTML template.

Adding a Database 📝

For our tutorial, we'll use SQLite, a lightweight, file-based database that's perfect for small applications.

bash

pip install flask-sqlalchemy

Now, let's configure our database in app.py:

python

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)

Here, we've imported SQLAlchemy, configured our database to use SQLite, and created a database file named site.db.

Creating Our First Database Model 📝

In Flask, we define our data structure using Models. Let's create a simple Model for Users:

python

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 f'<User {self.username}>'

This code creates a User model with id, username, and email columns.

Migrating the Database 📝

Before we can add data, we need to create the database table based on our User model. For that, we'll use Flask-Migrate:

bash

pip install flask-migrate

Now, let's initialize Flask-Migrate and create our initial migration:

python

from flask_migrate import Migrate
migrate = Migrate(app, db)

# To create the initial migration:
db.create_all()

After creating the initial migration, run the following command to apply it:

bash

flask db upgrade

Your database is now set up and ready to use!

Querying the Database 🎯

Now that our database is ready, let's learn how to query it:

python

@app.route('/users')
def users():
    users = User.query.all()
    return render_template('users.html', users=users)

In this code, we've created a new route that fetches all users from the database and passes them to a users.html template.

Adding Data to the Database 📝

Let's create a route to add a new user:

python

@app.route('/add_user', methods=['GET', 'POST'])
def add_user():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']

        new_user = User(username=username, email=email)
        db.session.add(new_user)
        db.session.commit()

        return 'User added.'

    return render_template('add_user.html')

In this code, we've created a new route that handles both GET and POST requests. On a POST request, it adds a new user to the database.

Wrapping Up 🎯

You've now learned the basics of querying databases using Flask. In the next lessons, we'll cover more advanced topics like fetching specific users, updating user data, and deleting users.