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.html>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
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
