user956424 user956424 - 2 months ago 31
Python Question

How to use sqlalchemy core for pagination in Flask?

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'

Answer

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'
Comments