PyPika 튜토리얼 번역 - (2)

1챕터에서 설치까지 다 끝냈으니 이제 진짜 튜토리얼로 내용을 채우겠다. pypika의 주요 클래스는 pypika.Query, pypika.Table, pypika.Field이므로 이들을 먼저 import하자.

from pypika import Query, Table, Field

Selecting Data

pypika.Query를 통해 쿼리 빌드를 시작할 수 있다. SELECT 쿼리를 통해 테이블에서 데이터를 가져오려면, 먼저 쿼리에 테이블을 추가해야 한다. 정교한 질의를 위해선 pypika.Table을 통해 테이블 객체를 얻어오는 것이 좋지만, 테이블 하나만 있는 간단한 쿼리의 경우 그냥 문자열을 사용해서 이들을 표현할 수도 있다.

q = Query.from_('customers').select('id', 'name', 'lname', 'phone')

쿼리를 raw SQL로 변환하려면, 문자열로 캐스팅하기만 하면 된다.

str(q)

또는, Query 객체의 get_sql() 메소드를 사용할 수 있다.

q.get_sql()

pypika.Table을 사용해서 다음과 같이 쿼리를 빌드할 수도 있다.

customers = Table('customers')
q = Query.from_(customers).select(customers.id, customers.fname, customers.lname, customers.phone)

위 두 예제에서 q.get_sql()의 결과는 다음과 같이 동일하다.

SELECT id,fname,lname,phone FROM customers

Arithmetic

pypika를 이용해 산술 표현식도 쉽게 표현할 수 있다. +, -, *, /와 같은 연산자는 pypika.Table의 속성이나 pypika.Field를 통해 표현할 수 있다.

from pypika import Field

q = Query.from_('account').select(
    Field('revenue') - Field('cost')
)

SELECT revenue-cost FROM accounts

아래는 pypika.Table을 사용하는 예다.

accounts = Table('accounts')
q = Query.from_(accounts).select(
    accounts.revenue - accounts.cost
)

SELECT revenue-cost FROM accounts

alias는 필드와 expression에게 모두 적용할 수 있다.

q = Query.from_(accounts).select(
    (accounts.revenue - accounts.cost).as_('profit')
)

SELECT revenue-cost AS 'profit' profit FROM accounts

아래는 그 외의 산술 표현식 예제다.

table = Table('table')
q = Query.from_(table).select(
    table.foo + table.bar,
    table.foo - table.bar,
    table.foo * table.bar,
    table.foo / table.bar,
    (table.foo+table.bar) / table.fiz,
)

SELECT foo+bar,foo-bar,foo*bar,foo/bar,(foo+bar)/fiz FROM table

Filtering

WHERE 절을 표현하려면, pypika.Criterion을 Query.where() 메소드에 전달하면 된다. 그냥 원하는 필드에 대해 비교 연산자를 사용하면 알아서 Criterion 객체로 변환된다.

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    customers.lname == 'Mustermann'
)

SELECT id,fname,lname,phone FROM customers WHERE lname='Mustermann'

select, where, groupby, orderby와 같은 쿼리 메소드들은 여러 번 호출할 수 있다. where의 경우 여러 번 호출하면 condition들이 AND로 묶인다.

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    customers.fname == 'Max'
).where(
    customers.lname == 'Mustermann'
)

SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'

IN과 BETWEEN 절도 지원된다.

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id,customers.fname
).where(
    customers.age[18:65] & customers.status.isin(['new', 'active'])
)

SELECT id,fname FROM customers WHERE age BETWEEN 18 AND 65 AND status IN ('new','active')

BETWEEN은 Python 내장 슬라이싱 연산을 사용하면서, IN은 왜 in 연산자를 사용하지 않았는지 의문이다.

논리 연산자는 boolean symbol들(&, |, ^)을 사용하여 표현한다.

AND

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    (customers.age >= 18) & (customers.lname == 'Mustermann')
)

SELECT id,fname,lname,phone FROM customers WHERE age>=18 AND lname='Mustermann'

OR

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    (customers.age >= 18) | (customers.lname == 'Mustermann')
)

SELECT id,fname,lname,phone FROM customers WHERE age>=18 OR lname='Mustermann'

XOR

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    (customers.age >= 18) ^ customers.is_registered
)

SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered

Ordering, Grouping and Aggregating

Ordering(order by)은 결과를 정렬하고, Grouping(group by)은 결과를 집계하기 위해 사용한다.

from pypika import Order, functions as fn

customers = Table('customers')
q = Query.from_(customers).where(
    customers.age >= 18
).groupby(
    customers.id
).orderby(
	customers.id, order=Order.asc
).select(
    customers.id, fn.Sum(customers.revenue)
)

SELECT id,SUM(revenue) FROM customers WHERE age>=18 GROUP BY id ORDER BY id ASC

GROUP BY절이 쿼리에 추가되면, HAVING 절을 사용할 수 있게 된다. Query.where()과 동일하게, Query.having()도 Criterion 객체를 파라미터로 받는다.

from pypika import functions as fn

payments = Table('payments')
q = Query.from_(payments).where(
    payments.transacted[date(2015, 1, 1):date(2016, 1, 1)]
).groupby(
    payments.customer_id
).having(
    fn.Sum(payments.total) >= 1000
).select(
    payments.customer_id, fn.Sum(payments.total)
)

SELECT customer_id,SUM(total) FROM payments
WHERE transacted BETWEEN '2015-01-01' AND '2016-01-01'
GROUP BY customer_id HAVING SUM(total)>=1000

Joining Tables and Subqueries

USING을 설명하는 부분의 코드가 잘못 작성되어 있어서, 다시 작성했다.

테이블과 서브쿼리는 Query.join() 메소드를 통해 join을 걸 수 있다. 기본적으로 SQL에서 join은 USING이나 ON 절을 포함할 수 있으며 PyPika는 당연히 이들에 대응되는 메소드들을 지원하고 있다.

Query.join().on()

ON 절을 사용하는, 가장 일반적인 형태의 join이다.

history, customers = Tables('history', 'customers')
q = Query.from_(history).join(
    customers
).on(
    history.customer_id == customers.id
).select(
    history.star
).where(
    customers.id == 5
)

SELECT "history".* FROM "history" JOIN "customers" ON "history"."customer_id"="customers"."id"
WHERE "customers"."id"=5

Tables라는 표현이 등장했다. 이는 생성자 인자로 전달된 테이블 이름들에 대해 순서대로 Table 객체를 만들어 튜플과 같은 연속열을 반환한다.

Query.join().on_field()

조인 대상의 컬럼 이름이 동일하다면, Query.join().on_field() 메소드에 이름만 넘기는 식으로 ON 쿼리를 자동 구성하도록 만들 수도 있다.

history, customers = Tables('history', 'customers')
q = Query.from_(history).join(
    customers
).on_field(
    'customer_id', 'group'
).select(
    history.star
).where(
    customers.group == 'A'
)

SELECT "history".* FROM "history"
JOIN "customers" ON "history"."customer_id"="customers"."customer_id" AND "history"."group"="customers"."group" 
WHERE "customers"."group"='A'

Query.join().using()

위처럼 조인 대상의 컬럼 이름이 동일한 경우, USING 쿼리를 사용하는 것도 방법이다.

history, customers = Tables('history', 'customers')
q = Query.from_(history).join(
    customers
).using(
    'customer_id'
).select(
    history.star
).where(
    customers.id == 5
)

SELECT "history".* FROM "history" JOIN "customers" USING "customer_id"
WHERE "customers"."id"=5

ON과 USING의 차이는 Stackoverflow의 MySQL ON vs USING?이라는 질문을 확인하자.