How to add comments inside a big query using Classical SQLAlchemy?

[Matthew Moisen]

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})

with open(file_name, 'w') as f:
    f.write(sql) 

Thanks and best regards,

Matthew Moisen

[Mike Bayer]

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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Matthew Moisen]

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

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

[Mike Bayer]

On Thu, Oct 26, 2017 at 8:23 PM, Matthew Moisen <mkmo...@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

>> > email to sqlalchemy+...@googlegroups.com.

>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.

>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Matthew Moisen]

Hi Mike,

Thanks so much - this is excellent.

Best regards,

Matthew

>> > email to sqlalchemy+unsubscribe@googlegroups.com.

>> > To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.