I'm writing SA classes against a very unnormalized legacy database. Some of
our columns comprise several distinct values, and I wanted to take this
opportunity to split those into separate properties. I also wanted to be able
to search on the values in those properties, and here's my solution to it.
################################################
from sqlalchemy import func
from sqlalchemy.orm.properties import ColumnProperty
def substrcomparatormaker(column, i, j=None):
"""Return a comparator that tests for the substring of 'column'
from 'i' to 'j', as specified with Python slice values. This
means setting the start column to i + 1 because Python is 0-based
and SQL is 1-based, and setting the length to j - i.
To search for columns where table.foo[0:3] has a certain value, use:
@comparable_using(substrcomparatormaker(foo, 0, 3)) # => substr(1, 3)
To search on table.foo[4:6], use:
@comparable_using(substrcomparatormaker(foo, 4, 6)) # => substr(5, 2)
"""
class SubstrComparator(ColumnProperty.Comparator):
"""Subclass of Comparator that looks at a slice of a column"""
def __eq__(self, other):
"""Compare the substr of a column to the given value"""
if j is None:
return func.substr(column, i + 1) == other
else:
return func.substr(column, i + 1, j - i) == other
return SubstrComparator
################################################
Here's how I use it in production:
class Invoice(Base):
__tablename__ = 'invoice'
typeofinv = Column(String(10))
@comparable_using(substrcomparatormaker(typeofinv, 0, 3))
@property
def shiptype(self):
"""The shipment's type"""
return self.typeofinv[:3]
@comparable_using(substrcomparatormaker(typeofinv, 3, 6))
@property
def shiptariff(self):
"""The shipment's tariff"""
return self.typeofinv[3:6]
@comparable_using(substrcomparatormaker(typeofinv, 6))
@property
def shipmode(self):
"""The shipment's mode"""
return self.typeofinv[6:]
invoice = session.query(Invoice).filter(Invoice.invid==2380724)
invoice = invoice.filter(Invoice.shiptype=='DPS')
invoice = invoice.filter(Invoice.shiptariff=='DOM')
invoice = invoice.filter(Invoice.shipmode=='HHG')
The point of mangling the arguments to substrcomparatormaker is so they can
have the same start and end values as the Python slices that they're mapping
to. I'd rather write Python in Python than SQL in Python.
For bonus points, I guess I could've made a function that accepts (column, i,
j) and returns the entire property definition, but this seemed useful in the
general case. For example, I might've wanted something like:
@comparable_using(substrcomparatormaker(typeofinv, 0, 3))
@property
def shiptype(self):
"""The shipment's type"""
return {'FOO': 'Foo invoice', 'BAR': 'Bar invoice'}[self.typeofinv[:3]]
--
Kirk Strauser
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
