On Tue, Jan 7, 2014 at 11:14 AM, Philip Scott <safetyfirstp...@gmail.com>wrote:
> Hi folks, > > SQLAlchemy is at the heart of what we do at work (UK based hedge fund); so > first of all a big thank you; I am not quite sure where we'd be without > you. We would like to give back to the community as much as we can (I tried > to get some of the developers on the company xmas present list this year > but was too late.. cross your fingers for next year). > > We have extended SQLAlchemy in a few places, though it is quite > intermingled with our domain specific stuff I keep an eye out for little > snippets that might be useful to others. So here's a trivial one; take it > or leave it (and feel free to think of a better name). Knowing my luck it > already exists; though I have looked hard through the docs! > > class QueryEnhanced(Query): > ''' Add a few extra bells and whistles to the standard Query object ''' > def matches_any(self): > ''' Returns true if your query would return 1 or more rows; false > otherwise. > The following two statements ask the same question; but > matches_any is _much_ quicker on large tables: > my_query.matches_any() > my_query.count() != 0 > ''' > return self.session.scalar(select([self.exists()])) > > The other bit of technology we have that could be unpicked without _too_ > much trouble is a sort of reverse CompositeProperty; many attributes of > different types, including collections, out of one HSTORE column (with a > sort of side-loaded instrumentation for mutation tracking that I think > could have been done in a more idiosyncratic way). > > Paraphrasing a bit but you can do things like: > > class Animal(Base): > data = Column(MutableDict.as_mutable(HSTORE)) > > colour = HsProperty(data, String) > legs = HsProperty(data, Integer) > discovered = HsProperty(data, Date) > fun_facts = HsProperty(data, JSONEncoded(list)) > > 'colour', 'legs', 'discovered', and 'fun_facts' end up as keys in the > HSTORE and the values are strings, integers, dates and lists on the python > side but stored as strings in the HSTORE such a way that they can be > CAST-ed in a server query [where possible]: > > session().query(Animal).filter(Animal.legs > 2) > > and get a query like > > SELECT ... FROM animal WHERE CAST(animal.data -> legs AS INTEGER) > 2 > > You can also put an arbitrary JSONEncodable object in there too. > Collections get converted to Mutable counterparts for change-tracking. > > In many ways it is similar to ColumnProperty except that - the properties > are writable (and when written only trigger the relevant bits of the hstore > to be updated). Also on object instances the values in HsProperties are > fetched as part of the query; we lazily de-serialise them directly from the > hstore dictionary. > > Before spend a couple of days removing our corporate clutter from that, > getting permission to license it etc.. and posting either as a patch or > extension I thought I would see if there is any interest (or if someone has > already done it better?). It's implemented as a custom metaclass right now, > but I think I might be able to do it fully with events. > I would be very interested in this work. At my org we have a subset of the same idea that we're depending upon, but it's tied to an ancient SQLAlchemy version and we never took it all the way into the query space like that. That looks absolutely fabulous! -- 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.
