Hi Mike, Thanks for your reply.
I now have comments activated for my joins and exists and some other functions. I'm still at a loss for how to add comments to an indivdiual column, function, or CASE in the select statment. Would you mind giving me a pointer? Any idea how I can go about monkey patching the base ClauseElement? Thanks and best regards, Matthew On Wed, Oct 4, 2017 at 7:29 PM, Mike Bayer <[email protected]> wrote: > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/BgZx_zvtVvA/unsubscribe. > To unsubscribe from this group and all its topics, 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.
