Flask SQLAlchemy Marshmallow | How do I query all entries between two ID values

Question

I want to be able to query a database and jsonify() to results to send over the server.

My function is supposed to incrementally send x amount of posts every time it called, i.e. Sending posts 1 - 10, ..., Sending posts 31 - 40, ...

I have the following query:

q = Post.query.filter(Post.column.between(x, x + 10))
result = posts_schema.dump(q)

return make_response(jsonify(result), 200) // or would it be ...jsonify(result.data), 200)?

Ideally, it would return something like this:

[
  {
    "id": 1,
    "title": "Title",
    "description": "A descriptive description."
  },
  {
    "id": 2,
    ...
  },
  ...
]

The SQLAlchemy model I am using and the Marshmallow schema:

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(30))
    content = db.Column(db.String(150))

    def __init__(self, title, description):
        self.title = title
        self.description = description


class PostSchema(ma.Schema):
    class Meta:
        fields = ('id', 'title', 'description')


posts_schema = PostSchema(many=True)

I am new to SQLAlchemy, so I don't know too much about query yet. Another user had to point me in the direction I am in now with the current query, but I don't think it is quite right.

In SQL, I am looking to reproduce the following:

SELECT * FROM Post WHERE id BETWEEN value1 AND value2

This is known as *pagination*: it can be done in sql with something like `SELECT FROM tbl WHERE ORDER BY LIMIT x OFFSET y`. This can be done fairly easily in SQLAlchemy, I think there's also a package that does this for flask (I don't know it would work with marshmallow) — snakecharmerb, Dec 04 '20 at 18:00
Yeah, pagination is what I am trying to achieve - feed of endless posts. I have a working example where I read from a `list()`, but I want to be able to read straight from the database. — Jake Jackson, Dec 04 '20 at 18:57
This might help a little https://stackoverflow.com/a/43104868/5320906 also https://github.com/marshmallow-code/marshmallow-jsonapi/issues/96#issuecomment-371975056 — snakecharmerb, Dec 05 '20 at 08:18

Dan Safee · Answer 1 · 2022-06-21T20:38:16.310

To paginate with SQL Alchemy you would do the following:


# In the view function, collect the page and per page values
@app.route('/posts/<int:page>/<int:per_page>', methods=['GET'])
def posts(page=1, per_page=30):
    #... insert other logic here

    posts = Post.query.order_by(Post.id.asc())  # don't forget to order these by ID
    posts = posts.paginate(page=page, per_page=per_page)

    return jsonify({
        'page': page,
        'per_page': per_page,
        'has_next': posts.has_next,
        'has_prev': posts.has_prev,
        'page_list': [iter_page if iter_page else '...' for iter_page in posts.iter_pages()],
        'posts': [{
            'id': p.id,
            'title': p.title,
            'content': p.content
        } for p in posts.items]
    })

On the front end, you would use the page_list, page, per_page, has_next, and has_prev values to help the user choose which page to go to next.

The values you pass in the URL will dictate which page to go to next. This is all handily built into SQLAlchemy for you, which is another reason it is such a great library.

I am using an endless scrolling feature, not pages :/ is there a way to adapt this to my needs? — Jake Jackson, Dec 05 '20 at 17:42
Yes, use javascript to grab the next page using the `window.addEventListener('scroll', function(e) {})` event listener. Make sure you debounce that event so you don't fire off thousands of requests each time a scroll event occurs. — Dan Safee, Dec 05 '20 at 18:50

score 0 · Accepted Answer · answered Dec 07 '20 at 02:59

0

I found out a solution to my question:

Post.query.filter((Post.id >= x) & (Post.id <= (x + 10))).all()

answered Dec 07 '20 at 02:59

Jake Jackson

1,055
1
12
34

This has two major problems, the first is that without an `order_by` clause you are going to be surprised one day by the results. Also even with mostly sequential ordering provided by the database sequence, you will find that it is not always going to be what you expect. – Dan Safee Jun 21 '22 at 20:39

Flask SQLAlchemy Marshmallow | How do I query all entries between two ID values

2 Answers2