Column property vs. Python (class) property for calculated columns
bojanb
amount which is just qty * price.
I can define it as a column_property in a mapper which makes it
available in all database operations, eg. I can write session.query
(myclass).filter_by(amount>1000) which will create the correct WHERE
clause "qty*price>1000".
However, the attribute is None until the object is flushed to the
database, ie.
myclass.qty = 2
myclass.price = 500
print myclass.amount
will return None if flush was not issued.
If I use a Python property function to define it, it will be
immediately available (and always up to date); however, I cannot query
on a Python property.
Is there a way to have best of both worlds? Or should I just define
the calculated property twice, eg. Python property named 'amount' and
a column_property named 'db_amount' and then work with the first but
use the second for querying?
Michael Bayer
> Let's say I want to have a simple calculated property in my class, eg.
> amount which is just qty * price.
>
> I can define it as a column_property in a mapper which makes it
> available in all database operations, eg. I can write session.query
> (myclass).filter_by(amount>1000) which will create the correct WHERE
> clause "qty*price>1000".
>
> However, the attribute is None until the object is flushed to the
> database, ie.
> myclass.qty = 2
> myclass.price = 500
> print myclass.amount
>
> will return None if flush was not issued.
>
> If I use a Python property function to define it, it will be
> immediately available (and always up to date); however, I cannot query
> on a Python property.
ultimately the value of this attribute is derived from other attributes
which are mapped. So there is a very simple and clever way to get both
in that case which you can see if you look at
examples/derived_attributes/attributes.py.
>
> Is there a way to have best of both worlds? Or should I just define
> the calculated property twice, eg. Python property named 'amount' and
> a column_property named 'db_amount' and then work with the first but
> use the second for querying?
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
bojanb
it does. I guess that's the "Alchemy" in "SQLAlchemy" :-)
However, I have issues with the difference in NULL value semantics
between Python and SQL. Ie. if a calculated column is defined via a
column_property as price*amount, then the result will be NULL if any
of the values is NULL. However, in Python, None*something throws a
TypeError, so the hybrid_property getter function needs to be filled
with lots of IFs.
Also, this solution can't be used for date calculations, as timedelta
objects are needed. So I guess I will stick with a mix of Python
properties and column_properties.
avdd
class argument:
Interval.length -> SQL Expression
Interval.contains(arg) -> SQLExpression
and operates normally on instances, ie, just runs the function.
On Jan 15, 11:41 pm, bojanb <boj...@gmail.com> wrote:
> Thanks Mike. I must admit I don't understand why that code works, but
> it does. I guess that's the "Alchemy" in "SQLAlchemy" :-)
>
> However, I have issues with the difference in NULL value semantics
> between Python and SQL. Ie. if a calculated column is defined via a
> column_property as price*amount, then the result will be NULL if any
> of the values is NULL. However, in Python, None*something throws a
> TypeError, so the hybrid_property getter function needs to be filled
> with lots of IFs.
When called as class properties, the descriptors always generate
SQL expressions as above.
When called as instance properties, it just calls your function and
you can
do what you like with None values, e.g.:
@hybrid_property
def length(self):
return self.thing is not None and self.thing or None
> Also, this solution can't be used for date calculations, as timedelta
> objects are needed. So I guess I will stick with a mix of Python
The example works in postgresql with timestamps and intervals/
timedeltas. ;-)
a.
bojanb
> > between Python and SQL. Ie. if a calculated column is defined via a
> > column_property as price*amount, then the result will be NULL if any
> > of the values is NULL. However, in Python, None*something throws a
> > TypeError, so the hybrid_property getter function needs to be filled
> > with lots of IFs.
>
> When called as class properties, the descriptors always generate
> SQL expressions as above.
>
> When called as instance properties, it just calls your function and
> you can
> do what you like with None values, e.g.:
>
> @hybrid_property
> def length(self):
> return self.thing is not None and self.thing or None
That's what I did, I just wasn't sure it's the right way to do it.
> > Also, this solution can't be used for date calculations, as timedelta
> > objects are needed. So I guess I will stick with a mix of Python
>
> The example works in postgresql with timestamps and intervals/
> timedeltas. ;-)
>
> a.
Then I'm doing something terribly wrong. If I define:
due_date = hybrid_property(lambda self: self.invoce_date +
self.payment_days)
When querying with session.query(Invoice).filter
(Invoice.due_date<date.today()).all(), I get the following error:
TypeError: unsupported type for timedelta days component:
InstrumentedAttribute
This is with PostgreSQL and SQLAlchemy 0.5.8.
avdd
"""
from datetime import datetime, timedelta, date
from sqlalchemy import MetaData, Table, Column, DateTime, Date,
Interval
from sqlalchemy.orm import mapper, create_session
metadata = MetaData('postgresql:///avdd')
interval_table1 = Table('period1', metadata,
Column('start', DateTime, primary_key=True),
Column('end', DateTime, primary_key=True))
interval_table2 = Table('period2', metadata,
Column('start', DateTime, primary_key=True),
Column('length', Interval, primary_key=True))
metadata.create_all()
class BasePeriod(object):
@hybrid
def contains(self, instant):
return (self.start <= instant) & (instant < self.end)
@hybrid
def intersects(self, other):
return (self.start < other.end) & (self.end > other.start)
def __repr__(self):
return "%s(%s..%s)" % (self.__class__.__name__, self.start,
self.end)
class Period1(BasePeriod):
length = hybrid_property(lambda s: s.end - s.start)
def __init__(self, start, end):
self.start = start
self.end = end
mapper(Period1, interval_table1)
class Period2(BasePeriod):
end = hybrid_property(lambda s: s.start + s.length)
def __init__(self, start, length):
self.start = start
self.length = length
mapper(Period2, interval_table2)
session = create_session()
intervals = [timedelta(seconds=1),
timedelta(1),
timedelta(366)]
instants = [datetime.now(),
datetime(2000, 1, 2, 3, 4, 5),
datetime(1987, 6, 5, 4, 3, 2, 1)]
session.begin()
for i in instants:
for j in intervals:
session.add(Period1(i, i + j))
session.add(Period2(i, j))
session.commit()
session.expunge_all()
for ptype in (Period1, Period2):
q = session.query(ptype)
for p in q.filter(ptype.length < timedelta(10)):
print p, p.length
for p in q.filter(ptype.end < date.today()):
print p, p.length
now = datetime.now()
for p in q.filter(ptype.contains(now)):
print p, p.contains(now)
other = Period2(datetime.now(), timedelta(7))
for p in q.filter(ptype.intersects(other)).order_by(ptype.length):
print p, p.intersects(other)
"""