How to combine queries with a single external variable using Pandas

Use sets. You can change the text columns to sets and check for intersection with the input. The rest is pure pandas. I never use .query because it is slow.

# change your search from list to set
search = set(request.values.get('searchinput').replace(" ","").split(','))

filtered = df.loc[(df['Drug Name'].str.split().map(lambda x: set(x).intersection(search)))
                  & (df['BP'].str.split().map(lambda x: set(x).intersection(search)))]

print(filtered)

Demo:

import pandas as pd

search = set(["apple", "banana", "orange"])
df = pd.DataFrame({
    "Drug Name": ["I am eating an apple", "We are banana", "nothing is here"],
    "BP": ["apple is good", "nothing is here", "nothing is there"],
    "Just": [1, 2, 3]
})


filtered = df.loc[(df['Drug Name'].str.split().map(lambda x: set(x).intersection(search)))
                  & (df['BP'].str.split().map(lambda x: set(x).intersection(search)))]

print(filtered)

#               Drug Name             BP  Just
# 0  I am eating an apple  apple is good     1

Updated:

I would want the results to also show We are banana, nothing is here and 2

That requires or which is Pandas' | instead of and which Pandas' $

filtered = df.loc[(df['Drug Name'].str.split().map(lambda x: set(x).intersection(search)))
                  | (df['BP'].str.split().map(lambda x: set(x).intersection(search)))]

print(filtered)

#                Drug Name               BP  Just
# 0  I am eating an apple    apple is good       1
# 1          We are banana   nothing is here     2

If you want to search for text in all columns, you can first join all columns, and then check for search terms in each row using str.contains and the regular expression pattern that matches at least one of the terms (term1|term2|...|termN). I've also added flags=re.IGNORECASE to make the search case-insensitive:

# search function
def search(searchterms):
    return df.loc[df.apply(' '.join, axis=1)    # join text in all columns
        .str.contains(                          # check if it contains
            '|'.join([                          # regex pattern
                x.strip()                       # strip spaces
                for x in searchterms.split(',') # split by ','
            ]), flags=re.IGNORECASE)]           # case-insensitive

# test search terms
for s in ['Alendronic Acid', 'months', 'Perindopril, Allopurinol']:
    print(f'Search terms: "{s}"')
    print(search(s))
    print('-'*70)

Output:

Search terms: "Alendronic Acid"
         Drug Name          BP              U&E
1  Alendronic Acid  Not needed  Every 12 months
----------------------------------------------------------------------
Search terms: "months"
         Drug Name                           BP              U&E
0      Perindopril              Every 12 months       Not needed
1  Alendronic Acid                   Not needed  Every 12 months
2      Allopurinol  Whilst titrating - 3 months       Not needed
----------------------------------------------------------------------
Search terms: "Perindopril, Allopurinol"
     Drug Name                           BP         U&E
0  Perindopril              Every 12 months  Not needed
2  Allopurinol  Whilst titrating - 3 months  Not needed
----------------------------------------------------------------------

P.S. If you want to limit search to specific columns, here's a version that does that (with the default of searching all columns for convenience):

# search function
def search(searchterms, cols=None):
    
    # search columns (if None, searches in all columns)
    if cols is None:
        cols = df.columns
        
    return df.loc[df[cols].apply(' '.join, axis=1) # join text in cols
        .str.contains(                             # check if it contains
            '|'.join([                             # regex pattern
                x.strip()                          # remove spaces
                for x in searchterms.split(',')    # split by ','
            ]), flags=re.IGNORECASE)]              # make search case-insensitive

Now if I search for months only in Drug Name and BP, it will not return Alendronic Acid where months is only found in U&E:

search('months', ['Drug Name', 'BP'])

Output:

     Drug Name                           BP         U&E
0  Perindopril              Every 12 months  Not needed
2  Allopurinol  Whilst titrating - 3 months  Not needed

I am assuming you want to query 2 columns and want to return the row if any of the query matches.

In this line, the issue is that engine=python is inside query.

filtered = df.query ("('`Drug Name` in @search') and ('BP.str.contains(@search, na=False)', engine='python')")

It should be

df.query("BP.str.contains(@search, na=False)", engine='python')

If you do searchterms = request.values.get('searchinput').replace(" ","").split(','), it converts your string to list of words which will cause Unhashable type list error because str.contains expects str as input.

What you can do is use regex to search for search terms in list, it will look something like this:

df.query("BP.str.contains('|'.join(@search), na=False, regex=True)", engine='python')

What this does is it searches for all the individual words using regex. ('|'.join(@search) will be "searchterm_1|search_term2|..." and "|" is used to represent or in regex, so it looks for searchterm_1 or searchterm_2 in BP column value)

To combine the outputs of both queries, you can run those separately and concatenate the results

pd.concat([df.query("`Drug Name` in @search", engine='python'),df.query("BP.str.contains('|'.join(@search), na=False, regex=True)", engine='python')])

Also any string based matching will require your strings to match perfectly, including case. so you can maybe lowercase everything in dataframe and query. Similarly for space separated words, this will remove spaces.

if you do searchterms = request.values.get('searchinput').replace(" ","").split(',') on Every 12 months, it will get converted to "Every12months". so you can maybe remove the .replace() part and just use searchterms = request.values.get('searchinput').split(',')

Without having sample input data, I used a random generated dataset as a showcase:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Drug_Name':['Drug1','Drug2','Drug3','Drug2','Drug5','Drug3']*4,
                  'Inv_Type': ['X', 'Y']*12,
                 'Quant': np.random.randint(2,20, size=24)})

# Search 1
search = "Drug3"
df.query('Drug_Name==@search')

# Search 2
search2 = "Y"
df.query ('Inv_Type.str.contains(@search2, na=False)', engine='python')

# Combined (use booleans, such as & or | instead of and or or
df.query ('Drug_Name==@search & Inv_Type.str.contains(@search2, na=False)')

Please note that engine='python' should be avoided as stated in the documentation:

Likewise, you can pass engine='python' to evaluate an expression using Python itself as a backend. This is not recommended as it is inefficient compared to using numexpr as the engine.

That said, if you are hell-bent on using it, you can do it like this:

mask = df["Inv_Type"].str.contains(search2, na=False)
df.query('Drug_Name==@search & @mask')

Alternatvely, you can achive the same without using .query() at all:

df[(df['Drug_Name']==search) & df['Inv_Type'].str.contains(search2, na=False)]