[sqlalchemy] matches_any: an extension to the Query object, and a HSTORE attribute access property
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. Code aside, if you can think of ways in which we as a company could support SQLAlchemy (bear in mind I am not in charge of the purse strings, but I can make a pitch on your behalf; we are still awaiting the fruits of our donation to the PyPy http://morepypy.blogspot.co.uk/2012/01/py3k-and-numpy-first-stage-thanks-to.htmlguys :).** Then do let me know. I don't check this email account all that regularly but my work address is my firstname.lastname at cantabcapital dot com Keep up the good work! ** [shameless plug] Or if you are keen, enthusiastic, mostly competent, and looking for a well paid job where you get to do loads of Python SQLAlchemy -- 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.
Re: [sqlalchemy] matches_any: an extension to the Query object, and a HSTORE attribute access property
On Tue, Jan 7, 2014 at 11:14 AM, Philip Scott safetyfirstp...@gmail.comwrote: 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.
Re: [sqlalchemy] matches_any: an extension to the Query object, and a HSTORE attribute access property
On Jan 7, 2014, at 2:14 PM, Philip Scott safetyfirstp...@gmail.com wrote: 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. that’s a very nice pattern! It looks like you could do that strictly with Python descriptors, such as subclassing @hybrid_property, no ? Each property is just an expression against the “data” column, either python-side (lazily deserialize a key) or expression-side (do a postgresql expression for a certain key), and you can also trigger the mutable.is_changed() flag within the setter. I don’t think you’d need events. Code aside, if you can think of ways in which we as a company could support SQLAlchemy (bear in mind I am not in charge of the purse strings, but I can make a pitch on your behalf; we are still awaiting the fruits of our donation to the PyPy guys :).** Then do let me know. I don't check this email account all that regularly but my work address is my firstname.lastname at cantabcapital dot com Thanks ! I think what I’m usually looking for are people resources. Doc fixes and pull requests and such. If I could organize some significant batch of work as something that would work under a “grant” model, I’ll let you know. I haven’t figured out how to work that way, yet. signature.asc Description: Message signed with OpenPGP using GPGMail
