In Sql Alchemy, how to select rows where any column contains a substring?

ailauli69 0
Viewed 29

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

[EDIT 2]

Here is the "computers1" table that I am trying to request, with two rows:

enter image description here

Here is the entire SQL sentence sent (I forced to search for the string 'eee')=:

[SQL: SELECT computers1.id, computers1.name, computers1.ip, computers1.options
FROM computers1
WHERE (computers1.id LIKE '%%' || %(id_1)s || '%%') OR (computers1.name LIKE '%%' || %(name_1)s || '%%') OR (computers1.ip LIKE '%%' || %(ip_1)s || '%%') OR (computers1.options LIKE '%%' || %(options_1)s || '%%')]
[parameters: {'id_1': 'eee', 'name_1': 'eee', 'ip_1': 'eee', 'options_1': 'eee'}]

But still, doing: conn.execute(THE_SENTENCE).fetchall() returns False...

sqlalchemy fastapi python
1 Answers

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

Toggle0
Related