[tiangolo/SqlModel] where 절 사용하기

FastAPI 의 tiangolo 에서 사용하고 있는 SQLModel
Where 사용법 간단히 알아보기

 

 

기본 사용

SELECT id, name, secret_name, age 
FROM hero 
WHERE age >= 35

이 쿼리는 다음과 같이 사용합니다.

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)

 

멀티 WHERE

아래와 같은 쿼리를 보내고 싶을 때

SELECT id, name, secret_name, age 
FROM hero 
WHERE age >= 35 AND age < 40

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age >= 35).where(Hero.age < 40)
        results = session.exec(statement)
        for hero in results:
            print(hero)
            
            
# 또는 콤마로 구분

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age >= 35, Hero.age < 40)
        results = session.exec(statement)
        for hero in results:
            print(hero)

 

OR 을 사용하려면?

# or_ 를 imoprt 함
from sqlmodel import Field, Session, SQLModel, create_engine, or_, select

def select_heroes():
    with Session(engine) as session:
        # or_() 로 묶어줌
        statement = select(Hero).where(or_(Hero.age <= 35, Hero.age > 90))
        results = session.exec(statement)
        for hero in results:
            print(hero)

 

 

age 컬럼이 Nullable 이라면?

오류가 발생하지 않도록 col() 을 사용해야함.

# col import 함
from sqlmodel import Field, Session, SQLModel, col, create_engine, select

def select_heroes():
    with Session(engine) as session:
        # col() 사용함
        statement = select(Hero).where(col(Hero.age) >= 35)
        results = session.exec(statement)
        for hero in results:
            print(hero)