Python, SqlAlchemy. How to make a more efficient search?

Question

I have a problem with a more efficient searching, I have made code in different ways and do not convince me. I'm trying to find an employee with three filters, name, last name and department where he works.

the code in the view is as follows:

if form.nombre.data == u'' and form.apellido.data == u'' and form.departamento.data != u'':
    empleados = db.session.query(Empleado).filter_by(departamento_id=form.departamento.data).all()

elif form.nombre.data == u'' and form.apellido.data != u'' and form.departamento.data == u'':
    empleados = db.session.query(Empleado).filter_by(apellido=form.apellido.data.lower()).all()

elif form.nombre.data == u'' and form.apellido.data != u'' and form.departamento.data != u'':
    empleados = db.session.query(Empleado).filter_by(apellido=form.apellido.data.lower(),
                                                         departamento_id=form.departamento.data).all()

elif form.nombre.data != u'' and form.apellido.data == u'' and form.departamento.data == u'':
    empleados = db.session.query(Empleado).filter_by(nombre=form.nombre.data.lower()).all()

elif form.nombre.data != u'' and form.apellido.data == u'' and form.departamento.data != u'':
    empleados = db.session.query(Empleado).filter_by(nombre=form.nombre.data.lower(),
                                                         departamento_id=form.departamento.data).all()

elif form.nombre.data != u'' and form.apellido.data != u'' and form.departamento.data == u'':
    empleados = db.session.query(Empleado).filter_by(nombre=form.nombre.data.lower(), apellido=form.apellido.data.lower()).all()

elif form.nombre.data != u'' and form.apellido.data != u'' and form.departamento.data != u'':
    empleados = db.session.query(Empleado).filter_by(nombre= form.nombre.data.lower(), apellido=form.apellido.data.lower(), departamento_id=form.departamento.data).all()

else:
    empleados = db.session.query(Empleado).all()

As you can see it is a horrible code. if you were to add a filter more would be a combination of 16 statements if, let alone two more.

Any type of response is welcome. Thank you

Doobeh · Accepted Answer · 2015-09-10 20:56:46Z

1

Just build the query as you go, something like:

query = db.session.query(Empleado)

if form.nombre.data != '':
    query = query.filter_by(nombre=form.nombre.data.lower())
if form.apellido.data != '':
    query = query.filter_by(apellido=form.apellido.data.lower())
if form.departamento.data != '':
    query = query.filter_by(departamento_id=form.departamento.data)

print query.all()

edited Sep 10, 2015 at 20:56

answered Sep 10, 2015 at 20:51

Doobeh

9,48042 silver badges32 bronze badges

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

1 Comment

Mauro Rufino Over a year ago

Thanks for your help. :D

Community · Accepted Answer · 2017-05-23 12:08:17Z

0

You may want to use or_ or and_ filters. Like:

from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))

Collectives™ on Stack Overflow

Python, SqlAlchemy. How to make a more efficient search?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related