My application has a table of item prices by week. A record is inserted into a week whenever there is a new, different price. To find the current price, you have to look backwards in time to the most recent record.
I've written some code using SqlAlchemy 0.3.10 to generate a query like this. The resulting query fails on PostgreSQL because the innermost query generated by SQLAlchemy is missing a FROM clause. (SQLite seems happy with it, however.) Is this a bug in SqlAlchemy? A sample program is attached. Barry --- from sqlalchemy import * from sqlalchemy.ext.assignmapper import assign_mapper import sqlalchemy from sqlalchemy.ext import activemapper, sessioncontext engine = None def create_engine(): global engine #engine = sqlalchemy.create_engine('sqlite://') engine = sqlalchemy.create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/testdb') metadata.connect(engine) def create_session(): return sqlalchemy.create_session(bind_to=engine) def fuzzy_search(column, value): """Case insensitive search allowing partial string matches.""" return func.lower(column).like('%%%s%%' % value.lower()) metadata = activemapper.metadata create_engine() ########################################################################## # Tables ########################################################################## week_table = Table( 'cal_week', metadata, Column('id', Integer, primary_key=True), Column('begin_date', Date), # Sunday beginning the fiscal week Column('end_date', Date)) # Saturday ending the fiscal week product_table = Table( 'prod_product', metadata, Column('id', Integer, primary_key=True)) promotion_product_week_table = Table( 'promo_promotion_product_week', metadata, Column('id', Integer, primary_key=True), Column('product_id', Integer, ForeignKey('prod_product.id'),index=True), Column('begin_date', Date), Column('end_date', Date), Column('regular_price', Float)) product_regular_price_table = Table( 'prod_regular_price', metadata, Column('id', Integer, primary_key=True), Column('product_id', Integer, ForeignKey('prod_product.id')), Column('week_id', Integer, ForeignKey('cal_week.id')), Column('regular_price', Float), UniqueConstraint('product_id', 'week_id')) ########################################################################## # Main program ########################################################################## import sys metadata.create_all() # We're trying to build this query: # update promo_promotion_product_week set regular_price = ( # select prod_regular_price.regular_price # from prod_regular_price # inner join cal_week # on prod_regular_price.week_id = cal_week.id # where prod_regular_price.product_id = promo_promotion_product_week.product_id and cal_week.begin_date = # (select max(begin_date) # from prod_regular_price # inner join cal_week # on prod_regular_price.week_id = cal_week.id # where prod_regular_price.product_id = promo_promotion_product_week.product_id and cal_week.begin_date <= promo_promotion_product_week.begin_date)) price_week_s = join(product_regular_price_table, week_table) price_week_s2 = price_week_s.alias() week_with_price_q = select([func.max(week_table.c.begin_date)], from_obj=[price_week_s, week_table], whereclause=and_( product_regular_price_table.c.product_id == promotion_product_week_table.c.product_id, week_table.c.begin_date <= promotion_product_week_table.c.begin_date)) current_price_q = select([product_regular_price_table.c.regular_price], from_obj=[price_week_s], whereclause=and_( product_regular_price_table.c.product_id == promotion_product_week_table.c.product_id and week_table.c.begin_date == week_with_price_q)) # The subquery's SQL looks correct when evaluated standalone print str(week_with_price_q) # Generates invalid SQL - notice the FROM clause is missing from v update_query = promotion_product_week_table.update(values={promotion_product_week_table.c.regular_price:current_price_q}) print str(update_query) update_query.execute() ____________________________________________________________________________________ Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---