python调试技巧：数据库调试，转换sqlalchemy中的sql语句

在Python代码中对数据查询使用sqlalchemy

但是在对代码进行检查调试的时候，需要对将查询语句转换成纯sql语句，放入数据库中查询调试

查询数据为query_str = session.query(User).filter(User.id == 1)时

print query_str

结果为

    SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email
    FROM users

    WHERE users.id = :id_1

打印的结果无法将filter(User.id == 1)中的1进行转换，需要手工修改

现添加函数literalquery可将查询语句直接转换成sql语句

代码如下：

# -*- coding: utf-8 -*-
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,
    }


# 打印执行的sql语句，print(literalquery(query))
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

if __name__ == "__main__":
    from flask import Flask
    from sqlalchemy import Column, Integer, String
    from sqlalchemy import create_engine
    from sqlalchemy.orm import Session
    from sqlalchemy.ext.declarative import declarative_base

    app =  Flask(__name__)

    Base = declarative_base()

    # 定义ORM
    class User(Base):
        __tablename__ = 'users'

        id = Column(Integer, primary_key=True)
        name = Column(String(50), unique=True)
        email = Column(String(120), unique=True)

        def __init__(self, name=None, email=None):
            self.name = name
            self.email = email

        def __repr__(self):
            return '<User %r>' % (self.name)


    engine = create_engine('sqlite:///./sqlalchemy1.db')
    global session
    session = Session(engine)

    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)

    admin = User('admin', 'admin@example.com')
    session.add(admin)

    guestes = [User('guest1', 'guest1@example.com'),
               User('guest2', 'guest2@example.com'),
               User('guest3', 'guest3@example.com'),
               User('guest4', 'guest4@example.com')]
    session.add_all(guestes)
    session.commit()


    query_str = session.query(User).filter(User.id == 1)

  print query_str
  # 打印结果
  # SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email   
  # FROM users 
  # WHERE users.id = :id_1

print literalquery(query_str) # 打印结果 # SELECT users.id, users.name, users.email # FROM users # WHERE users.id = 1