ktmrc200.in

Python Flask Rest Api With Sqlalchemy

Written by

arun

in

Building a Python Flask REST API that talks to a relational database is one of the most practical ways to deliver data-driven web services. When you pair Flask with SQLAlchemy, you get a lightweight, flexible framework backed by a powerful ORM that handles everything from simple queries to complex transactions. In this guide we’ll walk through every step—setting up the project, defining models, creating CRUD endpoints, and polishing the API for production—so you can launch a robust, SEO‑friendly Flask REST API in no time.

Why Combine Flask with SQLAlchemy?

Flask is celebrated for its minimalistic core and extensibility, while SQLAlchemy is the de‑facto ORM for Python. Together they offer:

  • Fast development: Minimal boilerplate lets you focus on business logic.
  • Database agnosticism: Write once, run on PostgreSQL, MySQL, SQLite, or Oracle.
  • Rich query language: Leverage SQLAlchemy’s expression language for safe, readable queries.
  • Scalable architecture: Separate concerns with blueprints, models, and services.

Project Setup: From Scratch to Ready‑to‑Run

1. Create a virtual environment

python3 -m venv venv
source venv/bin/activate  # On Windows use: venv\Scripts\activate

2. Install required packages

pip install Flask==2.3.2 Flask‑SQLAlchemy==3.0.5 Flask‑RESTful==0.3.10

3. Project structure

  • app/ – Main application package
  • app/__init__.py – Flask app factory
  • app/models.py – SQLAlchemy models
  • app/resources.py – RESTful resources (endpoints)
  • run.py – Entry point

Creating the Flask Application Factory

Using an app factory keeps your code testable and lets you configure different environments (development, testing, production).

# app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_restful import Api

db = SQLAlchemy()
api = Api()

def create_app(config_name='development'):
    app = Flask(__name__)

    # Basic configuration – adjust for your DB
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    db.init_app(app)
    api.init_app(app)

    # Register resources (endpoints)
    from .resources import UserListResource, UserResource
    api.add_resource(UserListResource, '/api/users')
    api.add_resource(UserResource, '/api/users/<int:user_id>')

    # Create tables if they don’t exist
    with app.app_context():
        db.create_all()

    return app

Defining Database Models with SQLAlchemy

SQLAlchemy models map Python classes to database tables. Below is a simple User model that includes common fields and a helpful to_dict method for JSON serialization.

# app/models.py
from . import db

class User(db.Model):
    __tablename__ = 'users'

    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)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'created_at': self.created_at.isoformat()
        }

    def __repr__(self):
        return f''

Building CRUD Endpoints with Flask‑RESTful

Flask‑RESTful simplifies the creation of RESTful resources. Each resource class handles HTTP verbs (GET, POST, PUT, DELETE) and returns JSON responses.

# app/resources.py
from flask import request, jsonify
from flask_restful import Resource
from . import db
from .models import User

class UserListResource(Resource):
    def get(self):
        """Return a list of all users."""
        users = User.query.all()
        return jsonify([u.to_dict() for u in users])

    def post(self):
        """Create a new user."""
        data = request.get_json()
        if not data or not data.get('username') or not data.get('email'):
            return {'message': 'Username and email are required.'}, 400

        if User.query.filter_by(email=data['email']).first():
            return {'message': 'Email already exists.'}, 409

        new_user = User(username=data['username'], email=data['email'])
        db.session.add(new_user)
        db.session.commit()
        return new_user.to_dict(), 201

class UserResource(Resource):
    def get(self, user_id):
        """Retrieve a single user by ID."""
        user = User.query.get_or_404(user_id)
        return user.to_dict()

    def put(self, user_id):
        """Update an existing user."""
        user = User.query.get_or_404(user_id)
        data = request.get_json()
        if data.get('username'):
            user.username = data['username']
        if data.get('email'):
            user.email = data['email']
        db.session.commit()
        return user.to_dict()

    def delete(self, user_id):
        """Delete a user."""
        user = User.query.get_or_404(user_id)
        db.session.delete(user)
        db.session.commit()
        return {'message': f'User {user_id} deleted.'}, 204

Running the API

Create a simple entry point to launch the server.

# run.py
from app import create_app

app = create_app()

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

Execute python run.py and your API will be available at http://127.0.0.1:5000/api/users.

Testing Your Endpoints

Using curl

  • List users: curl http://localhost:5000/api/users
  • Create a user: curl -X POST -H "Content-Type: application/json" -d '{"username":"alice","email":"alice@example.com"}' http://localhost:5000/api/users
  • Update a user: curl -X PUT -H "Content-Type: application/json" -d '{"email":"alice@newdomain.com"}' http://localhost:5000/api/users/1
  • Delete a user: curl -X DELETE http://localhost:5000/api/users/1

Using Postman or Insomnia

Import the above URLs as a collection, set the request body to raw JSON, and you’ll have a visual testing environment that also generates documentation snippets.

Best Practices for a Production‑Ready Flask REST API

1. Configuration Management

  • Store secrets (DB URI, API keys) in environment variables.
  • Use separate config classes for development, testing, and production.

2. Input Validation

Leverage libraries such as marshmallow or pydantic to validate request payloads and avoid manual checks.

3. Error Handling

# app/__init__.py (add to create_app)
from flask import jsonify

def handle_404(error):
    return jsonify({'error': 'Resource not found'}), 404

def handle_500(error):
    return jsonify({'error': 'Internal server error'}), 500

app.register_error_handler(404, handle_404)
app.register_error_handler(500, handle_500)

4. Pagination & Filtering

Never return an unbounded list of rows. Implement query parameters like ?page=2&limit=20 and optional filters (e.g., ?username=alice).

5. Security Measures

  • Enable CORS with flask-cors only for trusted origins.
  • Use HTTPS in production; Flask’s built‑in server is not meant for public traffic.
  • Implement authentication (JWT, OAuth2) to protect private endpoints.
  • Sanitize inputs—SQLAlchemy already protects against SQL injection, but stay vigilant.

6. Logging & Monitoring

Configure logging to capture request/response cycles and integrate with tools like Sentry or Prometheus for real‑time alerts.

Common Pitfalls and How to Avoid Them

  1. Hard‑coding the database URL: Move it to .env and load

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts