can't get delete-orphan work

[sector119]

Hi.

I think that I'm doing somethig wrong, but I can't get delete-orphah work... No DELETES on static_page_urls are performed...

2011-11-12 12:31:14 EET LOG:  statement: BEGIN

2011-11-12 12:31:14 EET LOG:  statement: DELETE FROM static_pages WHERE static_pages.id = 1

2011-11-12 12:31:14 EET ERROR:  update or delete on table "static_pages" violates foreign key constraint "static_page_urls_page_id_fkey" on table "static_page_urls"

2011-11-12 12:31:14 EET DETAIL:  Key (id)=(1) is still referenced from table "static_page_urls".

2011-11-12 12:31:14 EET STATEMENT:  DELETE FROM static_pages WHERE static_pages.id = 1

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, ForeignKey

from sqlalchemy import Integer, Unicode, UnicodeText

from sqlalchemy.orm import relationship

engine = create_engine('postgresql+psycopg2://USER:PASS...@127.0.0.1:5432/test', echo=True)

Session = sessionmaker()

Base = declarative_base()

Session.configure(bind=engine)

Base.metadata.bind = engine

class StaticPageUrl(Base):

    __tablename__ = 'static_page_urls'

    id = Column(Integer, primary_key=True)

    title = Column(Unicode(255), nullable=False)

    url = Column(UnicodeText, nullable=False)

    page_id = Column(Integer, ForeignKey('static_pages.id'), nullable=False)

class StaticPage(Base):

    __tablename__ = 'static_pages'

    id = Column(Integer, primary_key=True)

    title = Column(Unicode(255), nullable=False)

    urls = relationship(StaticPageUrl, cascade="all, delete, delete-orphan")

Base.metadata.create_all(engine)

session = Session()

p = StaticPage(title='test page')

p.urls.append(StaticPageUrl(title='testurl', url='www.url.org'))

session.add(p)

session.flush()

session.commit()

session.close()

session = Session()

user = session.query(StaticPage).filter_by(id=1).delete()

session.add(report)

session.flush()

session.commit()

session.close()

[Michael Bayer]

On Nov 12, 2011, at 2:36 AM, sector119 wrote:

> Hi.
>
> I think that I'm doing somethig wrong, but I can't get delete-orphah work... No DELETES on static_page_urls are performed...
>

> session = Session()
> user = session.query(StaticPage).filter_by(id=1).delete()

delete and delete-orphan cascade doesn't take effect with aggregate delete() calls against a Query. If you wanted this behavior you can get your database to do it (if supported) using "ON DELETE CASCADE" on your foreign keys. query.delete() also does not return an object, just a count of rows affected.

For the delete cascade to take effect you'd say:

static_page = s.query(StaticPage).filter_by(id=1).one()
s.query(static_page)

[sector119]

Thanks a lot, Michael!