[SOLVED] Python function for creating a string based on length of row/list

Issue

I am using Python and SQL Alchemy to automate some categorizations of products. Essentially I am looking to take any csv with items as the rows and format it into search strings for a case statement of a SQL query.

I want to write a function in python that will measure the length of a row (or list) and insert text in between each of the words and output a string that will feed into SQL to find those words. The screenshot attached is how I’ve been doing it in excel. I’m sure this is fairly straightforward for someone with some concatenation skills.

Script Output Example

Solution

Seems to me that you can take the value from your first column and split that in to a list in order to get your desired result.

For example:

input_items = [
   "Bootcut Yoga Pants",
   "Go-Go Boots",
   "Flower Print Mini Skirts",
]

for input_item in input_items:
    # Split the item in to a list of individual words
    items = input_item.split()
    # Turn the list of words in to a list of query clauses
    query_items = [f"item_name ilike '%{item}%'" for item in items]
    # Turn that in to a string, with the word "and" between each item
    query_line = " and ".join(query_items)
    # Construct the final query
    print(f"when {query_line} then '{input_item}'")

Results in:

when item_name ilike '%Bootcut%' and item_name ilike '%Yoga%' and item_name ilike '%Pants%' then 'Bootcut Yoga Pants'     
when item_name ilike '%Go-Go%' and item_name ilike '%Boots%' then 'Go-Go Boots'
when item_name ilike '%Flower%' and item_name ilike '%Print%' and item_name ilike '%Mini%' and item_name ilike '%Skirts%' then 'Flower Print Mini Skirts'

Answered By – gvee

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.