On Wed, May 29, 2019, at 6:22 AM, Chris Wilson wrote: > Hi all, > > Thanks for the replies! Sorry, perhaps I wasn't clear, this is just a minimal > example. We are actually storing serialized objects in a column, which can be > e.g. dicts or lists of (dehydrated) SQLAlchemy objects, numpy arrays, etc. > It's much faster to store (both read and write) a complex structure > serialized into a single column than to split it across many tables. > Generally this works fine for us, but I just discovered this unusual case, > and couldn't see how to solve it.
Note that we suggested approaches that work in conjunction with the serialized approach you gave, the @property approach and the mapper.load() event approach. However, if you are storing the fully serialized object in the column, like the whole Cat object, you don't need to emit a SQL query to restore it, for caching objects in serialized form you'd want to merge() it back into the Session with load=False so that no SQL is emitted. You still need your Session though and of course, using a threadlocal variable is the best way to make that happen right now without changing the type API. If you are in fact looking to emit a SQL query for every occurrence of a value in a row, you will lose any performance gains you're getting here. If you get 500 rows back and there are three of these columns in each row, that's 1500 additional SQL queries, and ORM-level object queries in particular are quite slow. The @property approach suggested would mitigate this by only emitting a SQL query when called upon. I would advise that any architecture that is taken on in the name of performance be measured, anytime I do performance work I use cProfile to validate and measure the positive effect of the change. > > If there were pre-load hooks as well as post-load, then we could set a global > variable to object_session(parent_object) for the duration of the load. You shouldn't need "object_session(parent_object)", there's only one Session in play at a time within a thread so just assign the Session to a thread local variable. The best hook to use here is simply the transaction-level hooks to set the current Session onto a global thread-local variable. The ones that are SessionTransaction level should work well: https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=after_begin#sqlalchemy.orm.events.SessionEvents.after_transaction_create https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=after_begin#sqlalchemy.orm.events.SessionEvents.after_transaction_end if you have multiple sessions per thread at a time, like an extra Session used for logging, the above events can swap out the previous Session already present and then restore it, to handle nesting of session contexts. > > > Alternatively, if the state or context was passed to the TypeDecorator then > we could use it to get the session. The context available is the ExecutionContext, however this isn't passed to the TypeEngine bind/result processor methods right now. That might not be a bad idea in the future but for the moment would require a major breaking API change that cannot be made quickly or trivially. An example of passing information between a Session and the execution-level context is at https://github.com/sqlalchemy/sqlalchemy/wiki/SessionModifiedSQL but this doesn't give you a way to get inside the TypeDecorator methods without using a global threadlocal. If we did have the ExecutionContext passed to TypeEngine processors, this example would be part of how you'd be using it. But we don't, so I think the threadlocal Session approach above should be very straightforward. > > > > > > But right now it appears that there is no hook that does what we want to do. > Please would you accept my request to implement something like this? > > Thanks, Chris. > > On Tuesday, 28 May 2019 23:42:59 UTC+1, Jonathan Vanasco wrote: >> >> >> On Tuesday, May 28, 2019 at 4:35:32 PM UTC-4, Mike Bayer wrote: >>> >>> additionally, running SQL inside of a TypeDecorator is not the intended >>> usage, as well as using ORM features inside of a TypeDecorator is also not >>> the intended usage. >> >> >> thanks, mike. I was 99.99% sure that was the case, but didn't want to risk >> bring wrong. > > -- > 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. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/7850da06-e4a1-4864-8b92-26a817041c6f%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/7850da06-e4a1-4864-8b92-26a817041c6f%40googlegroups.com?utm_medium=email&utm_source=footer>. > 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/cfa2c74c-286d-49ac-9a00-9b9f915c7c87%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
