Issue

This Content is from Stack Overflow. Question asked by ailauli69

I want to retrieve all the rows of a table where a substring “h” is contained in any of the columns)

I tried something like this:

list_of_columns = [c for c in my_table.columns] # where my_table is of class Table

with my_engine.connect() as conn:

    result = conn.execute(select(my_table).where(
            list_of_columns.contains(q),
    ))

Of course this does not work, as “contains()” should be called on a single column…

Any idea ?

p.s: the retrieving of the columns must be dynamic, this is the way my code must work

[EDIT]
An almost working solution:

with my_engine.connect() as conn:

    result = conn.execute(select(my_table).where(
            or_(
                list_of_columns[0].contains(q),
                list_of_columns[1].contains(q),
                ...
            )
    ))

But, I need the listing of the columns to be dynamic

Solution

You can use a list comprehension to add all the columns to the query:

with my_engine.connect() as conn:
    result = conn.execute(select(my_table).where(
        or_(*[col.contains(q) for col in list_of_columns])
    ))

For this kind of search, you might also get better performance and results by using postgresql’s full-text search functionality, and creating a tsvector that combines all of the columns: https://stackoverflow.com/a/42390204/16811479

This Question was asked in StackOverflow by ailauli69 and Answered by Toggle It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.