Query for empty string or Null using SQLAlchemy

Question

I'm pretty new to database design and web design, so please bear with me.

I have a Flask app where I need to search in a database depending on arguments passed in the url, using flask-sqlalschemy, such as:

.../index?part_number=123*321$description1=&description2=TestText

The asterisk will be interpreted as one or more char and ** as a single char. So I'll replace them with % and _, and terminate any special chars.

My question is: In the database some fields will be empty strings and some will be Null. But I need to have them both be interpreted as an empty string, such that they will be returned when the arg is empty or *.

I know I can do something along the lines of .like(part_number == None | part_number == '%'). But I only want to do this in the case where the search string is *, but how do I do that in a clever way when I have 10 different args, all and'ed together?

Here's a snippet and boiled down version of the relevant code. I'm not quite sure how to make a small program that can run in itself for testing.

filter_args = ['part_number', 'description1', 'description2'] # actual code has ~10 args
filters = dict()
for arg in filter_args:
 filter_str = request.args.get(arg, type=str, default='*') # get filter arg from url
 filter_str = '*' if filter_str == '' else filter_str # if the filter string is empty, search for all
 # Replace * by % and ** by _ and terminate special chars
 filter_str = filter_str.replace('\\', '\\\\')
 filter_str = filter_str.replace('%', '\%')
 filter_str = filter_str.replace('_', '\_')
 filter_str = filter_str.replace('**', '_')
 filter_str = filter_str.replace('*', '%')
 filters[filter_name] = filter_str
parts = Part.query.filter(
 Part.part_number.ilike(filters['part_number']), # and
 Part.description1.ilike(filters['description1']), # and
 Part.description2.ilike(filters['description2'])
).order_by(Part.part_number)

• Ilja Everilä: That's a bit much to go through, but you can interpret nullable strings as empty string when NULL using COALESCE(<expr>, '').
• anderswb: Yea, I wasn't sure how much information was needed. How would you embed this into the query? I can't seem to find much information on a quick google session.
• anderswb: That did the trick! Thank you very much for your help.

Answer 1

I was searching for a way to filter for a null or empty string when I came across this question. While Ilja's answer is correct for the specifics of OP's question I thought I'd add that the coalesce function work for my situation too. If OP was looking for rows where part_number was None or empty:

Pat.query.filter(func.coalesce(Part.part_number, '') == '')

Answer 2

The COALESCE function returns the value of the first argument expression that does not evaluate to NULL (or NULL, if all values evaluated to NULL). So if you'd like to treat NULL part numbers as empty strings, you could do

func.coalesce(Part.part_number, '').ilike(filters['part_number'])