Hi Mike, Thanks so much - this is excellent.
Best regards, Matthew On Fri, Oct 27, 2017 at 10:30 AM, Mike Bayer <mike...@zzzcomputing.com> wrote: > On Thu, Oct 26, 2017 at 8:23 PM, Matthew Moisen <mkmoi...@gmail.com> > wrote: > > 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? > > > I added a POC to the wiki to illustrate your original SELECT statement: > > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/CompiledComments > > > > > > 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 <mike...@zzzcomputing.com> > wrote: > >> > >> On Wed, Oct 4, 2017 at 5:57 PM, Matthew Moisen <mkmoi...@gmail.com> > 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 sqlalchemy+unsubscr...@googlegroups.com. > >> > To post to this group, send email to sqlalchemy@googlegroups.com. > >> > 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 > >> sqlalchemy+unsubscr...@googlegroups.com. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> 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 sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > 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 > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
