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
An almost working solution:
with my_engine.connect() as conn: result = conn.execute(select(my_table).where( or_( list_of_columns.contains(q), list_of_columns.contains(q), ... ) ))
But, I need the listing of the columns to be dynamic
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.