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