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 requirementsInstall the Flask and Flask-SQLAlchemy libraries using pip
Syntaxpip install Flask flask_sqlalchemy pymysql cryptography
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 RoutesThis Flask app runs raw SQL queries without defining routes. It establishes an SQLAlchemy connection and executes three queries:
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
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.
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
Breakdown of Code:Note: Update db_cred dictionary with you own MySQL username and password before running the app.
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