Flask-SQLAlchemy is a great tool for building web applications with Flask, as it provides a high-level interface for interacting with databases. However, there are times when you may need to execute raw SQL queries in your app, for tasks such as complex data manipulation or optimization.

In this article, we will explore how to execute raw SQL queries in a Flask-SQLAlchemy app using the `execute()` method and HTML tags.

To begin, let’s create a simple Flask-SQLAlchemy app with a database model and some sample data. We will use a sample `User` model with fields for `id`, `username`, and `email`.

“`html

Executing raw SQL in Flask-SQLAlchemy

In this article, we will explore how to execute raw SQL queries in a Flask-SQLAlchemy app.

“`

Next, we need to define our `User` model and initialize our Flask app with the SQLAlchemy extension.

“`python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config[‘SQLALCHEMY_DATABASE_URI’] = ‘sqlite:///test.db’
db = SQLAlchemy(app)

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

def __repr__(self):
return ” % self.username
“`

Now that we have our app and database model set up, let’s take a look at how to execute raw SQL queries. In Flask-SQLAlchemy, you can use the `execute()` method provided by the `db` object to execute raw SQL queries. Let’s say we want to retrieve all users from our database, but we want to sort them by their usernames in descending order.

“`html

Executing raw SQL in Flask-SQLAlchemy

In this article, we will explore how to execute raw SQL queries in a Flask-SQLAlchemy app.

Executing raw SQL query

Below is an example of executing a raw SQL query in our Flask-SQLAlchemy app to retrieve all users and order them by username in descending order:

db.engine.execute("SELECT * FROM user ORDER BY username DESC")

“`

To execute the raw SQL query in our Flask-SQLAlchemy app, we simply use the `execute()` method provided by the `db` object and pass in our raw SQL query as a string. In this case, we are using the `ORDER BY` clause to sort the users by their usernames in descending order.

“`python
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config[‘SQLALCHEMY_DATABASE_URI’] = ‘sqlite:///test.db’
db = SQLAlchemy(app)

# … User model definition …

@app.route(‘/’)
def execute_raw_sql():
result = db.engine.execute(“SELECT * FROM user ORDER BY username DESC”)
users = [dict(row) for row in result]
return render_template(‘index.html’, users=users)
“`

In our Flask route, we use the `execute()` method to execute the raw SQL query that retrieves all users from our database and orders them by their usernames in descending order. We then convert the result into a list of dictionaries and pass it to an HTML template using the `render_template()` function.

“`html

Executing raw SQL in Flask-SQLAlchemy

In this article, we will explore how to execute raw SQL queries in a Flask-SQLAlchemy app.

Executing raw SQL query

Below is an example of executing a raw SQL query in our Flask-SQLAlchemy app to retrieve all users and order them by username in descending order:

db.engine.execute("SELECT * FROM user ORDER BY username DESC")

Retrieved users

{% for user in users %}
• {{ user }}

{% endfor %}

“`

Finally, in our HTML template, we iterate over the retrieved users and display them in an unordered list using a for loop.

In conclusion, executing raw SQL queries in a Flask-SQLAlchemy app can be done using the `execute()` method provided by the `db` object. This allows for more complex and specific database operations that may not be achievable using the high-level ORM provided by Flask-SQLAlchemy.