[sqlalchemy] 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:PASSWORD@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()

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/DOaTfEuV_bYJ.
To post to this group, send email to sqlalchemy@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.

Re: [sqlalchemy] can't get delete-orphan work

[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)

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.