Hi all,
I have two classes, Product and Price, that have a many-to-many relation
through a secondary table (see attached test file). I'm trying to delete
all Prices that are not related to any Product. I would expect the last
query in the file to produce something like
DELETE prices FROM prices LEFT OUTER JOIN current_prices ON prices.id =
current_prices.price_id WHERE current_prices.product_id IS NULL
but instead sqlalchemy generates
DELETE FROM prices WHERE current_prices.product_id IS NULL
and then (rightly) crashes with "(OperationalError) no such column:
current_prices.product_id"
I'm seeing this on 1.0.0b4 and on 0.9.8 with Python 2.7.3 on Debian 7, in
sqlite as well as on MySQL. What am I doing wrong?
Thanks, Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import create_engine, Column, Integer, Float, String, ForeignKey, Table
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
SABase = declarative_base()
class Price(SABase):
__tablename__ = 'prices'
id = Column(Integer, primary_key=True)
price = Column(Float)
currentPricesTable = Table('current_prices', SABase.metadata,
Column('price_id', Integer, ForeignKey('prices.id')),
Column('product_id', Integer, ForeignKey('products.id'))
)
class Product(SABase):
__tablename__ = 'products'
id = Column(Integer, primary_key=True)
name = Column(String(100))
current_price = relationship(Price, secondary=currentPricesTable)
engine = create_engine('sqlite:///:memory:', echo=True)
SABase.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
(session.query(Price)
.outerjoin(currentPricesTable)
.filter(currentPricesTable.c.product_id == None)
.delete())
