How to Execute Raw SQL in Flask - SQLAlchemy App

Introduction to Executing Raw SQL in Flask - SQLAlchemy App

SQLAlchemy is a powerful ORM (Object Relational Mapper) that simplifies database operations in Flask applications. While it encourages the use of Pythonic ORM methods, there are times when writing raw SQL is more efficient or necessary—such as for performance-critical queries, stored procedures, or complex joins. In this tutorial, we'll explore how to execute raw SQL in a Flask application using SQLAlchemy.

Prerequisites

Before we begin, ensure you have the following installed:

• Python
• Flask
• Flask-SQLAlchemy

Install the required packages using pip:

pip install Flask Flask-SQLAlchemy

Setting Up the Flask Application

Let's start by setting up a basic Flask app connected to a SQLite database for demonstration purposes. We'll define a simple model and use raw SQL to interact with the database.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

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

db = SQLAlchemy(app)

Creating a Database Model

Define a basic User model to work with:

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

Run the following command to initialize your database:

with app.app_context():
    db.create_all()

Executing Raw SQL Queries

You can execute raw SQL in SQLAlchemy using the text() method from the sqlalchemy module. Here's how to fetch all users using a raw SQL query:

@app.route('/raw-users')
def get_users_raw():
    sql = text("SELECT * FROM user")
    result = db.session.execute(sql)
    users = result.fetchall()
    return {'users': [dict(row) for row in users]}

Using Raw SQL with Parameters

It's essential to avoid SQL injection by binding parameters securely. Here's how to query using placeholders:

@app.route('/raw-user/<int:user_id>')
def get_user_by_id(user_id):
    sql = text("SELECT * FROM user WHERE id = :id")
    result = db.session.execute(sql, {'id': user_id})
    user = result.fetchone()
    return dict(user) if user else {'error': 'User not found'}

Executing Insert Queries with Raw SQL

You can also insert records using raw SQL. Here's an example:

@app.route('/add-user')
def add_user_raw():
    sql = text("INSERT INTO user (name, email) VALUES (:name, :email)")
    db.session.execute(sql, {'name': 'Alice', 'email': 'alice@example.com'})
    db.session.commit()
    return {'message': 'User added successfully!'}

Conclusion

Using raw SQL in a Flask-SQLAlchemy application gives you more control and flexibility, especially for complex queries. While SQLAlchemy ORM provides a clean and abstracted way to handle databases, raw SQL is still a valuable tool when performance and customization matter. Always use parameterized queries to protect your application from SQL injection vulnerabilities.