Flask applications use SQLAlchemy's ORM to manage database operations. In some scenarios, such as running complex queries, optimizing performance, or leveraging database-specific features, executing raw SQL becomes necessary. SQLAlchemy provides mechanisms to execute raw SQL while maintaining database connectivity and transaction management.
Installing requirements
Install the Flask and Flask-SQLAlchemy libraries using pip
pip install Flask flask_sqlalchemy pymysql cryptography
Syntax
To run raw SQL queries, create a database connection using the SQLAlchemy engine and execute the query using the execute() method.
with db.engine.connect() as connection:
result = connection.execute(text(statement))
Executes a SQL statement using an active database connection.
- Parameters: statement: SQL query wrapped using text()
- Return Type: sqlalchemy.engine.Result
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:
- Create a users table.
- Insert sample records.
- Fetch and display all records in the terminal.
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"] ", Update the connection string with your MySQL username and password.
Explanation:
- Database Configuration: Configures the MySQL database connection using SQLAlchemy with pymysql as the database driver.
- Database Initialization: Creates a users table if it doesn’t already exist and inserts sample user records.
- Executing Raw SQL: Uses SQLAlchemy’s text() to execute raw SQL queries for table creation, data insertion, and retrieval.
- Using Flask App Context: Ensures database operations execute within a valid app context, maintaining proper connection handling.
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.
- get_results API will be used to fetch the records that we get from the SELECT query.
- execute_query API is used to execute raw SQL queries and will return the response message if the query is successfully executed or not.
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
# CREATE THE FLASK APP
app = Flask(__name__)
# DATABASE CONFIGURATION
db_cred = {
'user': 'root',
'pass': 'password',
'host': '127.0.0.1',
'name': 'dbname'
}
app.config['SQLALCHEMY_DATABASE_URI'] = (
f"mysql+pymysql://{db_cred['user']}:"
f"{db_cred['pass']}@{db_cred['host']}/"
f"{db_cred['name']}"
)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# INITIALIZE DATABASE
db = SQLAlchemy(app)
# APP ROUTE TO GET RESULTS FOR SELECT QUERY
@app.route('/get_results', methods=['POST'])
def get_results():
query = request.get_json()['query']
with db.engine.connect() as connection:
result = connection.execute(text(query))
response = {
f"Record {i}": list(row)
for i, row in enumerate(result, start=1)
}
return response
# APP ROUTE TO RUN RAW SQL QUERIES
@app.route('/execute_query', methods=['POST'])
def execute_query():
try:
query = request.get_json()['query']
with db.engine.begin() as connection:
connection.execute(text(query))
except Exception:
return {"message": "Request could not be completed."}
return {"message": "Query executed successfully."}
# RUN THE APP
if __name__ == '__main__':
app.run(debug=True)
Explanation:
- /get_results route executes a raw SELECT query and returns the fetched records.
- Uses text() to execute raw SQL queries in SQLAlchemy 2.x.
- /execute_query route executes INSERT, UPDATE, or DELETE queries.
- Uses transactions to automatically commit successful database changes.
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 your own MySQL username and password before running the app.
Testing with POSTMAN: The API endpoints are tested with three cases:
- Running a SELECT query to fetch records.
- Running an INSERT query to add data.
- Executing an invalid query to check error handling.