Функция func.coalesce

Функция func.coalesce возвращает первое не NULL значение из списка переданных аргументов. Первым параметром передаётся колонка или выражение, последующими параметрами - альтернативные значения, которые будут использованы, если предыдущие равны NULL. Функция полезна для подстановки значений по умолчанию.

Синтаксис

func.coalesce(column, value1, value2, ...)

Пример

Давайте создадим таблицу articles и подставим значение по умолчанию для колонки status:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, func

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
)

metadata.create_all(engine)

with engine.connect() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'status': None},
		{'id': 2, 'title': 'article 2', 'status': 'published'},
	])
	
	query = select(
		articles.c.id,
		func.coalesce(articles.c.status, 'draft').label('status')
	)
	
	res = conn.execute(query).fetchall()
	print(res)

Результат выполнения кода:

[(1, 'draft'), (2, 'published')]

Пример

Давайте перечислим несколько альтернативных значений, чтобы показать порядок выбора:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, func

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
	Column('num', Integer),
)

metadata.create_all(engine)

with engine.connect() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'status': None, 'num': None},
		{'id': 2, 'title': 'article 2', 'status': None, 'num': 5},
		{'id': 3, 'title': 'article 3', 'status': 'published', 'num': 10},
	])
	
	query = select(
		articles.c.id,
		func.coalesce(articles.c.status, 'draft').label('status'),
		func.coalesce(articles.c.num, 0).label('num')
	)
	
	res = conn.execute(query).fetchall()
	print(res)

Результат выполнения кода:

[(1, 'draft', 0), (2, 'draft', 5), (3, 'published', 10)]

Пример

Давайте используем func.coalesce в условии where для фильтрации записей:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, func

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
)

metadata.create_all(engine)

with engine.connect() as conn:
	conn.execute(articles.insert(), [
		{'id': 1, 'title': 'article 1', 'status': None},
		{'id': 2, 'title': 'article 2', 'status': 'published'},
		{'id': 3, 'title': 'article 3', 'status': None},
	])
	
	query = select(articles.c.id, articles.c.title).where(
		func.coalesce(articles.c.status, 'draft') == 'draft'
	)
	
	res = conn.execute(query).fetchall()
	print(res)

Результат выполнения кода:

[(1, 'article 1'), (3, 'article 3')]

Пример

Давайте посмотрим итоговый SQL-запрос, который генерирует func.coalesce:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, func

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

articles = Table(
	'articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('title', String),
	Column('status', String),
)

query = select(
	articles.c.id,
	func.coalesce(articles.c.status, 'draft').label('status')
)

print(query)

Итоговый SQL запрос:

SELECT articles.id, coalesce(articles.status, :coalesce_1) AS status
FROM articles

Смотрите также

функцию func.nullif,
которая возвращает NULL, если два значения равны
функцию func,
которая создает вызов произвольной SQL-функции
функцию case,
которая реализует конструкцию CASE WHEN
функцию cast,
которая приводит значение к указанному типу