SQLAlchemy : 실제 쿼리 인쇄

SQLAlchemy : 실제 쿼리 인쇄

---

바인드 매개 변수가 아닌 값을 포함하여 내 응용 프로그램에 유효한 SQL을 실제로 인쇄하고 싶지만 SQLAlchemy 에서이 작업을 수행하는 방법은 확실하지 않습니다 (설계에 따르면 상당히 확실합니다).

누구 든지이 문제를 일반적인 방식으로 해결 했습니까?

---

대부분의 경우 SQLAlchemy 문 또는 쿼리의 "문자열"은 다음과 같이 간단합니다.

print str(statement)

이것은 ORM Query뿐만 아니라 select()다른 진술 에도 적용됩니다 .

참고 : 다음 자세한 답변은 sqlalchemy 설명서 에서 유지 관리됩니다 .

명령문을 특정 방언 또는 엔진에 컴파일 된 것으로 가져 오려면 명령문 자체가 아직 바인딩되지 않은 경우이를 compile () 에 전달할 수 있습니다 .

print statement.compile(someengine)

또는 엔진이없는 경우 :

from sqlalchemy.dialects import postgresql
print statement.compile(dialect=postgresql.dialect())

ORM Query객체가 주어지면 compile()메소드 를 얻으려면 먼저 .statement 접근 자만 액세스하면 됩니다 .

statement = query.statement
print statement.compile(someengine)

바운드 매개 변수가 최종 문자열에 "인라인"되어야한다는 원래 규정과 관련하여 여기서 해결해야 할 과제는 SQLAlchemy가 일반적으로이 작업을 수행하지 않는 것입니다. 아마도 현대 웹 응용 프로그램에서 가장 널리 이용되는 보안 허점 일 것입니다. SQLAlchemy는 DDL을 방출하는 것과 같은 특정 상황에서 이러한 엄격화를 수행하는 능력이 제한되어 있습니다. 이 기능에 액세스하기 위해 'literal_binds'플래그를 사용할 수 있습니다 compile_kwargs.

from sqlalchemy.sql import table, column, select

t = table('t', column('x'))

s = select([t]).where(t.c.x == 5)

print s.compile(compile_kwargs={"literal_binds": True})

위의 접근법에는 int 및 string과 같은 기본 유형에만 지원된다는 경고가 있으며 bindparam, 사전 설정 값이없는 직접 사용하는 경우에도 문자열을 지정할 수 없습니다.

지원되지 않는 유형에 대한 인라인 리터럴 렌더링을 지원하려면 메소드 TypeDecorator가 포함 된 대상 유형에 대해를 구현하십시오 TypeDecorator.process_literal_param.

from sqlalchemy import TypeDecorator, Integer


class MyFancyType(TypeDecorator):
    impl = Integer

    def process_literal_param(self, value, dialect):
        return "my_fancy_formatting(%s)" % value

from sqlalchemy import Table, Column, MetaData

tab = Table('mytable', MetaData(), Column('x', MyFancyType()))

print(
    tab.select().where(tab.c.x > 5).compile(
        compile_kwargs={"literal_binds": True})
)

다음과 같은 출력을 생성합니다.

SELECT mytable.x
FROM mytable
WHERE mytable.x > my_fancy_formatting(5)

---

이것은 파이썬 2와 3에서 작동하며 이전보다 약간 깨끗하지만 SA> = 1.0이 필요합니다.

from sqlalchemy.engine.default import DefaultDialect
from sqlalchemy.sql.sqltypes import String, DateTime, NullType

# python2/3 compatible.
PY3 = str is not bytes
text = str if PY3 else unicode
int_type = int if PY3 else (int, long)
str_type = str if PY3 else (str, unicode)


class StringLiteral(String):
    """Teach SA how to literalize various things."""
    def literal_processor(self, dialect):
        super_processor = super(StringLiteral, self).literal_processor(dialect)

        def process(value):
            if isinstance(value, int_type):
                return text(value)
            if not isinstance(value, str_type):
                value = text(value)
            result = super_processor(value)
            if isinstance(result, bytes):
                result = result.decode(dialect.encoding)
            return result
        return process


class LiteralDialect(DefaultDialect):
    colspecs = {
        # prevent various encoding explosions
        String: StringLiteral,
        # teach SA about how to literalize a datetime
        DateTime: StringLiteral,
        # don't format py2 long integers to NULL
        NullType: StringLiteral,
    }


def literalquery(statement):
    """NOTE: This is entirely insecure. DO NOT execute the resulting strings."""
    import sqlalchemy.orm
    if isinstance(statement, sqlalchemy.orm.Query):
        statement = statement.statement
    return statement.compile(
        dialect=LiteralDialect(),
        compile_kwargs={'literal_binds': True},
    ).string

데모:

# coding: UTF-8
from datetime import datetime
from decimal import Decimal

from literalquery import literalquery


def test():
    from sqlalchemy.sql import table, column, select

    mytable = table('mytable', column('mycol'))
    values = (
        5,
        u'snowman: ☃',
        b'UTF-8 snowman: \xe2\x98\x83',
        datetime.now(),
        Decimal('3.14159'),
        10 ** 20,  # a long integer
    )

    statement = select([mytable]).where(mytable.c.mycol.in_(values)).limit(1)
    print(literalquery(statement))


if __name__ == '__main__':
    test()

이 출력을 제공합니다 : (python 2.7 및 3.4에서 테스트 됨)

SELECT mytable.mycol
FROM mytable
WHERE mytable.mycol IN (5, 'snowman: ☃', 'UTF-8 snowman: ☃',
      '2015-06-24 18:09:29.042517', 3.14159, 100000000000000000000)
 LIMIT 1

---

디버깅 할 때만 원하는 것이 의미가 있다고 생각하면 SQLAlchemy를 사용 echo=True하여 모든 SQL 쿼리를 기록 할 수 있습니다. 예를 들면 다음과 같습니다.

engine = create_engine(
    "mysql://scott:tiger@hostname/dbname",
    encoding="latin1",
    echo=True,
)

단일 요청에 대해서만 수정할 수도 있습니다.

echo=False–이면 True엔진은 모든 명령문과 repr()매개 변수 목록을 엔진 로거에 기본값으로 기록합니다 sys.stdout. 의 echo속성은 Engine언제든지 로깅을 켜거나 끄도록 수정할 수 있습니다. string으로 설정하면 "debug"결과 행도 표준 출력으로 인쇄됩니다. 이 플래그는 궁극적으로 Python 로거를 제어합니다. 로깅을 직접 구성하는 방법에 대한 정보는 로깅 구성을 참조하십시오 .

소스 : SQLAlchemy 엔진 구성

플라스크와 함께 사용하면 간단하게 설정할 수 있습니다

app.config["SQLALCHEMY_ECHO"] = True

같은 행동을 취합니다.

---

이 목적으로 컴파일 방법을 사용할 수 있습니다 . 로부터 문서 :

from sqlalchemy.sql import text
from sqlalchemy.dialects import postgresql

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")

print(stmt.compile(dialect=postgresql.dialect(),compile_kwargs={"literal_binds": True}))

결과:

SELECT * FROM users WHERE users.name BETWEEN 'm' AND 'z'

문서에서 경고 :

웹 양식이나 다른 사용자 입력 응용 프로그램과 같은 신뢰할 수없는 입력에서 수신 한 문자열 내용에는이 기술을 사용하지 마십시오. Python 값을 직접 SQL 문자열 값으로 강제 변환하는 SQLAlchemy의 기능은 신뢰할 수없는 입력에 대해 안전하지 않으며 전달되는 데이터 유형의 유효성을 검증하지 않습니다. 관계형 데이터베이스에 대해 비 DDL SQL 문을 프로그래밍 방식으로 호출 할 때는 항상 바인딩 된 매개 변수를 사용하십시오.

---

그래서 @bukzor의 코드에 대한 @zzzeek의 의견을 바탕으로 "꽤 인쇄 가능한"질의를 쉽게 얻기 위해 이것을 생각해 냈습니다.

def prettyprintable(statement, dialect=None, reindent=True):
    """Generate an SQL expression string with bound parameters rendered inline
    for the given SQLAlchemy statement. The function can also receive a
    `sqlalchemy.orm.Query` object instead of statement.
    can 

    WARNING: Should only be used for debugging. Inlining parameters is not
             safe when handling user created data.
    """
    import sqlparse
    import sqlalchemy.orm
    if isinstance(statement, sqlalchemy.orm.Query):
        if dialect is None:
            dialect = statement.session.get_bind().dialect
        statement = statement.statement
    compiled = statement.compile(dialect=dialect,
                                 compile_kwargs={'literal_binds': True})
    return sqlparse.format(str(compiled), reindent=reindent)

개인적으로 들여 쓰기되지 않은 코드를 읽는 데 어려움 sqlparse을 겪어 SQL을 다시 들여 쓰는 데 사용 했습니다. 로 설치할 수 있습니다 pip install sqlparse.

---

This code is based on brilliant existing answer from @bukzor. I just added custom render for datetime.datetime type into Oracle's TO_DATE().

Feel free to update code to suit your database:

import decimal
import datetime

def printquery(statement, bind=None):
    """
    print a query, with values filled in
    for debugging purposes *only*
    for security, you should always separate queries from their values
    please also note that this function is quite slow
    """
    import sqlalchemy.orm
    if isinstance(statement, sqlalchemy.orm.Query):
        if bind is None:
            bind = statement.session.get_bind(
                    statement._mapper_zero_or_none()
            )
        statement = statement.statement
    elif bind is None:
        bind = statement.bind 

    dialect = bind.dialect
    compiler = statement._compiler(dialect)
    class LiteralCompiler(compiler.__class__):
        def visit_bindparam(
                self, bindparam, within_columns_clause=False, 
                literal_binds=False, **kwargs
        ):
            return super(LiteralCompiler, self).render_literal_bindparam(
                    bindparam, within_columns_clause=within_columns_clause,
                    literal_binds=literal_binds, **kwargs
            )
        def render_literal_value(self, value, type_):
            """Render the value of a bind parameter as a quoted literal.

            This is used for statement sections that do not accept bind paramters
            on the target driver/database.

            This should be implemented by subclasses using the quoting services
            of the DBAPI.

            """
            if isinstance(value, basestring):
                value = value.replace("'", "''")
                return "'%s'" % value
            elif value is None:
                return "NULL"
            elif isinstance(value, (float, int, long)):
                return repr(value)
            elif isinstance(value, decimal.Decimal):
                return str(value)
            elif isinstance(value, datetime.datetime):
                return "TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')" % value.strftime("%Y-%m-%d %H:%M:%S")

            else:
                raise NotImplementedError(
                            "Don't know how to literal-quote value %r" % value)            

    compiler = LiteralCompiler(dialect, statement)
    print compiler.process(statement)

---

I would like to point out that the solutions given above do not "just work" with non-trivial queries. One issue I came across were more complicated types, such as pgsql ARRAYs causing issues. I did find a solution that for me, did just work even with pgsql ARRAYs:

borrowed from: https://gist.github.com/gsakkis/4572159

The linked code seems to be based on an older version of SQLAlchemy. You'll get an error saying that the attribute _mapper_zero_or_none doesn't exist. Here's an updated version that will work with a newer version, you simply replace _mapper_zero_or_none with bind. Additionally, this has support for pgsql arrays:

# adapted from:
# https://gist.github.com/gsakkis/4572159
from datetime import date, timedelta
from datetime import datetime

from sqlalchemy.orm import Query


try:
    basestring
except NameError:
    basestring = str


def render_query(statement, dialect=None):
    """
    Generate an SQL expression string with bound parameters rendered inline
    for the given SQLAlchemy statement.
    WARNING: This method of escaping is insecure, incomplete, and for debugging
    purposes only. Executing SQL statements with inline-rendered user values is
    extremely insecure.
    Based on http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query
    """
    if isinstance(statement, Query):
        if dialect is None:
            dialect = statement.session.bind.dialect
        statement = statement.statement
    elif dialect is None:
        dialect = statement.bind.dialect

    class LiteralCompiler(dialect.statement_compiler):

        def visit_bindparam(self, bindparam, within_columns_clause=False,
                            literal_binds=False, **kwargs):
            return self.render_literal_value(bindparam.value, bindparam.type)

        def render_array_value(self, val, item_type):
            if isinstance(val, list):
                return "{%s}" % ",".join([self.render_array_value(x, item_type) for x in val])
            return self.render_literal_value(val, item_type)

        def render_literal_value(self, value, type_):
            if isinstance(value, long):
                return str(value)
            elif isinstance(value, (basestring, date, datetime, timedelta)):
                return "'%s'" % str(value).replace("'", "''")
            elif isinstance(value, list):
                return "'{%s}'" % (",".join([self.render_array_value(x, type_.item_type) for x in value]))
            return super(LiteralCompiler, self).render_literal_value(value, type_)

    return LiteralCompiler(dialect, statement).process(statement)

Tested to two levels of nested arrays.

참고URL : https://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query