Chris Withers wrote:
> Hi All,
>
> I have a few monster queries like this:
>
> query = session.query(
> Blah1.name.label('blah1'),
> Blah2.name.label('blah2'),
> blah3.name.label('blah4'),
> Blah5.name.label('blah5'),
> Blah6.name.label('blah6'),
> func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty)],
> else_=Blah10.qty*-1)).label('blah11'),
>
> func.sum(case([(Blah7.blah8_blah9=='Blah8',Blah10.qty*blah13.price)],
> else_=Blah10.qty*blah13.price*-1)).label('blah12')).\
> join(Blah1.participents,
> Participation.blah5,
> Blah5.blah10s,
> Blah10.fill,
> blah13.blah7,
> Blah7.blah6,
> Blah7.blah12,
> Blah12.blah7_idea,
> Blah2.blah4).\
> filter(and_(Blah1.id==self.id,
> Participation.valid_from <= on_date,
> or_(Participation.valid_to > on_date,
> Participation.valid_to == None))).\
> group_by(Blah6).\
> order_by(Blah6.name)
>
> ...apologies for the obfuscation.
>
> Now, is there any way I can "pre-cook" this (eg: at module-level) such
> that I can later just plug in self.id and on_date, bind to a session and
> call .all() on it?
>
> It seems a bit wasteful to do all the SQL generation on every query when
> it's almost all identical all the time...
easiest way, call your Query from a def. most of the work in SQL
generation isn't internally "cached" anyway so you aren't saving much by
having the same Query lying around.
second way, you can use bindparam() for the binds as others have
mentioned, and then params() to set the values as needed, but the missing
link is that you want the Query against your own particular session at the
moment. I haven't yet gotten the chance to add a "with_session()" method
to Query but you can do this easily enough yourself:
from sqlalchemy.orm.query import Query, _generative
class MyQuery(Query):
@_generative
def with_session(self, session):
self.session = session
Session = sessionmaker(query_cls=MyQuery)
so have your query lying around:
q = Session().query(...).filter(...)
then use it :
print q.with_session(my_session).params(foo='bar').all()
>
> cheers,
>
> Chris
>
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting
> - http://www.simplistix.co.uk
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
