Hello,
Recently I started playing with SQLAlchemy. When implementing some
database, I ran into this problem: The very neat column_property feature of
SA accepts various constructs like comparisons, subselects and even other sql
operators such as addition and concatenation. But boolean comparisons
(represented by SA.sql.expression.ClauseList objects) cause the code to
break, whilst in SQL these constructions are valid.
The SQL query I'm trying to map is:
SELECT id, begin_date, end_date,
begin_date IS NOT NULL
OR begin_date <= NOW()
AND
end_date IS NULL
OR end_date > NOW() AS active
FROM package WHERE active = 't'
The SA version used is 0.4.0beta5
-----------------------------------------------------
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///:memory:', echo=True)
meta = MetaData(bind=engine)
session = sessionmaker(bind=engine)()
package_table = Table('package', meta,
Column('id', Integer, primary_key=True),
Column('begin_date', DateTime),
Column('end_date', DateTime),
)
meta.create_all()
class Package(object): pass
"""
Here the column_property() is used in one of the documented ways.
This works, but doesn't contain all of the logic for active packages:
"""
mapper(Package, package_table, properties={
'active':column_property(
(package_table.c.begin_date != None).label('active')
),
})
"""
Here is the active column with all logic defined,
the code breaks as SA doesn't seem to allow logic
comparisons within column_property():
"""
clear_mappers()
try:
mapper(Package, package_table, properties={
'active':column_property(
((
(package_table.c.begin_date != None)
| (package_table.c.begin_date <= func.current_timestamp())
) & (
(package_table.c.end_date == None)
| (package_table.c.end_date > func.current_timestamp())
)
).label('active')),
})
except AttributeError, e:
""" e contains:
'ClauseList' object has no attribute 'label'
Or if we don't label the column_property construct:
<sqlalchemy.sql.expression.ClauseList object> is not a valid
candidate for ColumnProperty
"""
print e
"""
Playing around, I got to this solution. Which works like a charm. But
the mapper code is not that clean anymore.
"""
clear_mappers()
b = (package_table.c.begin_date == None)
c = ((
(package_table.c.begin_date != None)
| (package_table.c.begin_date <= func.current_timestamp())
) & (
(package_table.c.end_date == None)
| (package_table.c.end_date > func.current_timestamp())
))
### Force the type
c.type = b.type ### Which is a NoneType()
### Force the label
from sqlalchemy.sql.expression import _Label
labeled_c = _Label('active', c, c.type)
s = select([package_table, labeled_c]).alias('package_select')
mapper(Package, s)
q = session.query(Package).filter_by(active=True)
q.all()
-----------------------------------------------
Can this be done in a less ugly way? If not currently, can SA be fixed so
ClauseLists can be used within column_property?
Aldough I tried,
I couldn't write the patch on SA to fix it myself.
Thanks a lot,
Berik
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
