Custom SQL 함수 + 집계

~12 min · python, create_function, extensibility

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

SQL 안에서 Python 호출

SQLite는 SQL에서 부를 수 있는 Python 함수를 등록하게 해줘. 엔진 안에서 제대로 대접받는 함수라 WHERE에도, SELECT list에도, 인덱스에도, trigger에도 쓸 수 있어. 주로 쓰는 API는 둘이야.

conn.create_function(name, n_args, callable, deterministic=False) — scalar 함수야. row 하나 받아 값 하나 내놔.
conn.create_aggregate(name, n_args, AggregateClass) — 집계 함수야. 여러 row를 받아 값 하나 내놔.

어디에 쓰냐면, regex extension이 없을 때 정규식 매칭을 대신하거나, 도메인에 맞춘 점수 계산을 하거나, JSON1보다 Python이 더 잘하는 JSON 조작을 하거나, 비교 함수를 직접 만들 때야.

Warning: Python 쪽 함수는 호출할 때마다 C와 Python 경계를 넘나들어. SQL 기준으로는 느리다는 뜻이지. 처리량보다 정확성이 중요한 자리에만 써. 백만 row가 도는 뜨거운 루프에는 넣지 마.

Code

Scalar 함수 — SQL에서 Python regex·python

import sqlite3, re

def regexp(pattern, value):
    if value is None:
        return False
    return re.search(pattern, value) is not None

conn = sqlite3.connect('demo.db')
conn.create_function('regexp', 2, regexp, deterministic=True)

rows = conn.execute(
    "SELECT * FROM users WHERE email REGEXP '^a.*@gmail\\.com$'"
).fetchall()

집계 — 직접 만든 표준편차·python

class StdDev:
    def __init__(self):
        self.values = []

    def step(self, value):
        if value is not None:
            self.values.append(value)

    def finalize(self):
        if not self.values:
            return None
        m = sum(self.values) / len(self.values)
        v = sum((x - m) ** 2 for x in self.values) / len(self.values)
        return v ** 0.5

conn.create_aggregate('stddev', 1, StdDev)
row = conn.execute('SELECT stddev(score) FROM submissions').fetchone()
print(row[0])

External links

Exercise

Python regexp 함수를 등록해서 WHERE 절에서 복잡한 패턴을 매칭해봐. 그다음 중앙값을 구하는 Python 집계를 만들어. SQLite에는 median이 없거든. 큰 테이블에서 이 Python 집계와 SELECT ... ORDER BY ... LIMIT 1 OFFSET n/2의 실제 시간을 견줘봐.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousPython의 Transaction — Implicit 모델 Next →Connection Pragma + 설정

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.