Building a Simple REST API with Python’s Flask and a SQL Database

In today’s tech landscape, RESTful APIs are essential for enabling interactions between different applications and systems. In this tutorial, we’ll walk you through creating a simple REST API using Python’s Flask framework and a SQL database for data storage. Whether you are a beginner hoping to level up your skills or an experienced developer looking for a refresher, this guide has something for you.

What is Flask?

Flask is a lightweight web framework for Python that promotes rapid development and a clean codebase. It’s easy to get started with and is highly extensible for more complex applications. Flask follows the WSGI (Web Server Gateway Interface) standard and is categorized as a micro-framework due to its minimalistic approach.

Why Use a SQL Database?

A SQL database provides a structured way to store data using tables, allowing for efficient querying and data manipulation. Popular SQL databases like SQLite, PostgreSQL, and MySQL are widely supported and well-documented, making them an excellent option for backend data management in web applications.

Prerequisites

Before we begin, ensure you have the following installed:

• Python 3.x
• Flask
• SQLAlchemy
• A SQLite database (or any SQL database of your choice)

Step 1: Setting Up Your Environment

First, let’s create a virtual environment and install Flask and the SQLAlchemy package for ORM access:

mkdir flask-api
cd flask-api
python -m venv venv
source venv/bin/activate  # For Windows use venvScriptsactivate
pip install Flask SQLAlchemy

Step 2: Project Structure

Your project structure will look like this:

flask-api/
│
├── app.py
└── models.py

Step 3: Creating Your Database Model

Next, let’s create a simple data model for a task management API. In this case, we will create a “Task” model with fields for the task ID, title, and status.

# models.py
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(150), nullable=False)
    completed = db.Column(db.Boolean, default=False)

    def __repr__(self):
        return f""

Step 4: Initializing Flask and Connecting to the Database

Now, let’s set up Flask and connect our application to the SQLite database.

# app.py
from flask import Flask, jsonify, request
from models import db, Task

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tasks.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)

with app.app_context():
    db.create_all()  # Create tables

@app.route('/tasks', methods=['GET'])
def get_tasks():
    tasks = Task.query.all()
    return jsonify([{'id': task.id, 'title': task.title, 'completed': task.completed} for task in tasks]), 200

Step 5: Adding API Endpoints

We will implement the following endpoints:

• GET /tasks – Retrieve all tasks
• POST /tasks – Create a new task
• PUT /tasks/<id> – Update an existing task
• DELETE /tasks/<id> – Delete a task

GET Endpoint

We have already defined the GET endpoint to fetch all tasks. Now, let’s add the POST endpoint to create new tasks.

@app.route('/tasks', methods=['POST'])
def create_task():
    data = request.get_json()
    new_task = Task(title=data['title'])
    db.session.add(new_task)
    db.session.commit()
    return jsonify({'id': new_task.id, 'title': new_task.title}), 201

PUT Endpoint

Now, let’s implement the PUT endpoint to update an existing task’s status or title.

@app.route('/tasks/<int:id>', methods=['PUT'])
def update_task(id):
    task = Task.query.get_or_404(id)
    data = request.get_json()
    task.title = data.get('title', task.title)
    task.completed = data.get('completed', task.completed)
    db.session.commit()
    return jsonify({'id': task.id, 'title': task.title, 'completed': task.completed}), 200

DELETE Endpoint

Finally, let’s create the DELETE endpoint.

@app.route('/tasks/<int:id>', methods=['DELETE'])
def delete_task(id):
    task = Task.query.get_or_404(id)
    db.session.delete(task)
    db.session.commit()
    return jsonify({'message': 'Task successfully deleted'}), 200

Step 6: Testing Your API

To test the REST API, you can use tools like Postman or cURL. Here are some sample requests you could test:

• Get all tasks: GET /tasks
• Create a task: POST /tasks

  { "title": "Buy groceries" }

• Update a task: PUT /tasks/1

  { "completed": true }

• Delete a task: DELETE /tasks/1

Step 7: Improving Your API with Error Handling

For a robust API, implementing proper error handling is crucial. Here’s how to return custom error messages:

@app.errorhandler(404)
def not_found(error):
    return jsonify({'error': 'Not found'}), 404

@app.errorhandler(400)
def bad_request(error):
    return jsonify({'error': 'Bad request'}), 400

Step 8: Running Your Application

Before running your API, ensure that you are still in the virtual environment. Use the following command to start the Flask application:

export FLASK_APP=app.py  # For Windows use set FLASK_APP=app.py
flask run

Your API should now be up and running on http://127.0.0.1:5000.

Conclusion

Congratulations! You have built a simple REST API using Flask and a SQL database. This foundational knowledge can be expanded further by implementing features such as authentication, pagination, or integrating with front-end frameworks.

As you continue your software development journey, remember that building RESTful APIs is a crucial skill that opens up a world of possibilities for connecting applications and services. Keep exploring and stay updated with best practices and emerging technologies!

Further Reading

• Flask Documentation
• SQLAlchemy Documentation
• Flask by Example: Project Setup

Happy coding!