vendredi 1 juillet 2016

SQLAlchemy substring filtering

I have stored types of Bags in my PostgreSQL database:["Clutch Bags", "Shoulder Bags", "Tote Bags"]. I am writing a python flask based API endpoint using which a user can provide a search term like "Red Shoulder Bag" or "Grey Clutch Bag". Now I want to write a query using SQLAlchemy so that for these given search terms I am able to look for that value in my Bags table. I wrote this, but it only works if user entered search string is plural, this doesnt work if user enters 'Grey Clutch Bag'

    categories = db.session.query(Bags.id) 
    .filter(literal(search_string).ilike(func.concat('%', Bags.type, '%')))
    .all()

how can I strip the trailing 's' from bag types in database at the time of filtering? I want to be able to query such that:

type_in_db = "Clutch Bags" 
search_string = "Grey Clutch Bag"

if type_in_db.rstrip('s') in search_string:
    return type_in_db

Aucun commentaire:

Enregistrer un commentaire