Open In App

How to execute raw SQL in Flask-SQLAlchemy app

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we are going to see how to execute raw SQL in Flask-SQLAlchemy using Python.

Installing requirements

Install the Flask and Flask-SQLAlchemy libraries using pip

pip install Flask
pip install flask_sqlalchemy

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.

Parameters:

  • statement: SQL expression

Returns:

  • sqlalchemy.engine.result.ResultProxy

Example 1

Python




# IMPORT REQUIRED LIBRARIES
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
  
# CREATE THE FLASK APP
app = Flask(__name__)
  
# ADD THE DATABASE CONNECTION TO THE FLASK APP
db  = SQLAlchemy(app)
db_cred = {
    'user': 'root',         # DATABASE USER
    'pass': 'password',     # DATABASE PASSWORD
    'host': '127.0.0.1',    # DATABASE HOSTNAME
    'name': 'Geeks4Geeks'   # 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
  
# CREATE A users TABLE USING RAW SQL QUERY
db.engine.execute(
    '''
    CREATE TABLE users (
        email VARCHAR(50),
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        passwd VARCHAR(50)
    );
    '''
)
  
# INSERT TEMP VALUES IN THE users TABLE USING RAW SQL QUERY
db.engine.execute(
    '''
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('john.doe@zmail.com', 'John', 'Doe', 'john@123');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('john.doe@zmail.com', 'John', 'Doe', 'johndoe@777');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('emma@tmail.com', 'Emma', 'Noah', 'whrfc2bfh904');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('noah.emma@wmail.com', 'Emma', 'Noah', 'emaaa!00');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia#900');
    INSERT INTO users(email, first_name, last_name, passwd) VALUES 
    ('liam.olivia@wmail.com', 'Liam', 'Olivia', 'lolivia$345');
    '''
)
  
# VIEW THE RECORDS INSERTED
for record in db.engine.execute('SELECT * FROM users;'):
    print(record)
  
  
# RUN THE APP
if __name__ == '__main__':
    app.run()


Output:

In this example, we created a simple flask app that does not have any route but instead runs raw SQL queries. We have created the SQLAlchemy connection and then executed 3 different raw SQL queries. The first query creates the user’s table. The second query inserts some sample records in the table. The third query fetches all the records and displays them in the terminal.

In all three cases, we have used the db.engine.execute() method. The db.engine provides an SQLAlchemy engine connection and the execute method takes in a SQL query to execute the request.

Example 2

In this example, we have created 2 different routes to work with. These routes will act as an API where we can send a POST request with a query key in the body. The value for this query key will be the raw SQL query that we need to execute. The get_results API will be used to fetch the records that we get from the SELECT query. The execute_query API is used to execute raw SQL queries and will return the response message if the query is successfully executed or not.

Python




# IMPORT REQUIRED LIBRARIES
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
  
# CREATE THE FLASK APP
app = Flask(__name__)
  
# ADD THE DATABASE CONNECTION TO THE FLASK APP
db  = SQLAlchemy(app)
db_cred = {
    'user': 'root',         # DATABASE USER
    'pass': 'password',     # DATABASE PASSWORD
    'host': '127.0.0.1',    # DATABASE HOSTNAME
    'name': 'Geeks4Geeks'   # 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
  
# APP ROUTE TO GET RESULTS FOR SELECT QUERY
@app.route('/get_results', methods=['POST'])
def get_results():
      
    # GET THE SQLALCHEMY RESULTPROXY OBJECT
    result = db.engine.execute(request.get_json()['query'])
    response = {}
    i = 1
  
    # ITERATE OVER EACH RECORD IN RESULT AND ADD IT 
    # IN A PYTHON DICT OBJECT
    for each in result:
        response.update({f'Record {i}': list(each)})
        i+= 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()


Output:

We will test the routes through POSTMAN. Following are the 3 cases that are tested 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



Last Updated : 19 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads