MariaDB 강좌 / Python에서 MariaDB 연결하고 매개변수 쿼리 사용하기

Python에서 MariaDB를 사용할 때는 드라이버로 연결한 뒤 SQL 문과 사용자 값을 별도 인수로 전달해야 합니다. 매개변수 쿼리는 입력을 SQL 구문으로 이어 붙이지 않게 하며, 함께 최소 권한 계정과 명시적인 COMMIT·ROLLBACK을 사용해야 데이터 변경을 통제할 수 있습니다.

실습 환경과 데이터베이스 계정 준비하기

이 예제는 Python 3, PyMySQL 1.1.2, 로컬 TCP로 연결하는 MariaDB를 사용합니다. 핵심 동작은 Python 3.12·MariaDB 12.3.3에서 확인했습니다. PyMySQL은 Python에서 MariaDB와 통신할 수 있는 드라이버이며, 여기서 사용하는 값 자리표시는 %s입니다. 다른 드라이버의 물음표 구문과 혼용하지 마세요.

관리자 연결에서 아래 SQL을 실행합니다. 공개 예제의 Replace_With_Your_Own_Lab_Password는 실제로 사용할 암호가 아니므로 자신만의 실습용 암호로 바꾸세요. 사용자 생성은 첫 실행용이며 같은 계정이 이미 있다면 계정과 권한을 확인하고 중복 생성하지 않습니다. 생성과 실행의 책임은 사용자 권한 관리에서 구분할 수 있습니다.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

계정의 호스트 부분 127.0.0.1은 로컬 네이티브 서버의 TCP 접속을 전제로 합니다. 컨테이너나 원격 서버에서는 서버가 보는 클라이언트 출발지가 달라질 수 있으므로 접속 계정의 호스트와 연결 경로를 확인해야 합니다. 단순히 모든 호스트에 관리자 권한을 허용하는 방식으로 해결하지 않습니다.

프로젝트 폴더와 실행 파일 구성하기

새 폴더를 만든 뒤 다음 명령을 실행합니다. 가상 환경을 활성화하는 대신 그 환경의 Python을 직접 지정하므로 PowerShell 실행 정책을 바꿀 필요가 없습니다.

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

위 명령은 Windows PowerShell 기준입니다. Linux·macOS에서는 같은 단계에서 .venv/bin/python을 사용합니다. 아래 전체 코드를 UTF-8 파일 notes_app.py로 저장하세요. 암호는 실행 때 입력받으며 코드나 SQL 문자열에 넣지 않습니다.

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

연결 제한 시간과 읽기·쓰기 제한 시간을 지정하고 문자 집합을 utf8mb4로 맞췄습니다. 이 글은 PC의 로컬 실습 연결을 다룹니다. 다른 네트워크로 연결할 때는 서버 인증서를 검증하는 TLS 설정도 별도로 구성해야 하며 암호만 있다고 전송 경로가 보호되는 것은 아닙니다.

정상 이름과 따옴표가 있는 이름으로 실행하기

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

두 명령은 서로 다른 실행입니다. 매번 데이터베이스 암호를 입력하며 첫 실행은 Notebook, 두 번째 실행은 작은따옴표가 포함된 O'Reilly를 삽입하고 같은 이름의 행을 조회합니다. 새 테이블에서 한 번씩 실행하면 각각 그 이름의 한 행이 출력됩니다. 자동 증가 ID는 기존 데이터나 롤백 이력에 따라 달라질 수 있으므로 숫자 자체를 고정된 정답으로 삼지 않습니다.

같은 이름으로 다시 실행하면 새 행을 하나 더 저장하고 그 이름에 맞는 모든 행을 반환합니다. UNIQUE 제약으로 이름의 유일성을 강제한 예제가 아니기 때문입니다. 업무에서 중복을 막아야 한다면 중복의 기준과 데이터베이스 제약을 함께 설계해야 합니다.

값 바인딩과 문자열 조합의 차이 이해하기

핵심은 cur.execute(sql, (name,))처럼 SQL과 값의 튜플을 분리하는 것입니다. 원소가 하나인 튜플은 쉼표가 필요합니다. SQL 안의 %s에 따옴표를 직접 붙이거나 Python의 % 연산, f-string, 문자열 덧셈으로 사용자 값을 끼워 넣지 않습니다. PyMySQL 공식 예제도 SQL과 인수를 분리하여 전달합니다.

예를 들어 x' OR 1=1 -- 라는 이름을 함수에 전달해도 그 전체를 이름 값으로 저장하고 같은 문자열만 비교해야 합니다. 여기서 말하는 매개변수 쿼리는 드라이버 API 사용 방식입니다. 모든 드라이버가 반드시 서버의 prepared statement 프로토콜을 사용한다는 뜻은 아니며, 이 예제의 안전성을 설명하기 위해 그렇게 표현하지 않습니다.

열 이름과 ASC·DESC 같은 구문 요소는 일반 값 매개변수로 대체하지 않습니다. 예제의 ORDER BY는 사용자가 보낸 문자열을 직접 넣는 대신 개발자가 정의한 columns 사전에서만 선택합니다. 명령줄의 choices 검사에 더해 함수 안에서도 검증하여 다른 호출 경로에서 함수가 사용되어도 허용 목록을 벗어나지 않게 했습니다. Cursor.execute의 인수 규칙을 참고하세요.

확정·취소·권한을 함께 확인하기

autocommit=False이므로 함수가 성공했다고 자동 저장되는 것은 아닙니다. main은 삽입과 조회가 성공한 뒤 commit하고, 도중에 예외가 나면 rollback합니다. finally에서 연결을 닫아 성공·실패 어느 경로에서도 자원이 남지 않게 했습니다. 다른 연결에서 저장 여부를 확인할 때는 확정 시점과 트랜잭션의 경계를 구분하세요.

실습 계정에는 SELECT와 INSERT만 주었습니다. 프로그램이 테이블을 만들거나 삭제하지 않으므로 CREATE·DROP을 줄 필요가 없습니다. 계정 분리는 SQL 인젝션 예방을 대체하지 않지만, 입력 처리나 프로그램에 문제가 생겼을 때 가능한 작업 범위를 제한합니다. PyMySQL 연결 옵션에서 autocommit과 시간 제한을 확인할 수 있습니다.

자주 발생하는 오류와 실습 정리

연결 대상이 기본값과 다르면 PowerShell에서 $env:DB_PORT = "3307"처럼 비밀이 아닌 연결 설정을 지정할 수 있습니다. 암호는 계속 입력창을 사용합니다. 사용을 마친 환경 변수는 Remove-Item Env:DB_PORT로 제거할 수 있습니다.

정리가 필요할 때는 프로그램을 종료하고 관리자가 이 실습 전용 계정과 테이블임을 확인한 뒤 아래 문장을 실행합니다. 업무 계정을 같은 이름으로 바꿔 삭제하지 마세요.

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;