SQLAlchemy에서 Raw SQL 실행하는 방법

Name

Sebastian Brandt

Published on 2023. 6. 5.

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

데이터 과학과 웹 개발의 분야에서는 데이터 베이스를 사용하는 것이 불가피합니다. 이러한 강력한 시스템들은 중요한 데이터를 보유하고 관리하며, 정보에 크게 의존하는 세상에서는 이를 사용하는 것이 매우 중요합니다. 이러한 분야에서 중요한 프로그래밍 언어중 하나인 Python언어는 데이터 베이스와의 통신을 위해 다양한 도구를 사용할 수 있는데, 그 중의 하나가 SQLAlchemy 입니다. SQLAlchemy의 ORM(Object-Relational Mapping) 레이어는 데이터 베이스 작업에 대한 고수준의 추상화를 원하는 개발자에게 큰 도움이 되지만 때로는 Raw SQL 쿼리를 실행하는 것이 효과적이거나 직관적일 수 있습니다. 이 글에서는 SQLAlchemy에서 Raw SQL 쿼리를 실행하는 방법에 대해 구체적인 코드 예제를 제공하여 명확하고 이해하기 쉽게 소개합니다.

SQLAlchemy 이해: 개요

SQLAlchemy에서 Raw SQL 실행의 개념을 이해하기 위해서는 우선 SQLAlchemy가 무엇인지 이해할 필요가 있습니다. SQLAlchemy는 Python 애플리케이션이 SQL 데이터베이스와 상호작용하는 데 필요한 도구 모음을 제공하기 위한 SQL 툴킷입니다. SQLAlchemy는 실업계 레벨의 영속성 패턴을 제공하며, 효율적이고 고성능의 데이터 베이스 접근을 위해 설계된 전체적인 도구 모음을 제공합니다.

SQLAlchemy는 고수준 및 저수준 데이터베이스 작업을 모두 수용하도록 설계되었습니다. 두 가지 주요 구성요소인 SQLAlchemy Core와 SQLAlchemy ORM은 이러한 작업을 처리합니다. SQLAlchemy Core는 SQL 추상화, 스키마 메타데이터, 연결 풀링, 유형 강제 변환 등이 중심이 되며, Python에서 데이터베이스와 상호작용할 수 있는 저수준 SQL 인터페이스를 제공합니다. 반면 SQLAlchemy ORM은 고수준 데이터 중심 API로서 도메인 모델 패턴을 제공하며, 데이터베이스와 테이블을 Python 클래스 및 객체로 캡슐화합니다.

Raw SQL의 장점과 가능성

SQLAlchemy와 그 구성 요소의 배경을 이해한 후 Raw SQL 쿼리에 집중해 봅시다. Raw SQL은 간단한 일반 텍스트 문자열로 쓰인 SQL 문과 쿼리를 가리킵니다. 데이터베이스 언어인 SQL (Structed Query Language)은 데이터베이스에 액세스하고 조작할 수 있도록 해줍니다. ORM 추상화가 제한적일 수있는 복잡한 작업을 실행해야할때, Raw SQL 쿼리가 종종 사용됩니다.

ORM 구문이 해당 SQL 작업보다 직관적이지 않거나, SQL 쿼리를 작성하는 것이 더 편한 개발자가 있다면, SQLAlchemy에서 Raw SQL은 SQL의 강력함과 Python의 편리함을 모두 얻을 수 있는 좋은 방법일 수 있습니다.

SQLAlchemy에서 Raw SQL 실행하는 방법

SQLAlchemy에서 Raw SQL은 'Engine' 및 'Session' 객체에서 제공하는 'execute()' 메소드를 사용하여 직접 실행할 수 있습니다.

Engine을 이용한 실행

아래 예제에서는 'engine.execute()' 메소드를 사용하여 Raw SQL을 실행하는 방법을 살펴보겠습니다.

from sqlalchemy import create_engine
 
engine = create_engine('sqlite:///:memory:')
result = engine.execute("SELECT * FROM users WHERE age >= :age", {'age': 21})
for row in result:
    print(row)

위의 코드에서 우리는 21세 이상의 모든 사용자를 가져오는 한 Raw SQL 쿼리를 실행합니다. 'create_engine()' 함수는 데이터베이스(이 경우 인메모리 SQLite 데이터베이스)와의 연결을 설정합니다. 이 엔진 인스턴스의 'execute()' 메서드는 SQL 쿼리를 문자열로 받아들이고 데이터베이스에 대해 실행합니다.

Session을 이용한 실행

대안으로, 'session.execute()' 를 사용하여 Raw SQL을 실행 할 수도 있습니다.

from sqlalchemy.orm import Session
 
session = Session(bind=engine)
result = session.execute("SELECT * FROM users WHERE age >= :age", {'age': 21})
for row in result:
    print(row)

이 예제는 이전 예제와 매우 유사해 보입니다. 그러나 미묘하지만 결정적인 차이가 있습니다. session.execute() 메서드는 쿼리가 트랜잭션에 의해 관리되도록 보장합니다. 데이터베이스 용어로, 트랜잭션은 단일 논리적 작업 단위로 수행되는 일련의 작업입니다. 따라서 세션을 통해 동일한 요청에서 여러 쿼리를 커밋하거나 롤백하여 일관성을 유지하고 시스템의 신뢰성을 향상시킵니다. 반면, engine.execute()를 사용하면 데이터 손상으로 이어질 수 있는 버그에 더 취약한 시스템이 될 수 있습니다.

트랜잭션 커밋의 기술

원시 SQL 쿼리를 실행할 때 트랜잭션 커밋 프로세스를 이해하는 것이 중요합니다. 특히 INSERT, UPDATE 또는 DELETE와 같은 데이터를 수정하는 작업을 수행하는 경우 이것은 매우 중요합니다. SQLAlchemy는 세션 관리를 통해 이러한 변경 사항이 언제 지속되는지를 제어할 수 있습니다.

다음은 예입니다.

from sqlalchemy import text
 
with session.begin():
    session.execute(
        text("UPDATE users SET age = :new_age WHERE age = :old_age"),
        {'new_age': 30, 'old_age': 20}
    )

위 코드에서 먼저 session.begin()을 사용하여 새 트랜잭션을 시작합니다. 이 트랜잭션에는 모든 사용자의 나이를 20에서 30으로 업데이트하는 원시 SQL 쿼리가 포함됩니다. 그런 다음 with 블록의 끝에서 트랜잭션이 자동으로 커밋됩니다. 쿼리 실행 중 예외가 발생하는 경우 트랜잭션이 롤백되어 데이터베이스의 상태가 일관성 있게 유지됩니다.

매개 변수화의 중요한 역할

원시 SQL 쿼리는 경우에 따라 SQL 주입 공격에 취약할 수 있습니다. 매개 변수화는 이러한 공격을 방지하는 기술입니다. SQLAlchemy의 text() 함수를 사용하면 원시 SQL 쿼리에 매개 변수를 제공할 수 있습니다.

다음은 이를 설명하는 예제입니다.

from sqlalchemy import text
 
query = text("SELECT * FROM users WHERE name = :name")
result = session.execute(query, {'name': 'Alice'})

위 코드 스니펫에서 쿼리 문자열 내부의 :name은 바인드 매개 변수입니다. execute() 함수가 호출될 때 이 바인드 매개 변수를 해당 값으로 매핑하는 딕셔너리를 전달합니다. 이 기능은 데이터가 올바르게 이스케이프되어 SQL 주입의 위험을 줄이도록 보장합니다.

내부 뷰 및 조인의 숙달

복잡한 쿼리는 종종 내부 뷰 및 조인 사용이 필요합니다. 내부 뷰는 FROM 절에서 하위 쿼리로, 쿼리 결과를 테이블처럼 사용할 수 있습니다. 조인은 관련 열을 기반으로 두 개 이상의 테이블에서 행을 결합합니다. 다음은 내부 뷰 및 조인이 포함된 원시 SQL 예제입니다.

query = text("""
SELECT users.name, counts.invoice_count
FROM users
JOIN (SELECT user_id, COUNT(*) as invoice_count FROM invoices GROUP BY user_id) AS counts
ON users.id = counts.user_id
WHERE counts.invoice_count > :count
""")
result = session.execute(query, {'count': 10})

이 쿼리는 10개 이상의 청구서를 가진 모든 사용자를 가져옵니다. 여기서 내부 뷰는 각 사용자에 대한 청구서 수를 계산하는 하위 쿼리의 결과인 counts 테이블입니다.

결론

SQLAlchemy에서 원시 SQL을 실행할 수 있는 능력은 복잡한 쿼리를 처리하면서 Python과 같은 고수준 언어의 편리성과 보안을 유지할 수 있도록 유연성을 제공합니다. 이 접근 방식은 SQL의 모든 기능과 Python의 사용성을 결합하여, 개발자들이 효율적이고 직관적이며 보안적인 데이터베이스 작업을 만드는 데 있다면 원하는 도움을 줍니다.

자주 묻는 질문

Q1: SQLAlchemy에서 원시 SQL 쿼리를 실행하는 방법은 무엇인가요?

session.execute() 또는 engine.execute() 메소드 중 하나를 사용하여 SQLAlchemy에서 원시 SQL 쿼리를 실행할 수 있습니다. 트랜잭션에서 쿼리가 올바르게 관리되도록 보장하는 session.execute() 메소드를 권장합니다.

Q2: SQLAlchemy에서 SELECT 쿼리를 수행하는 방법은 무엇인가요?

SELECT 쿼리를 수행하려면, execute() 메소드에 원시 SQL 쿼리 문자열을 전달하면 됩니다. 예를 들어, result = session.execute("SELECT * FROM users")은 users 테이블의 모든 행을 가져올 것입니다.

Q3: SQLAlchemy에서 원시 SQL 쿼리를 사용할 때 SQL 인젝션을 방지하는 방법은 무엇인가요?

SQLAlchemy에서 원시 SQL 쿼리를 사용할 때 SQL 인젝션을 방지하려면, 쿼리 매개변수를 바인딩 값을로 전달하여 매개변수화를 이용하고, 사용자 입력의 적절한 이스케이핑과 유효성 검사를 보장하세요.