Blog

Internal of sqlalchemy.sql.expression.func

July 5, 2015

Internal of sqlalchemy.sql.expression.func

※ 이 글은 원본이 갱신되었습니다. 원본을 읽어주시면 감사하겠습니다.

 

SQLAlchemy(이하 ‘SA’라고 줄여서 적음)를 사용하다가 궁금점이 생겼다.
PostgreSQL에는 pgcrypto라고 하는 module이 있는데, 과연 여기서 정의된 함수를 SA에선 어떻게 사용할까 하는 점이었다.

내가 이런 의문을 가질 수 밖에 없는 이유는 SA가 ORM으로써 가능한 거의 모든 영역을 커버하려 하기 때문이다.
예를 들면 아래와 같은 코드는 어떤 DBMS에서건 동작한다.

from sqlalchemy.sql.expression import func
session.query(func.now()).one()

불행히도 SQL은 DBMS별로 상당히 문법이 상이하다.
현재 시간을 구하려면 SQLite에서는 CURRENT_TIMESTAMP를 사용해아하고, MySQL과 PgSQL에선 함수 now()를 사용한다.
자료형을 바꿀때는 SQLite에선 cast('10' AS INTEGER)꼴을 사용하는데 MySQL에선 살짝 다르게 cast('10' AS SIGNED)꼴을 쓴다.
PgSQL에선 cast('10' AS int)와 같이 쓸 수 있지만 field_name::integer 같은 형태로도 가능하다.
(field에 null이 있는 경우엔 문제가 더 심각해진다. ifnull(fieldname, '')::integer같은 형태로 써야한다.)
하지만 현재 시간 구하기, 자료형 바꾸기 모두 func.now()func.cast('10', Integer)로 충분히 가능하다.
SA의 마법같은 부분이라고 할 수 있다.

자, 그럼 내가 쓰려고 하는 pgcrypto의 encrypt_iv(), decrypt_iv() 함수는 SA에서 어떻게 정의되어있을까?
난 일단 내가 하려고 하는 것과 가장 유사한 글을 참조했다.
저 글에서는 decryption을 func.aes_decrypt(~~) 이런 꼴로 사용하고 있었다.
그럼 SA에서는 decryption을 알고리즘별로 ALGONAME_en/decrypt 이렇게 함수를 정의해놓은 것일까?
그렇다면 iv를 별도로 넣어줘야하는 케이스는 어떻게 했을까?
나는 무턱대고 저걸 집어다 쓸 수 없다고 판단하고 SA 소스를 까보기로 했다.

SA의 저장소는 사실 bitbucket에 있다.
하지만 난 구글링하면 나오는 GitHub쪽 저장소를 활용했다.
왜냐하면 GitHub은 저장소 내의 코드 내용을 검색할 수 있기 때문이다.
가장 먼저 검색해본 것은 역시 먼저 봤던 aes_decrypt이다.
하지만 aes_decrypt로 검색해본 결과 아무것도 나오지 않았다.
그럼 아까 세웠던 가설처럼 ALGONAME_en/decrypt 형태로 조합하는건 아닐까 하는 생각이 들어 crypt로 검색해보았다.
하지만 crypt 역시 나오지 않았다.

이쯤되면 sqlalchemy.sql.expression.func가 뭐하는 녀석인지 알아야할 필요가 생겼다. 바로 해당 파일을 열어보았다.
func의 정의는 expression이 아니라 같은 디렉토리의 functions에 있었다.

이것이 func의 정체다.

func = _FunctionGenerator()

_FunctionGenerator는 바로 위에 정의된 클래스이다.
그런데, 내부 구조가 좀 독특하게 되어있다.
우리는 보통 func.now() 이런 형태로 사용한다.
하지만 _FunctionGeneratornow라는 메소드는 정의되어있지 않다.
대신 __getattr____call__이 정의되어 있다.

func.now까지는 __getattr__의 범주에 속한다.

    def __getattr__(self, name):
        # passthru __ attributes; fixes pydoc
        if name.startswith('__'):
            try:
                return self.__dict__[name]
            except KeyError:
                raise AttributeError(name)

        elif name.endswith('_'):
            name = name[0:-1]
        f = _FunctionGenerator(**self.opts)
        f.__names = list(self.__names) + [name]
        return f

__getattr__은 내부적으로 (특수한 경우를 제외하고는) _FunctionGenerator를 만들어서 함수명(예를들어 func.now라면 now)을 내부에 저장하고 return한다.

return되서 나온 값이 _FunctionGenerator 그 다음에(func.now 다음에) 나오는 ()__call__의 범주에 속하게 된다.

    def __call__(self, *c, **kwargs):
        o = self.opts.copy()
        o.update(kwargs)

        tokens = len(self.__names)

        if tokens == 2:
            package, fname = self.__names
        elif tokens == 1:
            package, fname = "_default", self.__names[0]
        else:
            package = None

        if package is not None:
            func = _registry[package].get(fname)
            if func is not None:
                return func(*c, **o)

        return Function(self.__names[-1],
                        packagenames=self.__names[0:-1], *c, **o)

이번엔 Function을 만들어서 return한다.
첫 인자로 self.__names[-1]을 넣는 것을 볼 수 있는데, 이것은 __getattr__에서 맨 뒤에 함수 이름을 넣어서 그렇다.
즉, func.now()를 실행하면 Function('now', ...)과 같은 내용물이 남는 것이다.

여기서부터는 상속의 늪인지라 소스를 뜯어봐도 이해가 되지 않았다.
일단 하나 확실한 것은 Function과 그 부모 클래스인 FunctionElement에도 now는 존재하지 않았다.
더 봐도 알 수 없겠다 싶어서 접근 방법을 바꾸기로 했다.
(왜냐하면 이런 식으로 소스를 까보다가는 SA의 전체 구현 명세를 다 파악해야할 가능성이 농후했기 때문이다.)
Python interactive shell을 사용하기로 했다.

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///', echo=True)
>>> from sqlalchemy.orm import sessionmaker
>>> session = sessionmaker(bind=engine)()
>>> from sqlalchemy.sql.expression import func

일단 이렇게 해서 준비를 마쳤다.
echo=True옵션을 줘서 실제로 어떤 SQL이 실행되는지 볼 수 있도록 했다.
일단 가장 만만한 func.now()를 실행해보기로 했다.

>>> session.query(func.now()).one()
2015-07-05 16:57:07,375 INFO sqlalchemy.engine.base.Engine SELECT CURRENT_TIMESTAMP AS now_1
2015-07-05 16:57:07,376 INFO sqlalchemy.engine.base.Engine ()
(datetime.datetime(2015, 7, 5, 7, 57, 7),)

func.now()CURRENT_TIMESTAMP로 바뀌었다.
이번엔 없을법한 것을 실행해보았다.

>>> session.query(func.utcnow()).one()
2015-07-05 16:59:21,713 INFO sqlalchemy.engine.base.Engine SELECT utcnow() AS utcnow_1
2015-07-05 16:59:21,714 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such function: utcnow

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2473, in one
    ret = list(self)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
    return self._execute_and_instances(context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: utcnow [SQL: 'SELECT utcnow() AS utcnow_1']

실행한 SQL문을 들여다보면 utcnow()라고 바로 실행하려고 했음을 알 수 있다.
한가지만 더 실험해보기로 했다. 아예 있을리도 없는 함수에 이것저것 인자까지 줘보는 것이다.

>>> session.query(func.item4_coding(1, 2, 3, 4, 'blog')).one()
2015-07-05 17:03:54,575 INFO sqlalchemy.engine.base.Engine SELECT item4_coding(?, ?, ?, ?, ?) AS item4_coding_1
2015-07-05 17:03:54,575 INFO sqlalchemy.engine.base.Engine (1, 2, 3, 4, 'blog')
Traceback (most recent call last):
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such function: item4_coding

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2473, in one
    ret = list(self)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
    return self._execute_and_instances(context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/item4/.virtualenvs/testenv/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: item4_coding [SQL: 'SELECT item4_coding(?, ?, ?, ?, ?) AS item4_coding_1'] [parameters: (1, 2, 3, 4, 'blog')]

역시 바로 존재할 리 없는 item4_coding()이란 함수를 실행하려하다 에러가 났다.
여기서 알 수 있는 점은 SA가 실제로는 함수명을 전혀 검사하지 않는다는 점이다.
그럼 func.now()는 어떻게 된 것일까?
나는 이미 func.now()CURRENT_TIMESTAMP로 치환된다는 것을 확인했다.
따라서 GitHub에 대고 CURRENT_TIMESTAMP로 바로 검색을 해보았다. 그리고 흥미로운 파일을 찾아냈다.

이 파일은 바로 DBMS별 dialects중에 SQLite용이다.
이 파일에는 SQLiteCompiler라는 이름으로 이런 메소드가 있었다.

    def visit_now_func(self, fn, **kw):
        return "CURRENT_TIMESTAMP"

우리가 알고있던 동작명세와 일치하는 함수이다.
차이점이라면 now 앞뒤로 visit__func가 붙어있다는 점 정도이다.
그 부분은 소스에서 정말 열심히 훝어보면서 visit__func라는 문자열이 있는 부분을 찾아보았더니 SQLCompiler라는 것이 나왔다.
SQLCompilerSQLiteCompiler의 부모클래스이기도 하다.

    def visit_function(self, func, add_to_result_map=None, **kwargs):
        if add_to_result_map is not None:
            add_to_result_map(
                func.name, func.name, (), func.type
            )

        disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
        if disp:
            return disp(func, **kwargs)
        else:
            name = FUNCTIONS.get(func.__class__, func.name + "%(expr)s")
            return ".".join(list(func.packagenames) + [name]) % 
                {'expr': self.function_argspec(func, **kwargs)}

이쯤되면 더 이상 Internal한 요소를 모두 알지 않아도 대략적인 결론을 내릴 수 있다.

  1. func.~~~~~~는 함수명으로 저장된다.
  2. DBMS별로 SQL을 처리하는 Compiler가 따로 있으며, 각각의 Compiler는 자신의 DBMS만의 특수한 경우를 예외처리한다.
  3. 각각의 Compiler에 예약되어있지 않은 경우 그냥 이름 그대로 함수로써 사용하려고 시도한다. 이때, 정상 동작여부는 보장하지 않는다. 따라서 오타를 주의해야한다.

이것으로 미루어볼때 내가 예제로 본 aes_decrypt를 naive하게 쓰는 것은 MySQL에서만 될 것이다.
MySQL에서도 함수 사용법이 바뀌면 (버전이 올라간다던가 해서) 동작하지 않을것이다.
나는 PgSQL에서 사용하는 방법이 필요한데, 혹시라도 MySQL에서 돌려야하는 상황이 온다면 대안이 필요할 것이다.
이에 대한 문서가 별로 많지 않던데, 실제로 구현을 해보고 테스트해보고 난 뒤 포스팅할 예정이다.
(이 글이 다루려고 했던 범주를 벗어나기때문이기도 하다.)