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-10T20:56:46.683

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 '15 at 20:56

answered Sep 10 '15 at 20:51

Doobeh

9,280
39
32

Thanks for your help. :D – Mauro Rufino Sep 10 '15 at 21:21

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

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

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

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

2 Answers2