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

Issue

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

Solution

I found out a solution to my question:

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

Answered By – Jake Jackson

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published