SQLAlchemy substring filtering

Question

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

As to the error, func.lower(Bags.type) in search_string is equal to search_string.__contains__(func.lower(Bags.type)) and so you get the error, since str.__contains__ expects another string. — Ilja Everilä
– Ilja Everilä, Commented Jun 21, 2016 at 13:14
you says I have stored types of Bags in my PostgreSQL database: but tag say mysql? — middlestump
– middlestump, Commented Jun 30, 2016 at 11:22
If your query contains a word that the database doesn't contain, you will not get a match. This may seem obvious, but your question seems to suggest that you expect that searching for "Grey Clutch Bag" will yield "Clutch Bags" from the database. I recommend editing your question to clarify what you want. — Julian Gonggrijp
– Julian Gonggrijp, Commented Jun 30, 2016 at 20:21

Alex · Accepted Answer · 2016-06-28 19:52:54Z

3

+25

I am not very expirienced python developer but why not simply:

 categories = db.session.query(Bags.id) \
.filter(Bags.type.like('%'+search_string+'%'))\
.all()

answered Jun 28, 2016 at 19:52

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julian Gonggrijp Over a year ago

This is the answer. To make it more concrete, .filter(Bags.type.like('%Clutch Bag%')) will match "Grey Clutch Bags".

Rick James · Accepted Answer · 2016-06-27 18:53:13Z

There is no way for "Clutch Bags" and "Grey Clutch Bag" to match with a simple LIKE or "contains". You must rethink your design.

Here are some thoughts in that direction.

In MySQL there is a FULLTEXT type of index that works with "words". It understands English singular/plural so that "Bags" and "Bag" will match each other. However, there are several limitations of FULLTEXT. Your one example might not hit any of them, but other situations might.

You could "cleanse" the input -- both in building the table and building the search string. This includes removing punctuation, pluralization, capitalization, synonyms, etc.

In the long run, you may need to avoid third-party software (such as SQLAlchemy); they tend to force you to learn both the database and their reformulation of it, plus they may prevent getting at some details that you will need.

Collectives™ on Stack Overflow

SQLAlchemy substring filtering

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related