On Sep 23, 2013, at 9:40 AM, Philip Scott <safetyfirstp...@gmail.com> wrote:
> I went though the exact same process of discovery that you did Jonathan :) It
> does work perfectly but does not get rendered properly when printing out the
> queries (possibly even when I set echo=True on the connection, if I remember
> correctly)
it will definitely show the right thing for echo=True, that's what's being sent
to the database.
DISTINCT ON is postgresql specific so a string repr wont show it unless you
pass a PG dialect:
from sqlalchemy import select, literal
s = select([literal(1)]).distinct(literal(2))
print s
from sqlalchemy.dialects import postgresql
print s.compile(dialect=postgresql.dialect())
>
>
> On Sun, Sep 22, 2013 at 5:44 PM, Jonathan Vanasco <jonat...@findmeon.com>
> wrote:
> ah ha! yes!
>
> that is right.
>
> query = select( (these,columns,) ).distinct( this.column )
>
> this was an even tricker problem... and I might have been executing correct
> queries last night without realizing it.
>
> i just noticed that i was getting a correct query in my database, while I was
> seeing the wrong query on screen.
>
> sqlalchemy didn't know that that 'core' commands I was using were for
> postgresql, so it rendered the query not using that dialect.
>
> when i actually did query the database, it was compiling with the right
> dialect :
>
> _query_EXT = dbSession.query( model.Table.data )
> _query_INT = dbSession.query( model.Table.data )
> _slurped = sqlalchemy.union( _query_EXT , _query_INT )
> _slurped = sqlalchemy.sql.expression.alias( _slurped , name='slurped')
> _deduped = sqlalchemy.select(\
> (\
> _slurped.c.object_id.label('object_id') ,
> _slurped.c.event_timestamp.label('event_timestamp')
> ),
> )\
> .distinct( _slurped.c.object_id )\
> .order_by(\
> _slurped.c.object_id.desc() ,
> _slurped.c.event_timestamp.desc()
> )
> _deduped = sqlalchemy.sql.expression.alias( _deduped , name='deduped')
> _culled = sqlalchemy.select( (_deduped.c.object_id,) )\
> .order_by(\
> _deduped.c.event_timestamp.desc()
> )
> _query = _culled
>
>
> # this executes a "DISTINCT ON ( slurped.object_id ) slurped.object_id ,
> event_timestamp"
> yay = dbSession.execute( _query )
>
>
> # this renders a "DISTINCT slurped.object_id , event_timestamp"
> nay = str( _query )
>
>
>
>
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
