[sqlalchemy] Caching intermediate results in baked queries
Hoi,
We're starting to do some pretty complicated things with the SQLAlchemy
bakery (because we have some pretty complicated queries) and I have
something I'd like to verify.
We have queries that are constructed like so (each letter is a step):
A B C .. M N0
A B C .. M N1
A B C .. M N2
A B C .. M N3
So we have lots of steps and only at the end does something change. Not all
the time, but much more often than the steps A to M. The way the bakery is
set up only the last step is actually cached, meaning that steps A to M get
done each time the last step changes.
However, suppose we could, after step M, do a kind of checkpoint so that at
least that part is done only once. AFAICT it would work like the "spoil"
method, except leave spoil as False. So something like:
class BakedQuery(object):
def checkpoint(self):
_check_point = self._clone()
_check_point._cache_key += ('_query_only', )
self.steps = [_check_point._retrieve_baked_query]
return self
It's not a great name I agree, but the alternatives I can think of are
mark, save, stuff like that.
Anyway, AFAICT this should just work. The assumption is that the query
construction takes significant time, but I think we are sure of that. When
an N4 comes, after executing the checkpoint after M, the last step simply
extracts the cached intermediate step, applies N4 and we're done.
Am I missing something? Is this something that could be considered an
submittable improvement?
Thanks in advance,
Martijn
--
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.
Re: [sqlalchemy] set a query timeout on a per query basis
you can set that then with before_cursor_execute() and then reset it
on after_cursor_execute().
On Tue, Nov 13, 2018 at 12:44 PM Jonathan Vanasco wrote:
>
> In postgres, you can execute:
>
> SET statement_timeout = 6;
>
> at any point. It lasts until the end of the "session", which I believe would
> be the SqlAlchemy connection's lifetime.
>
>
>
>
> On Monday, November 12, 2018 at 6:44:06 PM UTC-5, Mike Bayer wrote:
>>
>>
>> if statement_timeout is accepted only on the "connect" method and not
>> once you have already connected, then it would need to be set for the
>> Engine globally. You would do this using the "connect" event:
>> https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect%20event#sqlalchemy.events.PoolEvents.connect
>>
>> Otherwise, if it can be set on a cursor, you can use
>> execution_options(), which you intercept with a before_cursor_execute
>> event:
>>
>> @event.listens_for(Engine, "before_cursor_execute")
>> def _set_timeout(conn, cursor, stmt, params, context, executemany):
>> timeout = conn._execution_options.get('timeout', None)
>> if timeout:
>>cursor.statement_timeout = timeout
>>
>> query.execution_options() accepts whatever keywords you pass into it
>> and you can get them out inside that event handler (or anywhere you
>> have a Connection).
>
> --
> 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 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.
Re: [sqlalchemy] set a query timeout on a per query basis
In postgres, you can execute:
SET statement_timeout = 6;
at any point. It lasts until the end of the "session", which I believe
would be the SqlAlchemy connection's lifetime.
On Monday, November 12, 2018 at 6:44:06 PM UTC-5, Mike Bayer wrote:
>
>
> if statement_timeout is accepted only on the "connect" method and not
> once you have already connected, then it would need to be set for the
> Engine globally. You would do this using the "connect" event:
>
> https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect%20event#sqlalchemy.events.PoolEvents.connect
>
>
> Otherwise, if it can be set on a cursor, you can use
> execution_options(), which you intercept with a before_cursor_execute
> event:
>
> @event.listens_for(Engine, "before_cursor_execute")
> def _set_timeout(conn, cursor, stmt, params, context, executemany):
> timeout = conn._execution_options.get('timeout', None)
> if timeout:
>cursor.statement_timeout = timeout
>
> query.execution_options() accepts whatever keywords you pass into it
> and you can get them out inside that event handler (or anywhere you
> have a Connection).
>
--
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.
