def compile_query(query):
from sqlalchemy.sql import compiler
# try:
# from MySQLdb.converters import conversions, escape
# except ImportError:
# return 'no sql conversion available'
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
enc = dialect.encoding
params = []
for k in comp.positiontup:
v = comp.params[k]
if isinstance(v, unicode):
v = v.encode(enc)
params.append(v)
# params.append(
# escape(v, conversions)
# )
comp = comp.string.encode(enc)
comp = comp.replace('?', '%s')
txt = (comp % tuple(params)).decode(enc)
return txt
def compile_query(query):
""" 把一个 sqlalchemy query object 编译成mysql风格的 sql 语句 """
from sqlalchemy.sql import compiler
from MySQLdb.converters import conversions, escape
from sqlalchemy.dialects import mysql as mysql_dialetct
dialect = mysql_dialetct.dialect()
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
comp_params = comp.params
params = []
for k in comp.positiontup:
v = comp_params[k]
# python2, escape(u'123', conversions) is '123', should be "'123'"
# escape('123', conversions) is ok
if six.PY2 and isinstance(v, unicode):
v = v.encode(enc)
v = escape(v, conversions)
# python3, escape return bytes
if six.PY3 and isinstance(v, bytes):
v = v.decode(enc)
params.append(v)
return (comp.string % tuple(params))
def get_sqlaclchemy_query_text(query):
"""
Печатает текст запроса из SQLAlchemy
Реализация для PostgreSQL
"""
from sqlalchemy.sql import compiler
# from psycopg2.extensions import adapt as sqlescape
# from nemesis.systemwide import db
# conn = db.session.connection
# sqlescape = conn().escape
# from pymysql import Connection
# sqlescape = Connection().escape
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
# params[k] = sqlescape(v)
params[k] = v
return (comp.string.encode(enc) % params).decode(enc)
def compile_query(query):
'''
주어진 SQLAlchemy Query 풀버전을 문자열로 돌려준다.
>>> query = model.Session().query(model.Board)
>>> print compile_query(query)
u'SELECT boards.id, boards.category_id, boards.board_name, boards.board_alias, boards.board_description, boards.deleted, boards.read_only, boards.hide, boards."order", boards.type, boards.to_read_level, boards.to_write_level \nFROM boards'
@type query: sqlalchemy.orm.query.Query
@param query: 들여다볼 쿼리 객체
@rtype: str
@return: 쿼리문 본문
'''
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = []
for key in comp.positiontup:
value = comp.params[key]
if isinstance(value, unicode):
value = value.encode(enc)
params.append(escape(value, conversions))
return (comp.string.encode(enc) % tuple(params)).decode(enc)
def compile_query(query):
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
params = dict()
for k, v in comp.params.items():
params[k] = "'%s'" % str(v)
return str(query.statement.compile(dialect=postgresql.dialect())) % params
def compile_query(query):
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.items():
params[k] = sqlescape(v)
return comp.string % params
def compile_statement(statement):
dialect = postgresql.dialect()
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def dump_query(query):
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in list(comp.params.items()):
if isinstance(v, str):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def compile_query(query):
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = []
for k in comp.positiontup:
v = comp.params[k]
if isinstance(v, unicode):
v = v.encode(enc)
params.append(escape(v, conversions))
return (comp.string.encode(enc) % tuple(params)).decode(enc)
def compile_query(self, query=None, dialect=postgresql.dialect()):
# dialect = query.session.bind.dialect
if query is None: query = self.query
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def _compile_query(self, query):
"""
Gets String representation of an SQLAlchemy query.
"""
from sqlalchemy.sql import compiler
from psycopg2.extensions import adapt as sqlescape
# or use the appropiate escape function from your db driver
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
enc = dialect.encoding
params = {}
for k, v in comp.params.items():
params[k] = sqlescape(v)
return (comp.string % params)
def _query_to_sql(q):
r"""SQL as string
:param q: ``sqlalchemy.orm.query``
:return: SQL as string
"""
dialect = q.session.bind.dialect
comp = compiler.SQLCompiler(dialect, q.statement)
comp.compile()
params = {}
if hasattr(comp.params, "items"):
_items = comp.params.items
else:
_items = comp.params.iteritems
return comp.string
def compile_query(query):
dialect = mysql_dialetct.dialect()
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
comp_params = comp.params
params = []
for k in comp.positiontup:
v = comp_params[k]
if six.PY2 and isinstance(
v, six.string_types) and not isinstance(v, six.text_type):
v = v.decode("utf8")
v = escape_item(v, conversions, encoders)
params.append(v)
return (comp.string % tuple(params))
def compile_query(query):
"""
Compile sqlalchemy query object to regular SQL with escaped values
@param query: sqlalchemy query object
@return: string
"""
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
enc = dialect.encoding
params = []
for k in comp.positiontup:
v = comp.params[k]
if isinstance(v, unicode):
v = v.encode(enc)
params.append(escape(v, conversions))
return (comp.string.encode(enc) % tuple(params)).decode(enc)
def visit_sa_query(self, node):
from sqlalchemy.sql import compiler
from psycopg2.extensions import adapt as sqlescape
session = sa.orm.session.Session(node.db)
query = sa.orm.query.Query(node.query, session)
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
comp = node.query.compile(node.db)
enc = dialect.encoding
params = {}
for k,v in comp.params.items():
params[k] = sqlescape(str(v))
result = comp.string.format(*params)
print(sqlparse.format(result, reindent=True, keyword_case='upper'))
print(";")
def compile_query(query):
"""
Reconstructs the raw SQL query from an SQLAlchemy query object.
:param query: SQLAlchemy query objext
:return: The raw SQL query as a string.
"""
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def compile_query(query):
""" 把一个 sqlalchemy query object 编译成mysql风格的 sql 语句 """
from sqlalchemy.sql import compiler
from sqlalchemy.dialects import mysql as mysql_dialetct
from pymysql.converters import conversions, escape_item, encoders
dialect = mysql_dialetct.dialect()
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
comp_params = comp.params
params = []
for k in comp.positiontup:
v = comp_params[k]
if six.PY2 and isinstance(v, six.string_types) and not isinstance(v, six.text_type):
v = v.decode("utf8")
v = escape_item(v, conversions, encoders)
params.append(v)
return (comp.string % tuple(params))
def compile_query(query):
"""Hack function to get sql query with params"""
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
params[k] = sqlescape(v)
# XXX: hack to remove ST_AsBinary put by geoalchemy, it breaks mapnik query
# :(((
str_query = (comp.string.encode(enc) % params).decode(enc)
m = re.search('ST_AsBinary\(([\w\.]*)\)', str_query)
if len(m.groups()) > 1:
# the trick does not handle this case
raise
return str_query.replace(m.group(), m.groups()[0])
