SQLAlchemy. 조건식의 원하는 곳에 괄호 추가하기

Calendar라는 테이블에 Mapping되는 모델을 다음과 같이 정의했다고 하자.

from sqlalchemy import Column, INTEGER
from sqlalchemy.orm import declarative_base


Base = declarative_base()

class Calendar(Base):
	__tablename__ = "calendar"

	year = Column(INTEGER, primary_key=True)
	month = Column(INTEGER, primary_key=True)
	day = Column(INTEGER, primary_key=True)
	...

위 테이블에서 2021년 12월과 2022년 1월의 정보를 얻고자 하면 다음과 같이 SQL Query를 작성할 것이다.

SELECT ...
FROM calendar
WHERE (year == 2021 AND month == 12) OR (year == 2022 AND month = 1)

SQLAlchemy을 이용해 작성한 쿼리가 우리가 원하는 쿼리와 같은지 확인해보자.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_


_session = sessionmaker()
with _session.begin() as session:
	query = (
		session
		.query(Calendar)
		.filter(
			or_(
				and_(Calendar.year == 2021, Calendar.month == 12),
				and_(Calendar.year == 2022, Calendar.month == 1,
			)
		)
	)

	print(query)

"""
SELECT calendar.year AS calendar_year,
	   calendar.month AS calendar_month,
	   calendar.day AS calendar_day 
FROM calendar 
WHERE calendar.year = :year_1
AND calendar.month = :month_1
OR calendar.year = :year_2
AND calendar.month = :month_2
"""

SQL Query를 그대로 옮겼음에도 불구하고 SQLAlchemy로 만들어진 Query의 WHERE 구문에 괄호가 적용되지 않아 원하는 결과가 나오지 않는 것을 예상할 수 있다. 이러한 문제는 괄호로 묶어줘야 할 부분에  `.self_group()` 메소드를 추가하여 해결할 수 있다.

with _session.begin() as session:
	query = (
		session
		.query(Calendar)
		.filter(
			or_(
				and_(Calendar.year == 2021, Calendar.month == 12).self_group(),
				and_(Calendar.year == 2022, Calendar.month == 1.self_group(),
			)
		)
	)

	print(query)
 
"""
SELECT calendar.year AS calendar_year,
	   calendar.month AS calendar_month,
	   calendar.day AS calendar_day 
FROM calendar 
WHERE (calendar.year = :year_1 AND calendar.month = :month_1)
OR (calendar.year = :year_2 AND calendar.month = :month_2)
"""

잘못된 내용, 오타, 부정확한 문장 등 어떤 피드백이든 환영합니다. 감사합니다.

---