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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
