복붙노트

[PYTHON] SQLAlchemy : 실제 쿼리 인쇄

PYTHON

SQLAlchemy : 실제 쿼리 인쇄

정말 매개 변수를 바인딩하는 것이 아니라 값을 포함하여 내 응용 프로그램에 유효한 SQL을 인쇄 할 수 있기를 원하지만 SQLAlchemy에서이를 수행하는 방법은 분명하지 않습니다 (설계 상으로는 상당히 확신합니다).

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

해결법

  1. ==============================

    1.대다수의 경우 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가 일반적으로이 역할을 맡지 않습니다. 이는 바인딩 된 매개 변수를 무시하지 않고 Python DBAPI에서 적절히 처리하므로 아마도 현대 웹 응용 프로그램에서 가장 널리 이용되는 보안 허점 일 것입니다. 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 및 문자열), 그리고 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. ==============================

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

    이것은 파이썬 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
  3. ==============================

    3.따라서 @ zzzeek의 @ bukzor 코드에 대한 의견을 바탕으로 "pretty-printable"쿼리를 쉽게 얻을 수있었습니다.

    따라서 @ zzzeek의 @ bukzor 코드에 대한 의견을 바탕으로 "pretty-printable"쿼리를 쉽게 얻을 수있었습니다.

    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를 사용하여 설치할 수 있습니다.

  4. ==============================

    4.이 코드는 @bukzor의 멋진 기존 답변을 기반으로합니다. 방금 datetime.datetime 형식의 사용자 지정 렌더링을 Oracle의 TO_DATE ()에 추가했습니다.

    이 코드는 @bukzor의 멋진 기존 답변을 기반으로합니다. 방금 datetime.datetime 형식의 사용자 지정 렌더링을 Oracle의 TO_DATE ()에 추가했습니다.

    데이터베이스에 맞게 코드를 자유롭게 업데이트하십시오.

    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)
  5. ==============================

    5.이를 위해 컴파일 방법을 사용할 수 있습니다. 문서에서 :

    이를 위해 컴파일 방법을 사용할 수 있습니다. 문서에서 :

    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'
  6. ==============================

    6.위에서 언급 한 솔루션은 단순한 쿼리가 아닌 "그냥 작동"하지 않는다는 점을 지적하고자합니다. 필자가 직면 한 한 가지 문제는 pgsql ARRAY가 문제를 일으키는 것과 같이 더 복잡한 유형입니다. 나는 저에게 있어도 pgsql ARRAY를 사용하여 작업 한 해결책을 찾았습니다.

    위에서 언급 한 솔루션은 단순한 쿼리가 아닌 "그냥 작동"하지 않는다는 점을 지적하고자합니다. 필자가 직면 한 한 가지 문제는 pgsql ARRAY가 문제를 일으키는 것과 같이 더 복잡한 유형입니다. 나는 저에게 있어도 pgsql ARRAY를 사용하여 작업 한 해결책을 찾았습니다.

    에서 빌린 : https://gist.github.com/gsakkis/4572159

    링크 된 코드는 이전 버전의 SQLAlchemy를 기반으로합니다. _mapper_zero_or_none 속성이 존재하지 않는다는 오류가 발생합니다. 최신 버전에서 작동하는 업데이트 된 버전이 있습니다. 단순히 _mapper_zero_or_none을 bind로 바꿉니다. 또한 pgsql 배열을 지원합니다.

    # 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)

    중첩 배열의 두 수준으로 테스트되었습니다.

  7. ==============================

    7.디버깅 할 때만 원하는 것을 감안할 때 모든 SQL 쿼리를 기록하려면 echo = True로 SQLAlchemy를 시작할 수 있습니다. 예 :

    디버깅 할 때만 원하는 것을 감안할 때 모든 SQL 쿼리를 기록하려면 echo = True로 SQLAlchemy를 시작할 수 있습니다. 예 :

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

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

    Flask와 함께 사용하면 간단히 설정할 수 있습니다.

    app.config["SQLALCHEMY_ECHO"] = True

    같은 행동을하기 위해서.

    8. from https://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query by cc-by-sa and MIT license

'PYTHON' 카테고리의 다른 글

[PYTHON] seek () 함수는 무엇입니까?  (0) 2018.10.09
[PYTHON] matplotlib에서 y 축 한계 설정  (0) 2018.10.09
[PYTHON] 내 장고 요청에서 내 JSON 데이터는 어디에 있습니까?  (0) 2018.10.09
[PYTHON] matplotlib을 사용하여 두 개의 히스토그램을 동시에 플롯합니다.  (0) 2018.10.09
[PYTHON] Python의 속성에 따라 객체 인스턴스를 비교합니다.  (0) 2018.10.09
,

티스토리툴바