[SQLAlchemy] 2. Connection, Commit, Query, Bound parameters

Connection

• 앞서 생성한 엔진 객체의 유일한 목적은 데이버테이스와의 연결을 제공하는 Connection 객체를 생성하는 것
• 데이터베이스와의 모든 상호작용은 Connection 객체를 통해서 이루어짐
• Connection 객체는 데이터베이스에 대한 열린 리소스를 나타내기에, 객체의 사용범위를 특정 context로 제한해야 함
  • 즉 Connection 객체를 통한 데이터베이스 처리가 끝났으면 Connection을 닫아서 잘 종료해야한다는 것
  • with 문과 사용하는 것이 좋음

# Hello world 예제
# text() 함수는 SQLAlchemy에서 원래 쓰던 SQL 구문을 그대로 사용할 수 있도록 해주는 함수
# ORM은 추후에..
from sqlalchemy import create_engine, text

engine = create_engine("sqlite+pysqlite:///:memory:")

with engine.connect() as conn:
    result = conn.execute(text("select 'hello world'"))
    print(result.all()) # 결과를 list 형태로 반환

 

Commit

• DBAPI 연결은 자동 커밋이 되지 않기 때문에 데이터를 DB에 반영하기 위해서는 명시적으로 Commit 명령을 실행해야 함
• 크게 두 가지 commit 방법이 있음
  • Commit as you go
    • 트랜잭션이 진행 중일 때 명시적으로 Connection.commit() 메서드를 호출하여 커밋
    • 작업 중간에 여러 번 커밋할 수 있어서 유연하게 사용 가능
  • Begin once:
    • Engine.begin() 메서드를 사용하여 트랜잭션 블록을 시작하면 블록이 성공적으로 완료되면 자동으로 커밋
    • 예외가 발생하면 rollback
    • 코드가 더 간결해지며, 전체 블록의 의도를 명확히 표현 가능
  • python에서 파일을 열 때 with 구문을 사용하지 않았을 때는 명시적으로 파일을 닫아야하지만, with 구문을 사용했을 때는 해당 블록이 끝나면 알아서 닫아주는 것과 비슷한 개념
• Commit as you go

from sqlalchemy import create_engine, text

# SQLite 메모리 데이터베이스에 연결하는 엔진 생성
# echo는 SQLAlchemy가 생성하고 실행하는 모든 SQL 문을 출력하도록 설정하는 디버깅 기능
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

# with 문을 사용하여 연결을 관리
with engine.connect() as conn:
    # 테이블 생성
    conn.execute(text("CREATE TABLE some_table (x int, y int)"))
    
    # 데이터 삽입
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
    )
    
    # 트랜잭션 커밋
    conn.commit()

• Begin once

from sqlalchemy import create_engine, text

# SQLite 메모리 데이터베이스에 연결하는 엔진 생성
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

# with 문을 사용하여 트랜잭션 블록을 관리
with engine.begin() as conn:
    # 데이터 삽입
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
    )

 

Query

• 위에서 봤듯이 Connection.execute() method를 사용해서 SQL문을 실행할 수 있음
• Result 객체를 사용하여 결과 행을 가져오고 다양하게 처리 가능

result = conn.execute(text("select x, y from some_table"))

# 튜플 할당
for x, y in result:
    print(x, y)
    
# 정수 인덱스
for row in result:
    x = row[0]
    y = row[1]
    print(x, y)
    
# 속성 이름
for row in result:
    x = row.x
    y = row.y
    print(f"x: {x}, y: {y}")
    
# Mapping
for dict_row in result.mappings():
    x = dict_row["x"]
    y = dict_row["y"]
    print(f"x: {x}, y: {y}")

 

Bound Parameters(바인드 파라미터)

• SQL문를 데이터와 함께 전달해야 할 때 사용
• SQL문을 고정된 상태로 유지하면서 데이터를 안전하게 전달 가능
  • SQL Injection attacks 방지
  • 데이터베이스 드라이버가 입력값을 올바르게 처리 가능

from sqlalchemy import create_engine, text

# SQLite 메모리 데이터베이스에 연결하는 엔진 생성
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

# with 문을 사용하여 연결을 관리
with engine.connect() as conn:
    # WHERE 절에 바인드 파라미터를 사용하여 데이터 조회
    result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
    
    # 결과 행 반복
    for row in result:
        print(f"x: {row.x}  y: {row.y}")

• {"y": 2}: 바인드 파라미터, y에 실제 값을 제공하는 딕셔너리
• INSERT, UPDATE, DELETE 같은 DML문은 여러 파라미터 Set를 한 번에 보낼 필요가 있음.
  • 이때는 단일 dictionary가 아닌 dictionary의 list를 전달
  • executemany 스타일이라고 함(여러 파라미터 세트를 사용하여 SQL 문을 최적화된 방식으로 여러 번 실행)

from sqlalchemy import create_engine, text

# SQLite 메모리 데이터베이스에 연결하는 엔진 생성
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)

# with 문을 사용하여 연결을 관리
with engine.connect() as conn:
    # 여러 파라미터 세트를 사용하여 데이터 삽입
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
    )
    # 트랜잭션 커밋
    conn.commit()

 

• [{"x": 11, "y": 12}, {"x": 13, "y": 14}]: 여러 파라미터 세트를 포함한 리스트
• SQLAlchemy는 각 파라미터 세트를 사용하여 INSERT 문을 여러 번 실행