SQLAlchemy doubles percent sign (%) #56
Comments
|
If the query contains |
|
If you set the If formatting is done regardless of the query parameters as follows, the |
|
Argh! Of course that was too easy to work! My use case is that I want to be able to copy sql statements that work in the Athena console and paste them directly into a Jupyter notebook, where the %%sql magic can execute them against Athena unmodified. Users can't be bothered to reformat the text with double percents. Could it be that the real problem is that the visit_textclause() function in the default SQLCompiler class from sqlalchemy/sql/compiler.py calls self.post_process_text(textclause.text) -- which doubles percents -- REGARDLESS of whether there are any bind parameters or not? This behavior can be overridden and corrected by your AthenaCompiler class in pyathena/sqlalchemy_athena.py. With the following changes only (the previous change I suggested is not needed): from sqlalchemy.sql.compiler import BIND_PARAMS_ESC, BIND_PARAMS
class AthenaCompiler(SQLCompiler):
"""PrestoCompiler
https://github.com/dropbox/PyHive/blob/master/pyhive/sqlalchemy_presto.py"""
def visit_char_length_func(self, fn, **kw):
return 'length{0}'.format(self.function_argspec(fn, **kw))
def visit_textclause(self, textclause, **kw):
def do_bindparam(m):
name = m.group(1)
if name in textclause._bindparams:
return self.process(textclause._bindparams[name], **kw)
else:
return self.bindparam_string(name, **kw)
if not self.stack:
self.isplaintext = True
if len(textclause._bindparams) == 0:
return textclause.text
else:
# un-escape any \:params
return BIND_PARAMS_ESC.sub(
lambda m: m.group(1),
BIND_PARAMS.sub(
do_bindparam,
self.post_process_text(textclause.text))
)I now get this: >>> import sqlalchemy
>>> engine = sqlalchemy.create_engine(....)
>>> session = engine.connect()
>>> session.execute(sqlalchemy.text("select date_parse('20191030', '%Y%m%d' )")).fetchall()
[(datetime.datetime(2019, 10, 30, 0, 0),)]
>>> session.execute(sqlalchemy.text("select date_parse('20191030', '%Y%m%d' ), 'cat'")).fetchall()
[(datetime.datetime(2019, 10, 30, 0, 0), 'cat')]
>>> session.execute(sqlalchemy.text("select date_parse('20191030', '%Y%m%d' ), :word"), {'word':"cat"}).fetchall()
[(datetime.datetime(2019, 10, 30, 0, 0), 'cat')]Thank you for your time in looking at this. |
|
It is the following method to override. It seems just to add the following conditions. It feels good. 👍 |
|
I fixed it with the following pull request. And a few test cases are added. Please check it. |
|
Thank you so much! It works well for me! |
…rcent_character_in_sqla Fix double escaping of percent character in SQLAlchemy (fix #56)
(But it can be fixed by setting self._double_percents = False in AthenaIdentifierPreparer
__init__)Broken:
(See how it has doubled the percent signs in the last line of the traceback?)
Fix by changing sqlalchemy_athena.py to have this:
Fixed:
The text was updated successfully, but these errors were encountered: