A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://www.geeksforgeeks.org/how-to-execute-raw-sql-in-flask-sqlalchemy-app/ below:

How to Execute Raw SQL in Flask - SQLAlchemy App

How to Execute Raw SQL in Flask - SQLAlchemy App

Last Updated : 23 Jul, 2025

In a Flask application that uses SQLAlchemy, we usually interact with the database using Python objects and methods. However, there are times when we need to execute raw SQL queries directly—for example, to optimize performance, run complex queries, or perform database-specific operations.

This guide will show how to execute raw SQL in a Flask app with SQLAlchemy, making it easy to interact with the database beyond the usual ORM methods.

Installing requirements

Install the Flask and Flask-SQLAlchemy libraries using pip

pip install Flask flask_sqlalchemy pymysql cryptography

Syntax

To run raw SQL queries, we first create a flask-SQLAlchemy engine object using which we can connect to the database and execute the SQL queries. The syntax is -

flask_sqlalchemy.SQLAlchemy.engine.execute(statement)

Executes a SQL expression construct or string statement within the current transaction.

Let's look at some of the examples.

Running SQL Queries Without Defining Routes

This Flask app runs raw SQL queries without defining routes. It establishes an SQLAlchemy connection and executes three queries:

  1. Create a users table.
  2. Insert sample records.
  3. Fetch and display all records in the terminal.
Python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text  # Import 'text' for executing raw SQL

# CREATE THE FLASK APP
app = Flask(__name__)

# DATABASE CONFIGURATION
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:password@127.0.0.1:3306/dbname"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# INITIALIZE DATABASE
db = SQLAlchemy(app)

# EXECUTE RAW SQL QUERIES WITH PROPER CONNECTION
with app.app_context():
    with db.engine.connect() as connection:
        # CREATE TABLE IF NOT EXISTS
        connection.execute(text('''
            CREATE TABLE IF NOT EXISTS users (
                email VARCHAR(50),
                first_name VARCHAR(50),
                last_name VARCHAR(50),
                passwd VARCHAR(50)
            );
        '''))
        
        # INSERT DATA INTO users TABLE
        connection.execute(text('''
            INSERT INTO users(email, first_name, last_name, passwd) VALUES 
            ('john.doe@zmail.com', 'John', 'Doe', 'john@123'),
            ('john.doe@zmail.com', 'John', 'Doe', 'johndoe@777'),
            ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00'),
            ('emma@tmail.com', 'Emma', 'Noah', 'whrfc2bfh904'),
            ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00'),
            ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia#900'),
            ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia$345');
        '''))
        
        # COMMIT CHANGES
        connection.commit()
        
        # FETCH RECORDS FROM users TABLE
        result = connection.execute(text('SELECT * FROM users;'))
        for record in result:
            print(record)

# RUN THE APP
if __name__ == '__main__':
    app.run()

Output:

Note: In "app.config["SQLALCHEMY_DATABASE_URI"] ", make edit to enter your MySQL password and username.

Breakdown of Code

Running SQL Queries With Routes

This example includes two routes that function as APIs. They accept POST requests with a query key in the body, where the value is the raw SQL query to be executed. Both the routes are discussed below.

Python
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy

# CREATE THE FLASK APP
app = Flask(__name__)

# DATABASE CONFIGURATION (Move this before initializing db)
db_cred = {
    'user': 'root',         # DATABASE USER
    'pass': 'password',  # DATABASE PASSWORD
    'host': '127.0.0.1',    # DATABASE HOSTNAME
    'name': 'dbname'        # DATABASE NAME
}
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://\
{db_cred['user']}:{db_cred['pass']}@{db_cred['host']}/\
{db_cred['name']}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# INITIALIZE DATABASE AFTER CONFIGURATION
db = SQLAlchemy(app)

# APP ROUTE TO GET RESULTS FOR SELECT QUERY
@app.route('/get_results', methods=['POST'])
def get_results():
    result = db.engine.execute(request.get_json()['query'])
    response = {f'Record {i}': list(each) for i, each in enumerate(result, start=1)}
    return response

# APP ROUTE TO RUN RAW SQL QUERIES
@app.route('/execute_query', methods=['POST'])
def execute_query():
    try:
        db.engine.execute(request.get_json()['query'])
    except:
        return {"message": "Request could not be completed."}
    return {"message": "Query executed successfully."}

# RUN THE APP
if __name__ == '__main__':
    app.run()

We will test the routes through POSTMAN. Following are the 3 cases that are tested using POSTMAN.

To understand API Testing using POSTMAN, refer article: API Testing using POSTMAN.

1. Running a SELECT query to fetch all the records through the get_results API.
 

2. Next, we will test the execute_query API for a valid INSERT query

3. Lastly, we will put any random query and see if we get any error message

Note: Update db_cred dictionary with you own MySQL username and password before running the app.

Breakdown of Code:

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4