PlanB의 백엔드 엔지니어링

engine은 일반적으로 sqlalchemy.engine.create_engine 함수에 의해 생성되는, SQLAlchemy에서 사용되는 lowest level의 객체다. engine은 어플리케이션이 데이터베이스와 통신할 때마다 사용할 수 있는 connection pool을 유지한다. 쿼리를 위해 사용하는 engine.execute는 engine.connect(close_with_result=True)를 수행해 Connection 객체를 얻고, conn.execute를 호출하는 편리한 메소드다. ORM을 사용하지 않아서, sqlalchemy.text 함수로 객체에 바인딩되지 않은 straight SQL 쿼리를 수행하는 경우 이처럼 engine과 connection을 사용하는 것이 적절한 방법이다.

어플리케이션이 SQLAlchemy ORM을 사용한다면, 객체에 바인딩된 쿼리를 위해서 Session 객체를 사용해야 한다. 이는 session.add(), session.rollback(), session.commit(), session.close()를 통해 트랜잭션을 단일 작업 단위로 관리하기 좋고, 이러한 특징을 통해 Python의 Context Manager 패턴을 사용하기에도 좋다.

session을 생성해 yield하고, 정상적인 경우 commit, 오류가 발생하면 rollback, 어느 상황이든 close하도록 만든다. 쿼리가 필요한 부분을 with-as 블럭으로 감싸기만 하면, global한 트랜잭션 관리는 context manager가 모두 수행해 준다. SQLAlchemy 문서에서는 위와 같은 구현을 scoped session이라고 이야기하고 있다. 따라서 raw string SQL을 사용하는 경우 engine이나 connection, ORM을 사용하는 경우 session과 scoped session 패턴을 사용하는 것이 적합하다고 생각한다. session 객체에서도 물론 execute 메소드가 지원되기 때문에, 어플리케이션 전체에 걸쳐 그냥 session만 사용하는 practice도 꽤 있었던 것 같다.

앞으로 블로그의 SQLAlchemy 카테고리에 올라올 포스트들의 예제는, scoped session이 적용되어 있다(session_scope 함수가 이미 존재한다)는 것을 가정하고 작성될 예정이다.

SQL에는 스키마가 동일한 두 테이블을 합쳐 쿼리하는 UNION과, SELECT 절에 리터럴한 expression을 두는 Literal Select라는 개념이 존재한다.

SQLAlchemy에서는 Query 객체의 union(*q), union_all(*q)로 각각 UNION/UNION ALL을, sqlalchemy.sql.expression.literal_column 함수를 통해 literal select를 표현할 수 있다. 위에서 예를 든 union 쿼리를 SQLAlchemy로 표현해 보자.

query 객체에는 filter 메소드가 있다. SQL로 따지면 WHERE clause의 역할이다. filter 메소드에 expression을 전달하면, 새로운 query 객체를 반환한다. 아래 예제는 scoped session이 적용되어 있고, TblUsers라는 ORM 클래스가 적당히 존재한다고 가정한다.

조건 결합, 추가적인 관계 연산자

query 객체의 filter 메소드에 sqlalchemy.sql 하위 함수, 또는 ORM 클래스 필드의 SQL expression 메소드를 전달하여 조건식을 결합하거나, 일반적인 관계 연산자로 표현하기 힘든 IN, BETWEEN 등의 조건식을 표현할 수 있다.

SQLAlchemy의 query 객체가 메소드 형식으로 WHERE절을 빌드하는 컨셉만 알고 있도록 하자. 나중에 필요한 쿼리가 있을 때 구글링해서 찾아보기 더 편하다.

sqlalchemy.sql.text는 raw string 형태의 SQL을 Connection.execute나 그에 상응하는 함수(engine.execute, session.execute)에 전달하여 쿼리하기 위한 함수다.

placeholder 기능을 지원하며, :[name] 포맷을 사용하고 키워드 인자로 이들에 값을 채운다.

되도록이면 ORM을 쓰자. 개인적으로는 ORM만 충실히 써도 어플리케이션 코드와 데이터베이스 간의 결합도를 많이 낮출 수 있었던 것 같다.

SQLAlchemy에서 ORM을 사용하기 위해, 스키마를 class로 정의하는 방법이 있다.

sqlalchemy.declarative_base 함수를 통해 Base를 얻고, 이를 상속받는 형태로 모델을 구현한다. 클래스의 내부에는 __tablename__이라는 클래스 필드로 테이블 이름을 명시하며, Column 클래스의 생성자에 sqlalchemy.types 모듈 하위에 구현되어 있는, 타입을 나타내는 클래스를 넘겨 Column 타입의 객체들로 컬럼을 명시한다. 타입 정보를 생성자에 전달할 때, 클래스 자체를 넘겨도 되고 클래스의 인스턴스를 넘겨도 된다. Column(Integer)와 Column(Integer())가 동일하다는 것이다. sqlalchemy의 타입 클래스, SQL DDL에서의 타입, Python의 빌트인 타입 간 매핑은 다음과 같다. 자주 사용하는 타입 위주로 작성했다.

• sqlalchemy.types.BigInteger - BIGINT - int
• sqlalchemy.types.SmallInteger - SMALLINT - int
• sqlalchemy.types.Integer - INT - int
• sqlalchemy.types.Boolean - BOOLEAN or SMALLINT or TINYINT - bool
• sqlalchemy.types.DateTime - TIME or TIMESTAMP - datetime.datetime
• sqlalchemy.types.Float : FLOAT or REAL - float | asdecimal 인자를 True로 설정하면, decimal.Decimal 타입으로 다뤄진다.
• sqlalchemy.types.PickleType - BLOB or TINYBLOB or MEDIUMBLOB or LONGBLOB - pickle로 직렬화 가능한 모든 객체 | list나 dict를 관리할 때 편리하게 사용할 수 있다.
• sqlalchemy.types.String - CHAR or VARCHAR or TEXT - str
• sqlalchemy.types.Text - CLOB or TEXT - str

BigInteger와 BIGINT, Boolean과 BOOLEAN, Text와 TEXT, ...

SQLAlchemy로 모델을 정의하다 보면, uppercase된 알파벳으로만 정의된 타입을 확인할 수 있다. SQLAlchemy 문서에서는 BigInteger, Boolean, Text 등을 Generic Type이라 부르고, BIGINT, BOOLEAN, TEXT 등을 SQL Standard and Multiple Vendor Type이라고 부른다.

Generic Type

BigInteger, Boolean, Text, DateTime, Float, Integer, ...

'Python 타입'의 데이터를 읽고, 쓰고, 저장할 수 있는 column을 명시한다. SQLAlchemy는 Generic Type의 컬럼에 대해 CREATE TABLE문을 만들어낼 때, 대상 데이터베이스에서 사용할 수 있는 최상의 타입을 알아서 선택한다. VARCHAR로 정의해 뒀는데 데이터베이스가 VARCHAR를 지원하지 않는다면, 그를 대체할 수 있는 타입으로 변경하여 DDL을 작성한다. 대충 써두면, 알아서 테이블을 생성해준다는 의미다. 코드 레벨에서 조금 더 편하다.

SQL Standard and Multiple Vendor Type

BIGINT, BOOLEAN, TEXT, BLOB, CHAR, CLOB, ...

SQLAlchemy에서 여기에 속하는 타입의 컬럼을 통해 CREATE TABLE문을 만들 때는, 데이터베이스 엔진에 상관 없이 컬럼의 타입을 항상 동일하게 만들어낸다. SQLAlchemy가 원하는 타입으로 정확한 DDL을 작성하도록 만들 때 유용할 수 있으나, Generic type과 달리 이들은 모든 데이터베이스에서 잘 작동한다는 보장이 없다. 따라서, 대충 'VARCHAR'라고 했다가 데이터베이스가 VARCHAR를 지원하지 않으면 DDL에 실패한다.

Vendor-Specific Type

mysql.BIGINT, mysql.BINARY, postgresql.REAL, oracle.VARCHAR2, ...

'벤더별로 명시되어 있는 타입'을 의미하는데, 여기서 벤더는 MySQL, PostgreSQL 등을 떠올리면 된다. sqlalchemy.dialects 패키지 하위에 데이터베이스별로 패키지가 존재하고, 여기에 명시되어 있는 타입을 사용하는 방식이다.

SQLAlchemy에서 query 객체의 filter 메소드에 전달하는 관계 연산자는 Python에 내장된 관계 연산 기호(<, >, == 등)나, Column 객체에서 지원하는 메소드(Column.between, Column.in_ 등)를 사용할 수 있었다. SQLAlchemy의 query 객체의 filter 메소드를 통해 LIKE나 ILIKE(case sensitive LIKE) 쿼리를 표현하려면, 각각 Column.like와 Column.ilike 메소드를 사용할 수 있다.

not

NOT을 표현하는 방법은, and와 or이 각각 sqlalchemy.and_, sqlalchemy.or_로 지원되었던 것을 생각하면 된다. sqlalchemy.not_ 함수를 사용한다.

또는, 단항 비트 반전을 위해 한번쯤 사용했던 unary operator(~)를 사용할 수 있다.

SQLAlchemy를 통해 쿼리하는 경우, 테이블 모델 자체를 넘기는 것이 가장 일반적이다.

SQL로 치면, SELECT * FROM ... 꼴이 될 것이다. SELECT id FROM ...처럼 특정 컬럼만 조회하고 싶다면, 해당 모델의 컬럼 객체들을 가변 인자 형태로 전달해주면 된다.

위 예제는 TblUsers에서 id, name, email을 SELECT한다. TblUsers의 테이블 이름이 tbl_users라면, SELECT id, name, email FROM tbl_users로 표현할 수 있을 것이다. 이 경우 쿼리의 결과 객체에서는 id, name, email만 접근할 수 있다. 다른 필드에 접근하려고 하면, TblUsers에 명시되어 있는 컬럼이더라도 에러가 발생한다.

SQL에서 AS 절은 SELECT 절의 expression에 별명을 붙여주는 역할을 한다. SQLAlchemy에서는 컬럼 객체의 label 메소드를 통해 labeled column을 얻어내는 방식으로 AS 절을 표현할 수 있다.

TblUsers.pw 컬럼을 password라는 이름으로 aliasing하여 SELECT했으므로 row 객체에서의 속성 이름도 pw가 아니라 password가 된다. 이렇게 aliasing된 expression을 다른 곳에서 사용하려면, filter 등에 string expression으로 표현하면 된다.

무조건 string expression을 써야 하는 것은 아니다. aliasing되었더라도 filter(func.length(TblUsers.pw) > 16)같은 표현식을 쓸 수 있다. 그러나 aliasing이라는 것 자체가 어떠한 expression에 별명을 지어주는 것이기 때문에, 위처럼 단지 filter에 문자열을 전달하는 것으로 alias된 이름을 통한 쿼리를 표현할 수 있다는 것이다.

함수

aliasing은 SELECT 절에서 함수를 사용할 때 유용하다. SQLAlchemy에서는 SQL의 함수들이 sqlalchemy.sql.expression.func 모듈에 준비되어 있다.

쿼리의 결과 row 수를 제한하기 위해 LIMIT 쿼리를 사용한다. 사용자가 리소스를 만들어가는 형태의 서비스(SNS, 게시판 블로그 등)는 데이터들이 수직적으로 늘어나는데, 이런 곳에서는 일반적으로 목록 데이터를 넘겨줄 때 pagination 개념을 적용한다. 페이스북 뉴스피드에서 포스트가 가장 처음에는 10개 남짓 보이다가, 스크롤이 끝나면 포스트가 새롭게 로드되는 것이 그 예다.

사용자에겐 자체적인 rank 알고리즘이나, 특별한 기준(가장 최신 글부터 등)에 따라 먼저 몇 개 정도만 보여주고, 원하면 더 불러오게 만드는 것이다. 수만 개가 넘는 포스트 목록이 있어봤자 사용자는 그들 중 일부만 볼텐데, 그 데이터를 다 넘겨줘 봐야 불필요하게 화면에 렌더링시키는 시간만 늘어나고, 네트워크 비용이 낭비될테니 말이다.

이런저런 이야기들로 LIMIT 쿼리의 의도를 설명했는데, 아무튼 결론적으로는 자원 낭비를 줄이기 위해 LIMIT 쿼리를 사용한다. 목록 데이터를 다 내려주기엔 양이 너무 많아서던, 표본 추출(Sampling) 방식으로 통계를 내기 위해서던 말이다.

SQLAlchemy에서 limit은 그냥 query 객체에 슬라이싱을 씌우면 된다. Query 클래스에는 슬라이싱을 처리할 수 있는 __getitem__ 매직 메소드가 구현되어 있고, 이는 n:m으로 슬라이싱했을 때 쿼리에 LIMIT n OFFSET m을 추가하고 all의 호출 결과를 반환하는 역할을 한다. 주저리주저리 열심히 설명했지만 참 간단하게 된다.

query 객체의 slice 메소드를 사용하는 것도 한가지 방법이다.

SQLAlchemy를 쓰면서 가장 혼란스러운 것 중 하나가 'Query 객체는 도대체 언제 SELECT 쿼리를 수행하는가?'였다. 마주치게 되는 상황은 이렇다.

• query.all()로 SELECT의 결과를 모두 가져올 수 있다고 한다. 이는 전형적인 반복자(iterator)로, for row in query.all()처럼 for문에서 사용할 수 있다.
• 그런데 그냥 query 자체를 for문에 넣는 예제도 보인다. for row in query를 해도 문제가 없다.
• LIMIT 쿼리를 어떻게 표현하는지 검색했더니, 그냥 슬라이싱하라고 한다. 그래서 for row in query[:10].all()로 했더니 에러가 발생하고, for row in query[:10]처럼 표현하니 제대로 동작한다.

그냥 SQLAlchemy의 소스 코드를 GitHub에서 살펴보면, 그 비밀을 알 수 있다. sqlalchemy.orm.query.py 모듈을 보면 되고, 아래는 그들 중 일부를 가져온 snippet이다.

__iter__

Query 객체는 __iter__ 메소드에서 실제로 SELECT를 수행한다고 요약할 수 있다. __iter__ 메소드가 호출되는 타이밍 중 몇개를 예로 들면,

• iter 함수에 전달되었을 때
• for문에 사용되었을 때(for문은 in 우측에 전달된 객체를 iter 함수에 전달하므로)
• list 함수에 전달되었을 때

그럼 의문이 조금 풀린다.

• for row in query : for문에 의해 간접적으로 __iter__가 호출되므로 잘 동작한다.
• for row in query.all() : 위 snippet을 보면 알 수 있듯, list(self)의 반환을 iteration하므로 잘 동작한다.

__getitem__

__getitem__ 메소드는 인덱싱과 슬라이싱 연산을 오버라이딩한다. Query.__getitem__ 메소드는

• 슬라이스를 받았다면 현재 Query 객체에 LIMIT 쿼리를 추가하는 self.slice 메소드를 호출한 후, 이를 통해 LIMIT이 반영된 Query 객체의 SELECT 결과를 반환하거나
• 인덱스를 받았고 그 인덱스가 -1이라면 list(self)[-1]을 반환하고, 아니라면 list(self[item:item + 1])[0]으로 __getitem__을 재귀호출하여 해당 인덱스에 대한 row 하나만을 가져오도록 LIMIT 쿼리를 수행해서 경제적으로 row를 반환한다. 예를 들어 query[15]에 대해서는, LIMIT 15, 1 쿼리가 추가된다.

따라서,

• for row in query[5:8] : __getitem__에 슬라이스가 전달되고, LIMIT 5, 3 쿼리가 추가된 Query 객체의 iterator가 반환된다.
• row = query[10] : __getitem__에 인덱스가 전달되고, LIMIT 10, 1 쿼리가 추가된 Query 객체의 결과 중 0번째 인덱스가 반환된다.