홈 / 학습 코스 / SQL 데이터베이스 입문

7부 · 코드와 실전으로 넓히기 (1) 파이썬과 SQL

← 6부 인덱스와 뷰 · 목차 · 다음: 다음 단계 →

지금까지는 SQL을 직접 쳐서 실행했습니다. 실제 프로그램에선 코드가 SQL을 보내고 결과를 받아 화면에 그리거나 다음 처리를 합니다. 이 챕터에서 파이썬으로 우리 서점을 움직입니다. 그리고 초보가 반드시 알아야 할 보안 함정(SQL 인젝션)과, 데이터 입문의 단짝 pandas와의 연결을 다룹니다.

flowchart LR
    code["파이썬 코드"] -->|SQL 문자열 전송| db[("SQLite DB")]
    db -->|결과(행) 반환| code
    code --> use["화면 표시 · 분석 · 다음 처리"]

    classDef c fill:#d5e8f2,stroke:#3498db,color:#000
    classDef d fill:#e8d5f2,stroke:#9b59b6,color:#000
    class code,use c
    class db d
이 챕터는 파이썬 기초(변수·함수·반복문)를 약간 가정합니다. 1~6부는 파이썬 없이도 됐지만, 여기서부턴 코드가 중심입니다.

7.1 기본 패턴: 연결 → 실행 → 결과

1부에서 살짝 봤던 패턴을 제대로 익힙니다. 파이썬 표준 라이브러리 sqlite3만 쓰며, 추가 설치가 필요 없습니다.

PYTHON
import sqlite3

# 1) 연결 (파일이 없으면 새로 만든다)
conn = sqlite3.connect("bookstore.db")
conn.execute("PRAGMA foreign_keys = ON")   # 외래키 검사 켜기 (매 연결마다!)

# 2) 커서로 SQL 실행
cur = conn.cursor()
cur.execute("SELECT title, price FROM books WHERE price > 10000 ORDER BY price DESC")

# 3) 결과 받기
for row in cur.fetchall():
    print(row)        # 각 행은 튜플: ('고서 필사본', 50000)

# 4) 연결 닫기
conn.close()

핵심 객체 둘:

  • 연결(connection). 데이터베이스 파일과의 통로. sqlite3.connect()로 연다.
  • 커서(cursor). SQL을 보내고 결과를 받아 오는 손잡이. cur.execute(SQL)로 실행, cur.fetchall()로 모든 행을, cur.fetchone()으로 한 행을 받는다.
외래키는 매 연결마다 켠다. 3부에서 강조했듯 SQLite는 외래키 검사가 기본 꺼짐입니다. 파이썬에서 연결할 때마다 PRAGMA foreign_keys = ON을 실행하세요. 빠뜨리면 잘못된 참조가 조용히 들어갑니다.

결과를 칼럼 이름으로 받기

기본은 행이 튜플(row[0], row[1])이라 읽기 불편합니다. row_factory를 켜면 칼럼 이름으로 접근할 수 있습니다.

PYTHON
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT title, price FROM books WHERE price > ? ORDER BY price DESC LIMIT 2", (10000,))
for row in cur.fetchall():
    print(f"{row['title']}: {row['price']}원")

출력:

CODE
고서 필사본: 50000원
모비딕: 15000원

7.2 ⚠️ SQL 인젝션: 절대 문자열을 이어 붙이지 마라

여기는 건너뛰면 안 되는 보안 주제입니다. 사용자 입력으로 검색하는 기능을 만든다고 합시다. 가장 자연스러워 보이는, 그러나 치명적으로 위험한 방법:

PYTHON
# ❌❌❌ 절대 이렇게 하지 마세요
keyword = input("검색어: ")
cur.execute("SELECT * FROM books WHERE title = '" + keyword + "'")

사용자가 평범한 검색어를 넣으면 동작합니다. 하지만 악의적 사용자가 검색어 칸에 이런 걸 넣으면:

CODE
'; DROP TABLE books; --

문자열을 이어 붙인 SQL은 이렇게 변합니다.

SQL
SELECT * FROM books WHERE title = ''; DROP TABLE books; --'

검색 쿼리 뒤에 "books 테이블을 통째로 삭제하라" 는 명령이 끼어들어 실행됩니다. 이것이 SQL 인젝션(SQL injection) 입니다 — 입력값이 데이터가 아니라 명령으로 해석되는 공격입니다. 데이터 유출·삭제·변조로 이어지는, 웹 보안에서 가장 오래되고 흔한 취약점입니다.

flowchart TB
    inj["사용자 입력: '; DROP TABLE books; --"]
    inj --> concat["문자열 이어붙이기"]
    concat --> danger["입력이 SQL 명령으로 해석됨<br/>→ 테이블 삭제 😱"]

    safe["파라미터 바인딩 (?)"] --> ok["입력은 항상 '값'으로만 취급<br/>→ 명령으로 해석 불가 ✅"]

    classDef bad fill:#f5c6cb,stroke:#c0392b,color:#000
    classDef good fill:#d5f2e0,stroke:#27ae60,color:#000
    class inj,concat,danger bad
    class safe,ok good

해결책: 파라미터 바인딩 (플레이스홀더 ?)

값이 들어갈 자리에 ?를 두고, 실제 값은 별도로 넘깁니다. 그러면 데이터베이스가 그 값을 항상 '데이터'로만 다루고, 절대 명령으로 해석하지 않습니다.

PYTHON
# ✅ 안전: 값은 ? 자리에 튜플로 따로 전달
keyword = input("검색어: ")
cur.execute("SELECT * FROM books WHERE title = ?", (keyword,))

이제 keyword에 '; DROP TABLE books; --를 넣어도, 그건 그냥 "그런 제목의 책을 찾아라"가 되어 (그런 책이 없으니) 빈 결과가 나올 뿐, 테이블은 안전합니다.

여러 값도 마찬가지로 ?를 순서대로 두고 튜플로 넘깁니다.

PYTHON
cur.execute(
    "INSERT INTO books (title, author, price) VALUES (?, ?, ?)",
    ("총, 균, 쇠", "재레드 다이아몬드", 18000)
)
철칙. 사용자 입력이든 변수든, SQL에 값을 넣을 땐 항상 ? 바인딩을 쓴다. 문자열을 +나 f-string으로 이어 붙여 SQL을 만드는 건 절대 금지입니다. 이 습관 하나가 가장 흔한 보안 사고를 통째로 막아 줍니다. (참고: ?는 SQLite 방식이고, PostgreSQL은 %s나 $1 등 라이브러리마다 표기가 조금 다르지만 원리는 같습니다.)

7.3 트랜잭션을 코드로: 주문 처리 함수

6부의 트랜잭션을 실제 함수로 만들어 봅니다. "재고를 확인하고, 충분하면 주문을 기록하고 재고를 줄이되, 중간에 문제가 생기면 전부 되돌리는" 현실적인 로직입니다.

PYTHON
def place_order(conn, customer_id, book_id, qty):
    cur = conn.cursor()
    try:
        cur.execute("BEGIN")
        # 재고 확인
        stock = cur.execute(
            "SELECT stock FROM books WHERE id = ?", (book_id,)
        ).fetchone()[0]
        if stock < qty:
            raise ValueError(f"재고 부족: {stock} < {qty}")
        # 주문 기록 + 재고 감소 (한 묶음)
        cur.execute(
            "INSERT INTO orders (customer_id, book_id, quantity) VALUES (?, ?, ?)",
            (customer_id, book_id, qty)
        )
        cur.execute(
            "UPDATE books SET stock = stock - ? WHERE id = ?", (qty, book_id)
        )
        conn.commit()                 # 전부 성공 → 확정
        return "주문 성공"
    except Exception as e:
        conn.rollback()               # 하나라도 실패 → 전부 취소
        return f"주문 실패(롤백): {e}"

실행해 보면:

PYTHON
conn = sqlite3.connect("bookstore.db")
conn.execute("PRAGMA foreign_keys = ON")

print(place_order(conn, 1, 2, 1))      # 데미안 1권 주문
print(place_order(conn, 1, 6, 999))    # 고서 필사본 999권? 재고 부족!

출력:

CODE
주문 성공
주문 실패(롤백): 재고 부족: 1 < 999

두 번째 주문은 재고가 모자라 ValueError가 났고, rollback()이 그때까지의 변경(있었다면)을 전부 되돌렸습니다. "전부 되거나 전부 안 되거나" 가 코드로 구현된 모습입니다.

파이썬 sqlite3의 커밋 습관. 파이썬 sqlite3는 데이터를 바꾸는 문장(INSERT/UPDATE/DELETE) 후 conn.commit()을 호출해야 파일에 영구 저장됩니다. 빠뜨리고 conn.close()하면 변경이 사라질 수 있습니다(DB Browser의 "Write Changes"와 같은 역할). with conn: 구문을 쓰면 블록이 정상 종료될 때 자동 커밋, 예외가 나면 자동 롤백되어 편리합니다.

7.4 pandas와 잇기: 데이터 입문 동선의 완성

SQL로 데이터를 꺼내고 pandas로 가공하는 흐름은 데이터 분석의 가장 흔한 경로입니다. pandas.read_sql은 쿼리 결과를 곧장 데이터프레임(DataFrame)으로 받아 줍니다.

PYTHON
import sqlite3
import pandas as pd

conn = sqlite3.connect("bookstore.db")

# SQL 결과를 데이터프레임으로
df = pd.read_sql("SELECT title, price, stock FROM books ORDER BY price DESC", conn)
print(df.head(3))

출력:

CODE
    title  price  stock
0  고서 필사본  50000      1
1    모비딕  15000      4
2    데미안  12000     10

여기서부터는 pandas의 세계입니다. SQL로 한 집계를 pandas로도 할 수 있죠.

PYTHON
df2 = pd.read_sql("""
    SELECT c.name AS customer, b.price * o.quantity AS subtotal
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    JOIN books b    ON b.id = o.book_id
""", conn)

print(df2.groupby("customer")["subtotal"].sum().sort_values(ascending=False))

출력:

CODE
customer
이두리    51000
김하나    49000
박세찬    24000
최넷별    18000

5부에서 GROUP BY로 구했던 고객별 매출과 똑같은 결과입니다. 반대로 데이터프레임을 테이블로 저장하는 to_sql도 있습니다.

PYTHON
new_books = pd.DataFrame({"title": ["테스트책"], "author": ["아무개"], "price": [5000], "stock": [3]})
new_books.to_sql("books_temp", conn, if_exists="replace", index=False)
어디까지 SQL로, 어디부터 pandas로? 일반적인 지침: 거르고·잇고·요약하는 무거운 작업은 SQL로(데이터베이스가 디스크에서 효율적으로 처리, 필요한 만큼만 메모리로 가져옴), 가져온 뒤의 세밀한 변형·시각화·모델링은 pandas로. 수백만 행을 전부 pandas로 끌어오면 메모리가 터지지만, SQL로 미리 집계해 수십 행으로 줄여 가져오면 가뿐합니다. 둘은 경쟁이 아니라 역할 분담입니다.

7.5 정리

  • 파이썬 표준 sqlite3로 연결(connection) → 커서(cursor) → 실행 → 결과의 패턴을 쓴다. row_factory = sqlite3.Row로 칼럼 이름 접근.
  • 연결할 때마다 PRAGMA foreign_keys = ON을, 변경 후엔 conn.commit()을 잊지 않는다.
  • SQL 인젝션을 막으려면 값을 문자열로 이어 붙이지 말고 반드시 ? 파라미터 바인딩을 쓴다. 가장 중요한 보안 습관.
  • 트랜잭션을 try/except + commit/rollback으로 감싸 "전부 되거나 전부 안 되거나"를 코드로 구현한다.
  • pandas read_sql/to_sql 로 SQL과 데이터프레임을 잇는다. 무거운 거르기·집계는 SQL, 세밀한 가공은 pandas로 역할을 나눈다.

마지막 챕터에서 시야를 더 넓힙니다. SQLite를 넘어 PostgreSQL·MySQL은 언제 필요한지, NoSQL은 무엇인지, 흔히 저지르는 실수는 무엇인지, 그리고 여기서 어디로 더 나아갈지 정리합니다.

직접 해 보기

  1. sqlite3로 bookstore.db에 연결해, 가격이 12000 이상인 책을 ? 바인딩으로 검색해 출력하세요. (입력값을 변수로)
  2. row_factory = sqlite3.Row를 켜고, 각 책을 "{제목} — {가격}원 (재고 {재고})" 형식으로 예쁘게 출력하세요.
  3. 7.3의 place_order 함수를 직접 실행해, 재고가 충분한 주문과 부족한 주문을 각각 넣어 보세요. 부족한 경우 재고가 그대로인지 SELECT로 확인하세요.
  4. pandas.read_sql로 "월별 매출"(5부)을 데이터프레임으로 받아, df.plot() 등으로 그려 보세요(pandas·matplotlib 필요).
  5. (보안 점검) 문자열 이어붙이기 방식과 ? 바인딩 방식의 차이를, "사용자가 ' OR '1'='1을 입력했을 때"를 기준으로 설명해 보세요.
← 6부 인덱스와 뷰 · 목차 · 다음: 다음 단계 →