def compile_query(query): # type: ignore
dialect = postgresql.dialect()
compiled_query = query.compile(dialect=dialect)
params = {}
for k, v in compiled_query.params.items():
if isinstance(v, str):
params[k] = sqlescape(v.encode('utf-8'))
else:
params[k] = sqlescape(v)
return compiled_query.string % 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_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 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 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.iteritems():
if isinstance(v, unicode):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def compile_element(element, dialect):
#statement = query.statement
comp = SQLCompiler(dialect, element)
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):
dialect = query.session.bind.dialect
statement = query.statement
comp = sqlalchemy.sql.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_to_sql_text(query):
'''
This function compile sql object by binding expression's free variable with its params
:param sqlalchemy query object
'''
unbound_sql_code = str(query)
params = query.compile().params
template = ':{name}'
if query.compile().bindtemplate is BIND_TEMPLATES['pyformat']:
template = '%({name})s'
for k, v in params.items():
unbound_sql_code = unbound_sql_code.replace(template.format(name=k), str(sqlescape(v)))
return unbound_sql_code
def compile_query_to_sql_text(query):
'''
This function compile sql object by binding expression's free variable with its params
:param sqlalchemy query object
'''
unbound_sql_code = str(query)
params = query.compile().params
template = ':{name}'
if query.compile().bindtemplate is BIND_TEMPLATES['pyformat']:
template = '%({name})s'
for k, v in params.items():
unbound_sql_code = unbound_sql_code.replace(template.format(name=k),
str(sqlescape(v)))
return unbound_sql_code
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 compile_query(metadata, query):
""" Convert an SQLAlchemy query to an SQL command
SQLAlchemy offers a nice syntax to build SQL queries, but since we
want to extract the data with pandas so as to get a dataframe object,
this helper method is needed to convert the query object to SQL
command string that can be used by pandas.
"""
comp = compiler.SQLCompiler(metadata.bind.dialect, query)
enc = metadata.bind.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 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):
"""
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 query_to_sql(query):
"""
Convert a sqlalchemy query to raw SQL.
https://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression
"""
from psycopg2.extensions import adapt as sqlescape
statement = query.statement.compile(dialect=query.session.bind.dialect)
dialect = query.session.bind.dialect
enc = dialect.encoding
params = {}
for k, v in statement.params.items():
if is_unicode(v):
v = v.encode(enc)
params[k] = sqlescape(v)
return statement.string % 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])
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])
def compile_query(query):
dialect = query.session.bind.dialect
comp = query.statement.compile(dialect=dialect)
# the one below caused errors with ARRAY types
# from sqlalchemy.sql import compiler
# comp = compiler.SQLCompiler(dialect, statement)
# comp.compile()
enc = dialect.encoding
params = {}
# if app.config['DB_TYPE'] == 'MYSQL':
# items = comp.params.items()
# else:
# items = comp.params.iteritems()
for k, v in comp.params.items():
if isinstance(v, str):
v = v.encode(enc)
params[k] = sqlescape(v)
return str(comp.string % params)