[SQLAlchemy 2.0, MySQL] Group by로 합계 구하기(2024년 최신)

요새 자체 제작해 사용 중인 교대근무 관리프로그램을 업데이트 하면서 오랜마네 자바가 아닌 파이썬을 사용했습니다. 이번 시간에는 이번달에 이 근무자가 몇시간 시간외를 했는지를 알기 위해서 Goup by와 sum을 SQLAlchemy로 사용하는 방법을 알아보겠습니다. 

 

처음에는 자바와 JPA로 할까 생각했었는지 제가 이번 코드를 작성하면서 느낀점은 처음 파이썬을 공부할 때 봤던 역시 인생은 짧기에 파이썬이 필요하다. 이 말이 왜 있는지 확실하게 느꼈습니다. 자바로 하게 되면 정말 많은 코드가 필요 했을텐데 파이썬을 이용하니 진짜 짧은 코드로도 원하는 코드를 완성할 수 있다는 걸 제대로 느꼈습니다. 

Life is short, you need Python.
인생은 짧기에, 당신에겐 파이썬이 필요하다.

---

목차

1. 기본 설정

2. 테이블 매핑

3. group by 구현 

프로젝트 구성

제가 만든 프로젝트 구성은 아래와 같습니다. MySQL과 연결하기 위한 config.py, 테이블과 매핑하기 위한 domain인 member_shift_tbl.py, 그리고 실제 group by 로 집계하기 위한 main.py로 구성하였습니다. 

 

프로젝트 폴더 구조

1.  기본 설정

ORM 을 사용하기 위해서는 기본 설정부터 해야합니다. 저는 MySQL을 DB로 사용하고 있기 때문에 SQLAlchemy로 MySQL과 연결하는 방법을 소개하도록 하겠습니다.  config.py 파일에 아래와 같이 엔진을 만듭니다. SQLAlchemy의 시작은 이 엔진을 만드는 것으로 시작합니다. 이 엔진은 추후 만들 세션에 입력하는데 사용 됩니다. 

from sqlalchemy import create_engine

url = 'mysql+pymysql://관리자아이디:비밀번호@DB서버주소:포트/DB이름'

engine = create_engine(url, echo=True)

 

2.  테이블 매핑

역시 ORM을 사용하기 위해서는 테이블 매핑 작업이 필요합니다. 저는 이미 MySQL과 스프링으로 교대근무 관리 서비스를 만들어서 사용하고 있었기 때문에 아래 테이블을 생성하는 코드는 따로 실행하지 않았습니다. 아래 전체 코드는 SQLAlchemy2.0 튜토리얼을 보고 작성하였습니다. 아마도 기존에 사용하시면 SQLAlchemy와 다르게 작성된걸 보실 수 있으실것 입니다. 

 

이렇게 SQLAlchemy을 사용하기 위해 밑작업이 마무리 되었습니다. 

2.1 테이블을 생성하는 코드

새로 테이블을 생성하실때만 아래 코드가 필요합니다. 

Base.metadata.create_all(engine)

 

2.2 테이블을 매핑하는 클래스의 전체코드

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column

from datetime import date


class Base(DeclarativeBase):
    pass


class MemberShiftTable(Base):
    __tablename__ = 'member_shift_tbl'
    id: Mapped[int] = mapped_column(primary_key=True)
    mdate: Mapped[date]
    person: Mapped[str]
    status: Mapped[str]
    squad: Mapped[int]
    holiday_add_time: Mapped[float]
    holiday_time: Mapped[float]
    meal_money: Mapped[int]
    night_time: Mapped[float]
    over_time: Mapped[float]
    pattern: Mapped[str]
    pattern_number: Mapped[int]
    real_work_time: Mapped[float]

3.  group by 구현 

아래 코드를 보시면 stmt로 쿼리문을 만들고 sesion.excuce(stmt)로 쿼리문을 실행하는 걸 보실수 있으실겁니다. 

각 코드 중 주요 내용을 하나씩 살펴보면 다음과 같습니다. 

• func.sum: 이건 SQL의 sum입니다. 
• label: 이건 SQL의 AS와 같습니다. 
• extract: 날짜 중 연도와 월을 추출하기 위한 함수입니다. 
• group_by: SQL의 group by 와 같습니다. 

그리고 각 컬럼의 접근의 meberShift.person과 같이 . 으로 접근할 수 있습니다. 마지막으로 scalars를 쓸데와 안쓸데의 차이점은 맨 아래 링크의 포스팅에 제가 예전에 실험해본 결과가 있으니 궁금하신 분들은 한번 들어가 보시기 바랍니다. 

 

3.1 scalars 유무의 차이점 요약

• session.execute(): 쿼리의 결과를 Result 객체로 반환하며, 여러 열의 결과를 처리할 때 유용합니다.
• session.scalars(): 단일 열의 결과를 처리하는 데 유용하며, 결과를 리스트 등으로 쉽게 변환할 수 있습니다.

4. 전체 코드

from sqlalchemy.orm import Session
from sqlalchemy import select, and_, func, extract
from config.config import engine

from domain.member_shift_tbl import MemberShiftTable

session = Session(engine)

year = 2024
month = 6

stmt = select(MemberShiftTable.person,
              func.sum(MemberShiftTable.meal_money).label("meal_money"),
              func.sum(MemberShiftTable.holiday_time).label("holiday_time"),
              func.sum(MemberShiftTable.holiday_add_time).label("holiday_add_time"),
              func.sum(MemberShiftTable.night_time).label("night_time"),
              func.sum(MemberShiftTable.over_time).label("over_time"),
              func.sum(MemberShiftTable.real_work_time).label("real_work_time"),
              ).where(
    and_(extract('year', MemberShiftTable.mdate) == year,
         extract('month', MemberShiftTable.mdate) == month,
         )).group_by(MemberShiftTable.person)

memberShifts = session.execute(stmt).all()

for memberShift in memberShifts:
    print(f"{memberShift.person}: {memberShift.meal_money}, {memberShift.over_time} "
          f"{memberShift.holiday_time}, {memberShift.holiday_add_time}, "
          f"{memberShift.real_work_time}, {memberShift.night_time}")

 

---

관련 링크

https://jh-industry.tistory.com/76

FASTAPI 사용을 위한 SQLAlchemy(ORM) 기초 - 3탄 데이터 가져오기(Read, SELECT문)