librelist archives

« back to archive

Pagination: avoiding to rerun each time count()

Pagination: avoiding to rerun each time count()

From:
Mic
Date:
2014-10-31 @ 11:52
Hi,

I found Flask-paginate ( https://pythonhosted.org/Flask-paginate/ )
extension which also provides a nice code example (
https://github.com/lixxu/flask-paginate/tree/master/example ).

I looked at the example code (
https://github.com/lixxu/flask-paginate/tree/master/example ) and it
produces a nice result for the small users example. For this example it is
quick for each page in the pagination to rerun *g.cur.execute('select
count(*) from users')*, but if the database is big then it will take a
while to rerun the *count(*)* each time.

I was not quite sure whether it is an issue in Flask-paginate and therefore
I posted it to github (https://github.com/lixxu/flask-paginate/issues/22)

How is possible to avoid to rerun *count(*)* for each page in pagination or
is there a way to save the count result temporary or anyhow to do it with
sessions?

Thank you in advance.

Mic

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Leandro Poblet
Date:
2014-10-31 @ 12:40
If you want to save it into a session, it would depend a lot on how long
the session lasts and how variable the content is. But, to achieve it, you
just need to do the following:

if 'paginate' not in session: # To avoid the rewrite
    session['paginate'] = get_pagination(...)

2014-10-31 8:52 GMT-03:00 Mic <mictadlo@gmail.com>:

> Hi,
>
> I found Flask-paginate ( https://pythonhosted.org/Flask-paginate/ )
> extension which also provides a nice code example (
> https://github.com/lixxu/flask-paginate/tree/master/example ).
>
> I looked at the example code (
> https://github.com/lixxu/flask-paginate/tree/master/example ) and it
> produces a nice result for the small users example. For this example it is
> quick for each page in the pagination to rerun *g.cur.execute('select
> count(*) from users')*, but if the database is big then it will take a
> while to rerun the *count(*)* each time.
>
> I was not quite sure whether it is an issue in Flask-paginate and
> therefore I posted it to github (
> https://github.com/lixxu/flask-paginate/issues/22
> <https://github.com/lix%20xu/flask-paginate/issues/22>)
>
> How is possible to avoid to rerun *count(*)* for each page in
> pagination or is there a way to save the count result temporary or anyhow
> to do it with sessions?
>
> Thank you in advance.
>
> Mic
>

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Mic
Date:
2014-10-31 @ 13:10
Hi Leandro,
I am still not quite sure how to avoid to rerun this:

       *g.cur.execute('select count(*) from users')*  *       total =
g.cur.fetchone()[0]*

because *get_pagination(total=total, ....)* requires the total variable.

On Fri, Oct 31, 2014 at 10:40 PM, Leandro Poblet <leandrodrhouse@gmail.com>
wrote:

> If you want to save it into a session, it would depend a lot on how long
> the session lasts and how variable the content is. But, to achieve it, you
> just need to do the following:
>
> if 'paginate' not in session: # To avoid the rewrite
>     session['paginate'] = get_pagination(...)
>
> 2014-10-31 8:52 GMT-03:00 Mic <mictadlo@gmail.com>:
>
>> Hi,
>>
>> I found Flask-paginate ( https://pythonhosted.org/Flask-paginate/ )
>> extension which also provides a nice code example ( https://github.com/li
>> xxu/flask-paginate/tree/master/example
>> <https://github.com/lixxu/flask-paginate/tree/master/example> ).
>>
>> I looked at the example code (
>> https://github.com/lixxu/flask-paginate/tree/master/example ) and it
>> produces a nice result for the small users example. For this example it is
>> quick for each page in the pagination to rerun *g.cur.execute('select
>> count(*) from users')*, but if the database is big then it will take a
>> while to rerun the *count(*)* each time.
>>
>> I was not quite sure whether it is an issue in Flask-paginate and
>> therefore I posted it to github (
>> https://github.com/lixxu/flask-paginate/issues/22
>> <https://github.com/lix%20xu/flask-paginate/issues/22>)
>>
>> How is possible to avoid to rerun *count(*)* for each page in
>> pagination or is there a way to save the count result temporary or anyhow
>> to do it with sessions?
>> Thank you in advance.
>>
>> Mic
>>
>
>

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Matthias Urlichs
Date:
2014-10-31 @ 13:38
Hi,

Mic:
> Hi Leandro,
> I am still not quite sure how to avoid to rerun this:
>       g.cur.execute('select count(*) from users')
>       total = g.cur.fetchone()[0]
> 
> because get_pagination(total=total, ....) requires the total variable.

Simple: store the total in the session, not the result of get_pagination().

>>> total_users = session.get('total_users',None)
>>> if total_users is None:
>>>     g.cur.execute('select count(*) from users')
>>>     session['total_users'] = total_users = g.cur.fetchone()[0]
>>> 
>>> … get_pagination(total=total_users, …)

-- 
-- Matthias Urlichs

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Mic
Date:
2014-11-01 @ 01:00
Hi,
Thank you, I think it is working:

*#!/usr/bin/env python*
*#-*- coding: utf-8 -*-*

*from __future__ import unicode_literals*
*import sqlite3*
*from flask import Flask, render_template, g, current_app, request, session*
*from flask.ext.paginate import Pagination*

*app = Flask(__name__)*
*app.config.from_pyfile('app.cfg')*


*@app.before_request*
*def before_request():*
*    g.conn = sqlite3.connect('test.db')*
*    g.conn.row_factory = sqlite3.Row*
*    g.cur = g.conn.cursor()*


*@app.teardown_request*
*def teardown(error):*
*    if hasattr(g, 'conn'):*
*        g.conn.close()*


*@app.route('/')*
*def index():*

*    total_users = session.get('total_users',None)*
*    if total_users is None:*
*        g.cur.execute('select count(*) from users')*
*        session['total_users'] = total_users = g.cur.fetchone()[0]*

*    page, per_page, offset = get_page_items()*
*    sql = 'select name from users order by name limit {}, {}'\*
*        .format(offset, per_page)*
*    g.cur.execute(sql)*
*    users = g.cur.fetchall()*
*    pagination = get_pagination(page=page,*
*                                per_page=per_page,*
*                                total=total_users,*
*                                record_name='users',*
*                                )*
*    return render_template('index.html', users=users,*
*                           page=page,*
*                           per_page=per_page,*
*                           pagination=pagination,*
*                           )*


*def get_css_framework():*
*    return current_app.config.get('CSS_FRAMEWORK', 'bootstrap3')*


*def get_link_size():*
*    return current_app.config.get('LINK_SIZE', 'sm')*


*def show_single_page_or_not():*
*    return current_app.config.get('SHOW_SINGLE_PAGE', False)*


*def get_page_items():*
*    page = int(request.args.get('page', 1))*
*    per_page = request.args.get('per_page')*
*    if not per_page:*
*        per_page = current_app.config.get('PER_PAGE', 10)*
*    else:*
*        per_page = int(per_page)*

*    offset = (page - 1) * per_page*
*    return page, per_page, offset*


*def get_pagination(**kwargs):*
*    kwargs.setdefault('record_name', 'records')*
*    return Pagination(css_framework=get_css_framework(),*
*                      link_size=get_link_size(),*
*                      show_single_page=show_single_page_or_not(),*
*                      **kwargs*
*                      )*

*if __name__ == '__main__':*
*    app.secret_key =

"\x94\xa9\xef\x8d\xc8\x18g\x1c\xb5x\xd8\x11\x88'\xf4r\xa5\xbcw\x99\xe0\xda\xb7\x10"*
*    app.run(debug=True)*

Any suggestions to improve it?

Thank you in advance.

Mic

On Fri, Oct 31, 2014 at 11:38 PM, Matthias Urlichs <matthias@urlichs.de>
wrote:

> Hi,
>
> Mic:
> > Hi Leandro,
> > I am still not quite sure how to avoid to rerun this:
> >       g.cur.execute('select count(*) from users')
> >       total = g.cur.fetchone()[0]
> >
> > because get_pagination(total=total, ....) requires the total variable.
>
> Simple: store the total in the session, not the result of get_pagination().
>
> >>> total_users = session.get('total_users',None)
> >>> if total_users is None:
> >>>     g.cur.execute('select count(*) from users')
> >>>     session['total_users'] = total_users = g.cur.fetchone()[0]
> >>>
> >>> … get_pagination(total=total_users, …)
>
> --
> -- Matthias Urlichs
>

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Paul Korzhyk
Date:
2014-11-01 @ 02:01
Hi Mick,

Your code looks OK to me. One thing you could add is expiry time for the
total.

Something like this will refresh the count every 5 minutes: (hopefully
others will correct any stupid mistakes I made in the snippet. Always get
lost in the time conversions modules)

...

MAX_TOTAL_USERS_AGE = 300
...

*total_users = session.get('total_users',None)*
*total_users_expire = **session.get('total_users_expire', -1)*
*cur_time = getnow()*
*if total_users is None or total_users_expire < cur_time:*
*      g.cur.execute('select count(*) from users')*
*      session['total_users'] = total_users = g.cur.fetchone()[0]*
      *session['total_users_expire'] = cur_time + *MAX_TOTAL_USERS_AGE




On Sat, Nov 1, 2014 at 12:00 PM, Mic <mictadlo@gmail.com> wrote:

> Hi,
> Thank you, I think it is working:
>
> *#!/usr/bin/env python*
> *#-*- coding: utf-8 -*-*
>
> *from __future__ import unicode_literals*
> *import sqlite3*
> *from flask import Flask, render_template, g, current_app, request,
> session*
> *from flask.ext.paginate import Pagination*
>
> *app = Flask(__name__)*
> *app.config.from_pyfile('app.cfg')*
>
>
> *@app.before_request*
> *def before_request():*
> *    g.conn = sqlite3.connect('test.db')*
> *    g.conn.row_factory = sqlite3.Row*
> *    g.cur = g.conn.cursor()*
>
>
> *@app.teardown_request*
> *def teardown(error):*
> *    if hasattr(g, 'con n'):*
> *        g.conn.close()*
>
>
> *@app.route('/')*
> *def index():*
>
> *    total_users = session.get('total_users',None)*
> *    if total_users is None:*
> *        g.cur.execute('select count(*) from users')*
> *        session['total_users'] = total_users = g.cur.fetchone()[0]*
>
> *    page, per_page, offset = get_page_items()*
> *    sql = 'select name from users order by name limit {}, {}'\*
> *        .format(offset, per_page)*
> *    g.cur.execute(sql)*
> *    users = g.cur.fetchall()*
> *    pagination = get_pagination(page=page,*
> *                                per_page=per_page,*
> *                                total=total_users,*
> *                                record_name='users',*
> *                                )*
> *    return render_template('index.html', users=users,*
> *                           page=page,*
> *                           per_page=per_page,*
> *                           pagination=pagination,*
> *                           )*
>
>
> *def get_css_framework():*
> *    return current_app.config.get('CSS_FRAMEWORK', 'bootstrap3')*
>
>
> *def get_link_size():*
> *    return current_app.config.get('LINK_SIZE', 'sm')*
>
>
> *def s how_single_page_or_not():*
> *    return current_app.config.get('SHOW_SINGLE_PAGE', False)*
>
>
> *def get_page_items():*
> *    page = int(request.args.get('page', 1))*
> *    per_page = request.args.get('per_page')*
> *    if not per_page:*
> *        per_page = current_app.config.get('PER_PAGE', 10)*
> *    else:*
> *        per_page = int(per_page)*
>
> *    offset = (page - 1) * per_page*
> *    return page, per_page, offset*
>
>
> *def get_pagination(**kwargs):*
> *    kwargs.setdefault('record_name', 'records')*
> *    return Pagination(css_framework=get_css_framework(),*
> *                      link_size=get_link_ size(),*
> *                      show_single_page=show_single_page_or_not(),*
> *                      **kwargs*
> *                      )*
>
> *if __name__ == '__main__':*
> *    app.secret_key =
> 
"\x94\xa9\xef\x8d\xc8\x18g\x1c\xb5x\xd8\x11\x88'\xf4r\xa5\xbcw\x99\xe0\xda\xb7\x10"*
> *    app.run(debug=True)*
>
> Any suggestions to improve it?
>
> Thank you in advance.
>
> Mic
>
> On Fri, Oct 31, 2014 at 11:38 PM, Matthias Urlichs <matthias@urlichs.de>
> wrote:
>
>> Hi,
>>
>> Mic:
>> > Hi Leandro,
>> > I am still not quite sure how to avoid to rerun this:
>> >       g.cur.execute('select count(*) from users')
>> >       total = g.cur.fetchone()[0]
>> >
>> > because get_pagination(total=total, ....) requires the total variable.
>>
>> Simple: store the total in the session, not the result of
>> get_pagination().
>>
>> >>> total_users = session.get('total_users',None)
>> >>> if total_users is None:
>> >>>     g.cur.execute('select count(*) from users')
>> >>>     session['total_users'] = total_users = g.cur.fetchone()[0]
>> >>>
>> >>> … get_pagination(total=total_users, …)
>>
>> --
>> -- Matthias Urlichs
>>
>
>

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Paul Korzhyk
Date:
2014-11-01 @ 02:02
Hit send too soon.
Instead of getnow() I was going to write
int(time.mktime(datetime.datetime.now().timetuple()))

On Sat, Nov 1, 2014 at 1:01 PM, Paul Korzhyk <paul.korzhyk@gmail.com> wrote:

> Hi Mick,
>
> Your code looks OK to me. One thing you could add is expiry time for the
> total.
>
> Something like this will refresh the count every 5 minutes: (hopefully
> others will correct any stupid mistakes I made in the snippet. Always get
> lost in the time conversions modules)
>
> ...
>
> MAX_TOTAL_USERS_AGE = 300
> ...
>
> *total_users = session.get('total_users',None)*
> *total_users_expire = **session.get('total_users_expire', -1)*
> *cur_time = getnow()*
> *if total_users is None or total_users_expire < cur_time:*
> *      g.cur.execute('select count(*) from users')*
> *      session['total_users'] = total_users = g.cur.fetchone()[0]*
>       *session['total_users_expire'] = cur_time + *MAX_TOTAL_USERS_AGE
>
>
>
>
> On Sat, Nov 1, 2014 at 12:00 PM, Mic <mictadlo@gmail.com> wrote:
>
>> Hi,
>> Thank you, I think it is working:
>>
>> *#!/usr/bin/env python*
>> *#-*- coding: utf-8 -*-*
>>
>> *from __future__ import unicode_literals*
>> *import sqlite3*
>> *from flask import Flask, render_template, g, current_app, request,
>> session*
>> *from flask.ext.paginate import Pagination*
>>
>> *app = Flask(__name__)*
>> *app.config.from_pyfile('app.cfg')*
>>
>>
>> *@app.before_request*
>> *def before_request():*
>> *    g.conn = sqlite3.connect('test.db')*
>> *    g.conn.row_factory = sqlite3.Row*
>> *    g.cur = g.conn.cursor()*
>>
>>
>> *@app.teardown_request*
>> *def teardown(error):*
>> *    if hasattr(g, 'con n'):*
>> *        g.conn.close()*
>>
>>
>> *@app.route('/')*
>> *def index():*
>>
>> *    total_users = session.get('total_users',None)*
>> *    if total_users is None:*
>> *        g.cur.execute('select count(*) from users')*
>> *        session['total_users'] = total_users = g.cur.fetchone()[0]*
>>
>> *    page, per_page, offset = get_page_items()*
>> *    sql = 'select name from users order by name limit {}, {}'\*
>> *        .format(offset, per_page)*
>> *    g.cur.execute(sql)*
>> *    users = g.cur.fetchall()*
>> *    pagination = get_pagination(page=page,*
>> *                                per_page=per_page,*
>> *                                total=total_users,*
>> *                                record_name='users',*
>> *                                )*
>> *    return render_template('index.html', users=users,*
>> *                           page=page,*
>> *                           per_page=per_page,*
>> *                           pagination=pagination,*
>> *                           )*
>>
>>
>> *def get_css_framework():*
>> *    return current_app.config.get('CSS_FRAMEWORK', 'bootstrap3')*
>>
>>
>> *def get_link_size():*
>> *    return current_app.config.get('LINK_SIZE', 'sm')*
>>
>>
>> *def s how_single_page_or_not():*
>> *    return current_app.config.get('SHOW_SINGLE_PAGE', False)*
>>
>>
>> *def get_page_items():*
>> *    page = int(request.args.get('page', 1))*
>> *    per_page = request.args.get('per_page')*
>> *    if not per_page:*
>> *        per_page = current_app.config.get('PER_PAGE', 10)*
>> *    else:*
>> *        per_page = int(per_page)*
>>
>> *    offset = (page - 1) * per_page*
>> *    return page, per_page, offset*
>>
>>
>> *def get_pagination(**kwargs):*
>> *    kwargs.setdefault('record_name', 'records')*
>> *    return Pagination(css_framework=get_css_framework(),*
>> *                      link_size=get_link_ size(),*
>> *                      show_single_page=show_single_page_or_not(),*
>> *                      **kwargs*
>> *                      )*
>>
>> *if __name__ == '__main__':*
>> *    app.secret_key =
>> 
"\x94\xa9\xef\x8d\xc8\x18g\x1c\xb5x\xd8\x11\x88'\xf4r\xa5\xbcw\x99\xe0\xda\xb7\x10"*
>> *    app.run(debug=True)*
>>
>> Any suggestions to improve it?
>>
>> Thank you in advance.
>>
>> Mic
>>
>> On Fri, Oct 31, 2014 at 11:38 PM, Matthias Urlichs <matthias@urlichs.de>
>> wrote:
>>
>>> Hi,
>>>
>>> Mic:
>>> > Hi Leandro,
>>> > I am still not quite sure how to avoid to rerun this:
>>> >       g.cur.execute('select count(*) from users')
>>> >       total = g.cur.fetchone()[0]
>>> >
>>> > because get_pagination(total=total, ....) requires the total variable.
>>>
>>> Simple: store the total in the session, not the result of
>>> get_pagination().
>>>
>>> >>> total_users = session.get('total_users',None)
>>> >>> if total_users is None:
>>> >>>     g.cur.execute('select count(*) from users')
>>> >>>     session['total_users'] = total_users = g.cur.fetchone()[0]
>>> >>>
>>> >>> … get_pagination(total=total_users, …)
>>>
>>> --
>>> -- Matthias Urlichs
>>>
>>
>>
>

Re: [flask] Pagination: avoiding to rerun each time count()

From:
Mic
Date:
2014-11-05 @ 03:51
Thank you for all the help.



On Sat, Nov 1, 2014 at 12:02 PM, Paul Korzhyk <paul.korzhyk@gmail.com>
wrote:

> Hit send too soon.
> Instead of getnow() I was going to write
> int(time.mktime(datetime.datetime.now().timetuple()))
>
> On Sat, Nov 1, 2014 at 1:01 PM, Paul Korzhyk <paul.korzhyk@gmail.com>
> wrote:
>
>> Hi Mick,
>>
>> Your code looks OK to me. One thing you could add is expiry time for the
>> total.
>>
>> Something like this will refresh the count every 5 minutes: (hopefully
>> others will correct any stupid mistakes I made in the snippet. Always get
>> lost in the time conversions modules)
>>
>> ...
>>
>> MAX_TOTAL_USERS_AGE = 300
>> ...
>>
>> *total_users = session.get('total_users',None)*
>> *total_users_expire = **session.get('total_users_expire', -1)*
>> *cur_time = getnow()*
>> *if total_users is None or total_users_expire < cur_time:*
>> *      g.cur.execute('select count(*) from users')*
>> *      session['total_users'] = total_users = g.cur.fetchone()[0]*
>>       *session['total_users_expire'] = cur_time + <
>> /i>MAX_TOTAL_USERS_AGE*
>>
>>
>>
>>
>> On Sat, Nov 1, 2014 at 12:00 PM, Mic <mictadlo@gmail.com> wrote:
>>
>>> Hi,
>>> Thank you, I think it is working:
>>>
>>> *#!/usr/bin/env python*
>>> *#-*- coding: utf-8 -*-*
>>>
>>> *from __future__ import unicode_literals*
>>> *import sqlite3*
>>> *from flask import Flask, render_template, g, current_app, request,
>>> session*
>>> *from flask.ext.paginate import Pagination*
>>>
>>> *app = Flask(__name__)*
>>> *app. config.from_pyfile('app.cfg')*
>>>
>>>
>>> *@app.before_request*
>>> *def before_request():*
>>> *    g.conn = sqlite3.connect('test.db')*
>>> *    g.conn.row_factory = sqlite3.Row*
>>> *    g.cur = g.conn.cursor()*
>>>
>>>
>>> *@app.teardown_request*
>>> *def teardown(error):*
>>> *    if hasattr(g, 'con n'):*
>>> *        g.conn.close()*
>>>
>>>
>>> *@app.route('/')*
>>> *def index():*
>>>
>>> *    total_users = session.get('total_users',None)*
>>> *    if total_users is None:*
>>> *        g.cur.execute('select count(*) from users')*
>>> *        session['total_users'] = total_users = g.cur.fetchone()[0]*
>>>
>>> *    page, per_page, offset = get_page_items()*
>>> *    sql = 'select name from users order by name limit {}, {}'\*
>>> *        .format(offset, per_page)*
>>> *    g.cur.execute(sql)*
>>> *    users = g.cur.fetchall()*
>>> *    pagination = get_pagination(page=page,*
>>> *                                per_page=per_page,*
>>> *                                total=total_users,*
>>> *                                record_name='users',*
>>> *                                )*
>>> *    return render_template('index.html', users=users,*
>>> *                           page=page,*
>>> *                           per_page=per_page,*
>>> *                           pagination=pagination,*
>>> *                           )*
>>>
>>>
>>> *def get_css_framework():*
>>> *    return current_app.config.get('CSS_FRAMEWORK', 'bootstrap3')*
>>>
>>>
>>> *def get_link_size():*
>>> *    return current_app.config.get('LINK_SIZE', 'sm')*
>>>
>>>
>>> *def s how_single_page_or_not():*
>>> *    return current_app.config.get('SHOW_SINGLE_PAGE', False)*
>>>
>>>
>>> *def get_page_items():*
>>> *    page = int(request.args.get('page', 1))*
>>> *    per_page = request.args.get('per_page')*
>>> *    if not per_page:*
>>> *        per_page = current_app.config.get('PER_PAGE', 10)*
>>> *    else:*
>>> *        per_page = int(per_page)*
>>>
>>> *    offset = (page - 1) * per_page*
>>> *    return page, per_page, offset*
>>>
>>>
>>> *def get_pagination(**kwargs):*
>>> *    kwargs.setdefault('record_name', 'records')*
>>> *    return Pagination(css_framework=get_css_framework(),*
>>> *                      link_size=get_link_ size(),*
>>> *                      show_single_page=show_single_page_or_not(),*
>>> *                      **kwargs*
>>> *                      )*
>>>
>>> *if __name__ == '__main__':*
>>> *    app.secret_key =
>>> 
"\x94\xa9\xef\x8d\xc8\x18g\x1c\xb5x\xd8\x11\x88'\xf4r\xa5\xbcw\x99\xe0\xda\xb7\x10"*
>>> *    app.run(debug=True)*
>>>
>>> Any suggestions to improve it?
>>>
>>> Thank you in advance.
>>>
>>> Mic
>>>
>>> On Fri, Oct 31, 2014 at 11:38 PM, Matthias Urlichs <matthias@urlichs.de>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> Mic:
>>>> > Hi Leandro,
>>>> > I am still not quite sure how to avoid to rerun this:
>>>> >       g.cur.execute('select count(*) from users')
>>>> >       total = g.cur.fetchone()[0]
>>>> >
>>>> > because get_pagination(total=total, ....) requires the total variable.
>>>>
>>>> Simple: store the total in the session, not the result of
>>>> get_pagination().
>>>>
>>>> >>> total_users = session.get('total_users',None)
>>>> >>> if total_users is None:
>>>> >>>     g.cur.execute('select count(*) from users')
>>>> >>>     session['total_users'] = total_users = g.cur.fetchone()[0]
>>>> >>>
>>>> >>> … get_pagination(total=total_users, …)
>>>>
>>>> --
>>>> -- Matthias Urlichs
>>>>
>>>
>>>
>>
>