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 -~----------~----~----~----~------~----~------~--~---