For an SQLAlchemy query, how to combine ilike search operator with in_ operator?

Question

I am writing SQLAlchemy code that takes a search bar's input and runs a query against my PostgreSQL database. To allow for misspellings and name fragments, I had this code that did the trick:

q = session.query(Detail).filter((Detail.sellers.ilike("%%%s%%" % (name_input)))).all()

Now I am trying to do the same thing, but to check against a list names that has multiple input values. I've accomplished that like so:

q = session.query(Detail).filter((Detail.sellers.in_(names))

That requires exact matches, however. I would like to combine the ilike operator and the in_ operator. Is this possible? I've tried:

q = session.query(Detail).filter((Detail.sellers.ilike.in_(names))

That gives the following traceback and 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_'

Answer 1

If it's just about the case insensitive 'in_' statement:

from sqlalchemy import func

q = session.query(Detail).filter(
    func.lower(Detail.sellers).in_(map(str.lower, names))
)

Otherwise, if you wanted to have multiple 'ilike' statements,

from sqlalchemy import or_

conditions = []
for name in names:
    conditions.append(Detail.sellers.ilike('%{}%'.format(name)))

q = session.query(Detail).filter(
    or_(*conditions)
)

I haven't tested it, in case it didn't work, I can edit it.