How to use sqlalchemy core for pagination in Flask?

Question

I am using flask framework with SQLAlchemy core only. My result set returned from the 'select' statement contains few thousands of records. I would like to use pagination to avoid memory error. How can I get a JSON output using cursor for api GET method, for any page dynamically? I have tried the accepted solution here How to use cursor() for pagination?, but could not quite get it right. Please guide. Using PostgreSQL 9.4 Options I have tried:

1.

@app.route('/test/api/v1.0/docs_page/<int:page>', methods=['POST'])
def search_docs_page(page):
    if 'id' in session:
        doc_list = []
        no_of_pgs = 0
        doc_list = common_search_code()
        # doc_list is a resultset after querying the table for a set of query condition  
        header_dict = dict(request.headers)
        for i in header_dict.items():
            if i == 'Max-Per-Page':
                max_per_pg = int(header_dict[i])
        no_of_pgs = len(doc_list) / max_per_pg
        print 'number of doc: ' + str(len(doc_list))
        print 'number of pages: ' +  str(no_of_pgs)
        print 'max per page:' + str(max_per_pg)
        print 'page number: '+ str(page)
        page = int(request.args.get(page, type=int, default=1))
        pagination = Pagination(page=page,
                                total=len(doc_list),
                                search=True, record_name='documents')

        return jsonify({'pagination': list(pagination.__dict__),
                        'doc_list': doc_list}), 200

    return jsonify({'message': "Unauthorized"}), 401

I want to control the number of records to be printed in each page by passing the parameter in the request header as in this curl:

curl -b cookies.txt -X POST http://localhost:8081/test/api/v1.0/docs_page/1 -H 'max_per_page:4' -H 'Content-type:application/json'

@app.route('/test/api/v1.0/docs_page2', methods=['POST'])
 def search_docs_page2():
     if 'id' in session:
         docs = []
         no_of_pgs = 0
         doc_list = common_search_code()
         header_dict = dict(request.headers)
         for i in header_dict.items():
             if i == 'Max-Per-Page':
                 max_per_pg = int(header_dict[i])
         no_of_pgs = len(doc_list) / max_per_pg
         print 'number of doc: ' + str(len(doc_list))
         print 'number of pages: ' +  str(no_of_pgs)
         print 'max per page:' + str(max_per_pg)
         page = int(request.form.get('page', type=int, default=1))
         cursor = request.form.get('cursor')
         if cursor:
             print 'hey'
             doc_list.with_cursor(cursor)
             docs = doc_list.fetchmany(4)
             for r in docs:
                 print r, 'rrrr'

         return jsonify({'docs': docs}), 200

     return jsonify({'message': "Unauthorized"}), 401







curl:curl -b cookies.txt  -X POST http://localhost:8081/test/api/v1.0/docs_page2 -H 'max_per_page:4' -H 'Content-type:application/json' -F 'cursor=1'

`dict(header_dict.items())` is unecesssary. It creates a new dictionary from a dictionary. Just iterate over `header_dict`. And if all you care about is one key, check for that key instead of iterating over them all. `max_per_pg = header_dict['Max-Per-Page']`. — dirn, Sep 06 '16 at 11:23
Does `common_search_code` return the cursor? How are you querying the database? Are you using `engine.execute` or table objects? Also, the question you linked to is about Google App Engine and doesn't apply to SQLAlchemy. — dirn, Sep 06 '16 at 13:13
common_search_code returns a document list after search criteria is applied. I have used rs = conn.execute(s), And then based on some condition created the list of documents returned here. — user956424, Sep 07 '16 at 01:55

user956424 · Accepted Answer · 2016-09-10T06:51:41.043

Currently, I have made changes to the above code to work this way:

@app.route('/test/api/v1.0/docs_page2', methods=['POST'])
def search_docs_page2():
    if 'id' in session:
        docs = []
        no_of_pgs = 0
        header_dict = dict(request.headers)
        for k in header_dict:
            print header_dict[k], 'key', k
        if 'Max-Per-Page' in header_dict.keys():
            max_per_pg = int(header_dict['Max-Per-Page'])
            page_no = int(request.headers.get('page_no', type=int, default=1))
            offset1 = (page_no - 1) * max_per_pg
            query1 = common_search_code()
            s = query1.limit(max_per_pg).offset(offset1)
            rs = conn.execute(s)
            for r in rs:
                docs.append(dict(r))
            # no_of_pgs = float(len(doc_list) / float(max_per_pg))
            no_of_pgs = float(len(docs) / float(max_per_pg))
            no_of_pgs = int(math.ceil(no_of_pgs))
            print 'number of doc: ' + str(len(docs))
            print 'number of pages: ' +  str(no_of_pgs)
            print 'max per page:' + str(max_per_pg)
            # docs.append(doc_list[offset:(offset + max_per_pg)])
            return jsonify({'docs': docs,
                            'no_of_pages': str(no_of_pgs)}), 200

        return jsonify({'message': "Max. per page not specified"}), 400

    return jsonify({'message': "Unauthorized"}), 401

Curl for the same:

curl -b cookies.txt  -X POST http://localhost:8081/test/api/v1.0/docs_page2 -H 'max_per_page:4' -H 'Content-type:application/json' -H 'page_no:2'

How to use sqlalchemy core for pagination in Flask?

1 Answers1