Python Flask-sqlAlchemy Prepapre query

Question

I'm working on an API made with python flask-SQLalchemy.

I'm looking for an elegant way prepare my query step by step.

Today my code is working but seam very hugly to me because of many duplicated content :

   if filteron is None:
    if orderby == 'config':
        if order == 'DESC':
            job = JobFull.query.order_by(JobFull.config_id.desc()).limit(limit).all()
        else:
            job = JobFull.query.order_by(JobFull.config_id.asc()).limit(limit).all()

    elif orderby == 'crawler':
        if order == 'DESC':
            job = JobFull.query.order_by(JobFull.crawler_id.desc()).limit(limit).all()
        else:
            job = JobFull.query.order_by(JobFull.crawler_id.asc()).limit(limit).all()

    elif orderby == 'site':
        if order == 'DESC':
            job = JobFull.query.order_by(JobFull.site_id.desc()).limit(limit).all()
        else:
            job = JobFull.query.order_by(JobFull.site_id.asc()).limit(limit).all()

    else:
        if order == 'DESC':
            job = JobFull.query.order_by(JobFull.job_id.desc()).limit(limit).all()
        else:
            job = JobFull.query.order_by(JobFull.job_id.asc()).limit(limit).all()

what i would like to do is prepare my query like :

if filteron is None:
    if order == 'DESC':
        job = job.query.orderby.desc()
    if orderby == 'config':
        job = job.query.orderby.(JobFull.config_id)
    if limit is not None:
        job = job.limit(limit)

is there an elegant way to do that or does I need to continue in my if nightmare ?

regards,

PGHE · Accepted Answer · 2020-12-17 01:32:26Z

2

Extrapolate your logic into a reusable function, combined with the getattr function.

def create_query(model, orderby: str='', desc: bool=False, limit: int=0):
    """ model: your SQLAlchemy Model
        orderby: the name of the column you want to order by
        desc: switch to order by Descending
        limit: limit the number of results returned"""

    query = model.query
    if orderby:
        col = getattr(model, orderby)
        col = col.desc() if desc else col.asc()
        query = query.order_by(col)

    if limit:
        query = query.limit(limit)

    return query.all()

answered Dec 17, 2020 at 1:32

PGHE

2,11217 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicolas Over a year ago

Hi, sorry for delay... thanks for this proposal who look very sexy to me ! i'll try it asap and feedback

Collectives™ on Stack Overflow

Python Flask-sqlAlchemy Prepapre query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related