Query db SQLAlchemy starting on last index

Question

I have the following question:

I'm currently learning Flask and SQLAlchemy, in my enviroment im building a blog (a simple one). In here on my home route, I'm doing the following:

@app.route("/")
@app.route("/home")
def home():
    posts = Post.query.all()
    return render_template("home.html", title = "Home", posts = posts)

The posts = Post.query.all() will query all the fields on my Posts table, then on my template I'm doing the following:

{% extends "layout.html" %}

    {% block content %}

        {% for post in posts %}

            <article class="media content-section">
                <img class="rounded-circle account-img" src="{{ 
                url_for("static", filename="profile_pics/" + post.author.image_file) }}" alt="">
                <div class="media-body">
                    <div class="article-metadata">
                        <a class="mr-2" href="#">{{ post.author.username }}</a>
                        <small class="text-muted">{{ post.date_posted.strftime("%Y-%m-%d") }}</small>
                    </div>
                    <h2><a class="article-title" href="#">{{ post.title }}</a></h2>
                    <hr>
                </div>
            </article>

        {% endfor %}


{% endblock %}

The problem is that in this for loop my latest post shows up as the last post , when I actually want to put it as the first one, how can I query a db through SQLAlchemy and Flask in reverse or I'm i thinking of it the wrong way?

simanacci · Accepted Answer · 2018-11-04T19:42:35.393

2

Use SQLAlchemy's order_by.

Post.query.order_by(desc(Post.date_posted)).all()

Example:

>>> from datetime import datetime
>>> p1 = Post(posted=datetime.strptime('2018-01-01', '%Y-%m-%d'))
>>> p2 = Post(posted=datetime.strptime('2017-01-01', '%Y-%m-%d'))
>>> p3 = Post(posted=datetime.strptime('2019-01-01', '%Y-%m-%d'))
>>> db.session.add_all([p1, p2, p3])
>>> db.session.commit()
>>> Post.query.order_by(desc(Post.posted)).all()
[<Post 3>, <Post 1>, <Post 2>]
>>> [item.posted.year for item in Post.query.order_by(desc(Post.posted)).all()]
[2019, 2018, 2017] #most recent to oldest

edited Nov 04 '18 at 19:42

answered Nov 04 '18 at 19:28

simanacci

2,197
3
26
35

Nice , Thank you , I'll give a it a try. – Alvaro Lamadrid Nov 05 '18 at 17:20
Hey, so it looks like thats the way to go, but the problem is that after I import desc i get an error saying that flask_sqlalchemy can not import desc. I'm guessing desc is in sqlalchemy but no in the Flask-SQLAlchemy library, is this possible? – Alvaro Lamadrid Nov 05 '18 at 20:29
Ok, so it looks like the package flask-sqlalchemy also fetches sqlalchemy , but desc can not be imported from flask-sqlalchemy it has to be imported directly from sqlachemy like: ` from sqlalchemy import desc` – Alvaro Lamadrid Nov 05 '18 at 20:33
`from sqlalchemy import desc` or directly `Post.query.order_by(Post.date_posted.desc()).all()`. – simanacci Nov 06 '18 at 03:25

Query db SQLAlchemy starting on last index

1 Answers1