SQL COALESCE() method in python

Question

How could I recreate the COALESCE() method from SQL, but in python?

I think [this](https://stackoverflow.com/questions/4978738/is-there-a-python-equivalent-of-the-c-sharp-null-coalescing-operator) may answer your question. — Yosua, Apr 10 '20 at 15:37

score 5 · Answer 1 · answered Apr 10 '20 at 15:46

5

You can use or, in which your expression will be equal to the first logical True:

var = 1
res = var or 2 # res=1

var = None
res = var or 1 # res=1

answered Apr 10 '20 at 15:46

Gabio

This will not manage correctly other "logical False" values (like 0 or "") – Don Apr 11 '20 at 03:35

pilcrow · Answer 2 · 2020-04-11T03:28:46.083

COALESCE is a variadic function, so you need a little more than just Python's or:

def coalesce(iterable):
    for el in iterable:
        if el is not None:
            return el
    return None

This assumes that Python's None is the equivalent of SQL's NULL.

More compactly, you could adapt the first_true() "recipe" from the itertools documentation:

def coalesce(iterable):
    return next((el for el in iterable if el is not None), None)

score 0 · Answer 3 · answered Mar 31 '23 at 08:37

if you do not want to limit yourself to an iterable itself, but a flexible list of args, you can use this:


def coalesce(*args):
  for el in args:
    if el is not None:
      return el
  return None

or use this one which is even more flexible


def coalesce(*args, preds=[False, None]):
  for el in args:
    if el not in preds:
      return el
  return None

3 Answers3