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)

That's a bit much to go through, but you can interpret nullable strings as empty string when NULL using COALESCE(<expr>, ''). — Ilja Everilä
– Ilja Everilä, Commented May 3, 2018 at 20:25
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
– anderswb, Commented May 3, 2018 at 20:36

Ed Manet · Accepted Answer · 2019-09-17 11:43:37Z

5

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, '') == '')

answered Sep 17, 2019 at 11:43

Ed Manet

3,1783 gold badges23 silver badges23 bronze badges

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

Comments

Ilja Everilä · Accepted Answer · 2018-05-04 06:39:20Z

4

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'])

answered May 4, 2018 at 6:39

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Collectives™ on Stack Overflow

Query for empty string or Null using SQLAlchemy

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related