CODEWITHSUNDEEP
pythonmysqlpaginationflask
user3751645
user3751645

Reputation: 87

Pagination in Flask using MySql

I searched a lot about it. All the articles i get include SQLAlchemy and none of them deal with mysql. I am working with flask and i have a database in mysql and i need to display the data in pages. Like 1000 images, per page 10 so 100 pages. In mysql we can do pagination with the help of limit. And the routes can be:

@app.route('/images', defaults={'page':1})
@app.route('/images/page/<int:page>')

I need to ask is this all that is needed for pagination? or am i forgetting something important here? and mysql syntax would be:

db = mysql.connect('localhost', 'root', 'password', 'img')
cursor = db.cursor()
cursor.execute('SELECT Id,Title,Img FROM image ORDER BY RAND() limit 20 offset 0;')
data = list(cursor.fetchall())

to get the first 20 results but how to get the next according to the pageno.? Flask-paginate library works only for tSQLAlchemy.

Upvotes: 2

Views: 10246

Answers (3)

Otu William
Otu William

Reputation: 33

Try this flask-mysql-paginate

You could then call the {{paginate.links}} in your html file

Upvotes: 2

Charlie 木匠
Charlie 木匠

Reputation: 2390

Here it is, pure SQL solution.

SELECT * FROM table LIMIT [offset,] rows;
or
SELECT * FROM table LIMIT rows OFFSET offset;

For example:

select * from user
limit 100 offset 850100;

Upvotes: 1

Aarushi
Aarushi

Reputation: 564

Try this 

@app.route('/images', defaults={'page':1})
@app.route('/images/page/<int:page>')
def abc(page):
    perpage=20
    startat=page*perpage
    db = mysql.connect('localhost', 'root', 'password', 'img')
    cursor = db.cursor()
    cursor.execute('SELECT Id,Title,Img FROM image limit %s, %s;', (startat,perpage))
    data = list(cursor.fetchall())

May be this will help.

Upvotes: 5

Related Questions