On Wed, Oct 4, 2017 at 5:57 PM, Matthew Moisen <[email protected]> wrote:
> I'm using Classic SQLAlchemy to create SQL statements, which are later
> formatted with sqlparse and stored in a file. I'm not executing the SQL
> through SQLAlchemy.
>
>
> Some of the queries have complexities that would benefit from a comments. Is
> there any way to get SQLAlchemy to output a query like the following?
>
>
> -- Comment explaining the query
> SELECT foo,
> -- comment explaining the convoluted case statement
> CASE WHEN .. END as complicated_case,
> -- comment exaplaining the convoluted window function
> ROW_NUMBER() OVER (PARTITION BY ..., ORDER BY ...) as complicated_row_num
>
> FROM bar JOIN (
> -- Comment explaining subquery and join
> SELECT ...
> ) WHERE 1=1
> -- comment explaining the purpose of the EXISTS clause
> AND EXISTS (SELECT ...)
>
> Reading through this user group, I saw a few posts related to comments and
> the ORM. The recommended solution was this link:
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL
>
> However for my use case, I am not executing any of the sql. I'm basically
> doing things like this:
>
> sel = select([...])
> sql = str(sel.compile(dialect=oracle.dialect(),
> compile_kwargs={'literal_binds': True})
you would need to create custom compilation functions for all the
constructs you're looking to add comments with, and additionally tack
on a comment to each one manually:
join = foo.join(bar)
join.comment = "some comment"
then you'd need to compile for Join:
from sqlalchemy.ext.compiler import compiles
@compiles(Join)
def _comment_join(element, compiler, **kw):
comment = getattr(element, 'comment')
if comment:
text = "-- %s" % comment
else:
text = ""
return text + compiler.visit_join(element, **kw)
a bit tedious but it would be a start and can perhaps be generalized a
bit once you get it going for many constructs. You can in theory
monkeypatch a comment() method onto the base ClauseElement construct
to.
see http://docs.sqlalchemy.org/en/latest/core/compiler.html .
>
> with open(file_name, 'w') as f:
> f.write(sql)
>
> Thanks and best regards,
>
> Matthew Moisen
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
