Python and SQLAlchemy: Building a Data-Driven Web App

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Installation
  4. Setting Up the Database
  5. Creating the Flask Application
  6. Defining the Models
  7. Creating the Routes
  8. Testing the Web App
  9. Conclusion

Introduction

In this tutorial, we will learn how to build a data-driven web application using Python and SQLAlchemy. SQLAlchemy is a powerful Object-Relational Mapping (ORM) library that provides an intuitive and flexible way to interact with databases in Python. We will be using SQLAlchemy along with Flask, a micro web framework, to create a web application that allows users to perform CRUD (Create, Read, Update, Delete) operations on a database.

By the end of this tutorial, you will have a working web application that can store and retrieve data from a database, as well as modify and delete existing data. You will also learn how to set up the necessary environment, define database models, create routes for handling requests, and test the web app using a web browser.

Prerequisites

To follow along with this tutorial, you should have a basic understanding of Python programming and web development concepts. You should also have the following software installed on your computer:

  • Python 3.x: You can download Python from the official website at https://www.python.org/downloads/.
  • Flask: Install Flask using the command pip install flask.
  • SQLAlchemy: Install SQLAlchemy using the command pip install sqlalchemy.

Installation

Before we start building the web app, let’s install the required packages and set up the project directory.

  1. Create a new directory for your project and navigate to it using the terminal or command prompt.
  2. Create a new virtual environment by running the command python -m venv venv.
  3. Activate the virtual environment:
    • On Windows, run the command venv\Scripts\activate.
    • On macOS and Linux, run the command source venv/bin/activate.
  4. Install Flask and SQLAlchemy by running the commands pip install flask and pip install sqlalchemy.

Setting Up the Database

Now that we have the project set up, let’s create a SQLite database for our web app.

  1. Create a new file named app.py in your project directory.
  2. Import the necessary modules at the top of the file: 
     from flask import Flask
 from flask_sqlalchemy import SQLAlchemy
  3. Initialize the Flask application and configure the database URL: 
     app = Flask(__name__)
 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///myapp.db'
  4. Create an instance of the SQLAlchemy class and bind it to the Flask app: 
     db = SQLAlchemy(app)

    Creating the Flask Application

With the database set up, we can now start creating the Flask application.

  1. Add the following code below the database setup: 
     @app.route('/')
 def index():
     return 'Hello, World!'
  2. Run the Flask application: 
     if __name__ == '__main__':
     app.run(debug=True)
  3. Save the file and open a terminal or command prompt.
  4. Navigate to your project directory and activate the virtual environment if it’s not already activated.
  5. Run the command python app.py.
  6. Open a web browser and visit http://localhost:5000. You should see the message “Hello, World!” displayed.

Defining the Models

Next, let’s define the models for our web app. Models are Python classes that represent database tables.

  1. Create a new file named models.py in your project directory.
  2. Import the necessary modules at the top of the file: 
     from app import db
  3. Define the models by creating a class for each table in the database: 
     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 f'<User {self.name}>'
	
 class Post(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     title = db.Column(db.String(120), nullable=False)
     content = db.Column(db.Text, nullable=False)
     user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
	
     def __repr__(self):
         return f'<Post {self.title}>'
  4. Save the file.

Creating the Routes

Now that we have our models defined, let’s create the routes for handling different requests in our web app.

  1. Open the app.py file.
  2. Import the models at the top of the file: 
     from models import User, Post
  3. Add the following routes below the existing route: 
     @app.route('/users')
 def users():
     users = User.query.all()
     return render_template('users.html', users=users)
	
 @app.route('/posts')
 def posts():
     posts = Post.query.all()
     return render_template('posts.html', posts=posts)
  4. Install the necessary package: 
     pip install flask render_template

    Testing the Web App

Now, let’s test our web app by creating some sample data and displaying it on the web pages.

  1. Create a new file named users.html in your project directory.
  2. Add the following code to the users.html file: 
     <!DOCTYPE html>
 <html>
 <head>
     <title>Users</title>
 </head>
 <body>
     <h1>Users</h1>
     <ul>
	        
     </ul>
 </body>
 </html>
  3. Create a new file named posts.html in your project directory.
  4. Add the following code to the posts.html file: 
     <!DOCTYPE html>
 <html>
 <head>
     <title>Posts</title>
 </head>
 <body>
     <h1>Posts</h1>
     <ul>
	        
     </ul>
 </body>
 </html>
  5. Save both files.
  6. Restart the Flask application by stopping it in the terminal or command prompt using CTRL + C and running the command python app.py again.
  7. Visit http://localhost:5000/users in a web browser. You should see the list of users displayed.
  8. Visit http://localhost:5000/posts in a web browser. You should see the list of posts displayed.

Conclusion

Congratulations! You have successfully built a data-driven web app using Python and SQLAlchemy. In this tutorial, you learned how to set up the necessary environment, define database models, create routes for handling requests, and test the web app using a web browser. You can further enhance the web app by adding more functionality, such as user registration, authentication, and CRUD operations on the models.

Feel free to explore the Flask and SQLAlchemy documentation to learn more about their capabilities and features. Happy coding!

Note: If you encounter any errors or issues while following this tutorial, check for any typos in the code or refer to the Flask and SQLAlchemy documentation for troubleshooting tips.

Published:

Related Articles