Combining the existing recipesThis works in python 2 and 3 and is a bit cleaner than before, but using inheritance rather than monkey-patching:requires SA>=1.0.
deffrom literalquery(statement,sqlalchemy.engine.default dialect=None):
import DefaultDialect
from sqlalchemy.sql.sqltypes import """GenerateString, anDateTime, SQLNullType
# expressionpython2/3 stringcompatible.
PY3 with= boundstr parametersis renderednot inlinebytes
text = forstr theif givenPY3 SQLAlchemyelse statement.
unicode
WARNING: Thisint_type method= ofint escapingif isPY3 insecure,else incomplete(int, and for debugginglong)
purposes only. Executingstr_type SQL= statementsstr withif inline-renderedPY3 userelse values(str, isunicode)
extremely
class insecure.StringLiteral(String):
"""
"""Teach SA how to importliteralize sqlalchemyvarious things.orm"""
ifdef isinstanceliteral_processor(statementself, sqlalchemy.orm.Querydialect):
ifsuper_processor dialect= issuper(StringLiteral, None:self).literal_processor(dialect)
dialect =def statement.session.get_bindprocess(value):
if statement._mapper_zero_or_noneisinstance(value, int_type):
).dialect
statement =return statement.statementtext(value)
if dialect is None:
dialectif =not getattrisinstance(statement.bind, 'dialect'value, Nonestr_type)
if dialect is None:
from sqlalchemy.dialects import mysql
dialect = mysql.dialect()
Compilervalue = type(statement._compilertext(dialect)value)
class LiteralCompiler(Compiler):
visit_bindparamresult = Compiler.render_literal_bindparam
def render_literal_valuesuper_processor(self, value, type_):
if isinstance(value, (Decimalresult, long)bytes):
returnresult str= result.decode(valuedialect.encoding)
elif isinstance(value,return datetime):result
return repr(strprocess
class LiteralDialect(value)DefaultDialect):
colspecs = {
else: # fallback
prevent various encoding explosions
String: StringLiteral,
value = super(LiteralCompiler, self).render_literal_value(
# teach SA about how to literalize a datetime
value,DateTime: type_StringLiteral,
# don't format py2 long integers to )NULL
NullType: StringLiteral,
if}
def isinstanceliteralquery(value, unicodestatement):
"""NOTE: This is entirely insecure. DO NOT execute the resulting strings."""
returnimport valuesqlalchemy.encode('UTF-8')orm
if isinstance(statement, sqlalchemy.orm.Query):
statement = else:statement.statement
return statement.compile(
dialect=LiteralDialect(),
return value
compile_kwargs={'literal_binds': True},
return LiteralCompiler(dialect, statement).string
# coding: UTF-8
from sqlalchemy.dialects import mysql
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'),
long(1234)10 ** 20, # a long integer
)
statement = select([mytable]).where(mytable.c.mycol.in_(values)).limit(1)
print (literalquery(statement))
if __name__ == '__main__':
test()
Gives this output: (tested in python 2.7 and 3.4)
SELECT mytable.mycol
FROM mytable
WHERE mytable.mycol IN (5, 'snowman: ☃', 'UTF-8 snowman: ☃',
'2014 '2015-0806-0124 1118:1909:5429.514764'042517', 3.14159, 1234100000000000000000000)
LIMIT 1