python - For an SQLAlchemy query, how to combine ilike search operator with in_ operator? -
i writing sqlalchemy code takes search bar's input , runs query against postgresql database. allow misspellings , name fragments, had code did trick:
q = session.query(detail).filter((detail.sellers.ilike("%%%s%%" % (name_input)))).all()
now trying same thing, check against list names
has multiple input values. i've accomplished so:
q = session.query(detail).filter((detail.sellers.in_(names))
that requires exact matches, however. combine ilike
operator , in_
operator. possible? i've tried:
q = session.query(detail).filter((detail.sellers.ilike.in_(names))
that gives following traceback , error:
file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__ return self.wsgi_app(environ, start_response) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app response = self.make_response(self.handle_exception(e)) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception reraise(exc_type, exc_value, tb) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app response = self.full_dispatch_request() file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request rv = self.handle_user_exception(e) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception reraise(exc_type, exc_value, tb) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) file "/users/tom/projects/land-records/repo/scripts/app.py", line 162, in search return query_db(names) file "/users/tom/projects/land-records/lib/python2.7/site-packages/flask_cache/__init__.py", line 537, in decorated_function rv = f(*args, **kwargs) file "/users/tom/projects/land-records/repo/scripts/app.py", line 106, in query_db q = session.query(detail).filter((detail.sellers.ilike.in_(names))).all() attributeerror: 'function' object has no attribute 'in_'
if it's case insensitive 'in_' statement:
from sqlalchemy import func q = session.query(detail).filter( func.lower(detail.sellers).in_(map(str.lower, names)) )
otherwise, if wanted have multiple 'ilike' statements,
from sqlalchemy import or_ conditions = [] name in names: conditions.append(detail.sellers.ilike('%{}%'.format(name))) q = session.query(detail).filter( or_(*conditions) )
i haven't tested it, in case didn't work, can edit it.
Comments
Post a Comment