The following script works as expected (also in 0.5.8) with
use_ansi=True. However, with use_ansi=False, the SQL is ill-
formed:
===============================================================
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('oracle://arc:a...@localhost:1521/xe?
use_ansi=False',echo=True)
metadata = MetaData()
Session = sessionmaker(bind=engine)
session = Session()
orders_table = Table("orders", metadata,
Column("orderid", Unicode, primary_key=True)
)
orderdetails_table = Table("orderdetails",metadata,
Column("orderid", Unicode, ForeignKey('orders.orderid'),
primary_key=True),
Column("lineid", Integer, primary_key=True),
Column("saleprice", Numeric, nullable=False),
Column("qtyordered",Numeric)
)
class Order(object):
pass
class OrderDetail(object):
pass
order_mapper = mapper(Order, orders_table,
properties=dict(orderdetails=relation(OrderDetail,
cascade='all,delete-orphan',
single_parent=True,
lazy=False,
backref=backref('parentorder',
cascade='refresh-expire,expunge'))))
# ----------- totalsale -----------
# note, I needed to add aliases because if you join with these
tables
in the rest of the query,
# we need it to be un-ambiguous
od_alias=orderdetails_table.alias('od__a')
order_mapper.add_property('totalsale',
# totalsale is an inline view column
column_property(
select([func.sum(od_alias.c.qtyordered *
od_alias.c.saleprice)],
orders_table.c.orderid==od_alias.c.orderid
).label('totalsale')))
orderdetail_mapper = mapper(OrderDetail, orderdetails_table)
#metadata.create_all(engine)
o=session.query(Order).all()
===============================================================
Expected SQL (use_ansi=True):
-------------------------------------------
2010-04-30 21:09:10,359 INFO sqlalchemy.engine.base.Engine.0x...
9450
SELECT orders.orderid AS orders_orderid, (SELECT sum
(od__a.qtyordered
* od__a.saleprice) AS sum_1
FROM orderdetails od__a
WHERE orders.orderid = od__a.orderid) AS totalsale,
orderdetails_1.orderid AS orderdetails_1_orderid,
orderdetails_1.lineid AS orderdetails_1_lineid,
orderdetails_1.saleprice AS orderdetails_1_saleprice,
orderdetails_1.qtyordered AS orderdetails_1_qtyordered
FROM orders LEFT OUTER JOIN orderdetails orderdetails_1 ON
orders.orderid = orderdetails_1.orderid
2010-04-30 21:09:10,360 INFO sqlalchemy.engine.base.Engine.0x...
9450
{}
SQL with use_ansi=False:
------------------------------------------
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00936: missing
expression
'SELECT orders.orderid AS orders_orderid, (SELECT
sum(od__a.qtyordered * od__a.saleprice) AS sum_1 \nFROM
orderdetails
od__a \nWHERE orders.orderid = od__a.orderid AND ) AS totalsale,
orderdetails_1.orderid AS orderdetails_1_orderid,
orderdetails_1.lineid AS orderdetails_1_lineid,
orderdetails_1.saleprice AS orderdetails_1_saleprice,
orderdetails_1.qtyordered AS orderdetails_1_qtyordered \nFROM
orders,
orderdetails orderdetails_1 \nWHERE orders.orderid =
orderdetails_1.orderid(+)' {}
Note the " AND )" before "AS totalsale" is causing the missing
expression database error.
--
You received this message because you are subscribed to the
Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com
.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
.
