sqlalchemy: delete() on m:n-relationship

[Wolfgang Meiners]

Hi all,

i dont understand, how sqlalchemy deletes from m:n relationships.

Maybe, someone can explain to me, how to delete in the following program:

(pyhton3, sqlalchemy 0.7.0)

=====================================================================
> #!/usr/bin/env python3
> # -*- coding: utf-8 -*-
> 
> '''
> Created on 19.05.2012
> 
> @author: wolfgang
> 
> '''
> 
> from sqlalchemy import *
> 
> from sqlalchemy.orm.session import sessionmaker
> from sqlalchemy.orm import relationship, backref
> from sqlalchemy.ext.declarative import declarative_base
> 
> 
> Base = declarative_base()
> 
> class Book(Base):
>     __tablename__='books'
>     
>     def __init__(self, title, authors):
>         # here authors is a list of items of type Autor
>         self.title = title
>         for author in authors:
>             self.authors.append(author)
>     
>     bid = Column(Integer, primary_key=True)
>     title = Column(String, index=True)
>     
>     authors = relationship('Author', secondary='author_book', 
>                            backref=backref('books', order_by='Book.title', cascade='all, delete'),
>                            cascade='all, delete')
>     
> class Author(Base):
>     __tablename__ = 'authors'
>     
>     def __init__(self, name):
>         self.name = name
>     
>     aid = Column(Integer, primary_key=True)
>     name = Column(String, index=True)
>     
> 
> # Association table between authors and books:
> author_book = Table('author_book', Base.metadata,
>                     Column('aid', Integer, ForeignKey('authors.aid'), primary_key=True),
>                     Column('bid', Integer, ForeignKey('books.bid'), primary_key=True))
> 
> 
> class DB:
>     def __init__(self, dbname=None, echo=False):
>         self.dbname = dbname if dbname else ':memory:'
>         self.dbfile = 'sqlite:///{db}'.format(db=self.dbname)
>         self.engine = create_engine(self.dbfile)
>         Base.metadata.create_all(self.engine)
>         self.Session = sessionmaker(self.engine)
> 
> def find_or_create_author(session, name):
>     qauthor = session.query(Author).filter_by(name=name)
>     if qauthor.count() == 0:
>         session.add(Author(name=name))
>     return qauthor.one()
>         
> if __name__ == '__main__':
> 
>     db = DB(dbname='booksdb.sqlite', echo=True)
>     session = db.Session()
>     
>     # insert 4 books into db
>     session.add_all([Book(title='Title a',
>                           authors=[find_or_create_author(session, name='Author 1'),
>                                    find_or_create_author(session, name='Author 2')]),
>                      Book(title='Title b',
>                           authors=[find_or_create_author(session, name='Author 1'),
>                                    find_or_create_author(session, name='Author 2')]),
>                      Book(title='Title c',
>                           authors=[find_or_create_author(session, name='Author 3'),
>                                    find_or_create_author(session, name='Author 4')]),
>                      Book(title='Title d',
>                           authors=[find_or_create_author(session, name='Author 3'),
>                                    find_or_create_author(session, name='Author 4')])])
>     
>     session.commit()
>     
>     # At this point there are 4 book in db, the first 2 written by Author 1 and Author 2,
>     # the last 2 written by Author 3 and Author 4.
>     # Now, i delete books with bid == 1 and bid == 3:
>     
>     book1 = session.query(Book).filter_by(bid=1).one()
>     session.delete(book1)
>     
>     session.query(Book).filter_by(bid=3).delete()
>     
>     session.commit()
>     
>     # The first query deletes to much: Title b is related to Author 1 and Author 2
>     # this relation has dissapeared from the db
>     
>     # The last query deletes to less: There is no Title 3, but the entries 
>     # of this book remain in the associationtable.
>     
>     # How is this done right?
==========================================================================================

after i run this program, the contents of booksdb.sqlite has the
following data:

$ sqlite3 booksdb.sqlite
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from author_book;
3|3
4|3
3|4
4|4

sqlite> select * from
   ...> books natural inner join author_book
   ...> natural inner join authors;
4|Title d|3|Author 3
4|Title d|4|Author 4

which means, association between Title b and ist authors is lost,
information on Title c is still in author_book table.

Thank you for any help

Wolfgang