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

def __init__(self, title, authors):
# here authors is a list of items of type Autor
self.title = title
for author in authors:

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)
self.Session = sessionmaker(self.engine)

def find_or_create_author(session, name):
qauthor = session.query(Author).filter_by(name=name)
if qauthor.count() == 0:
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')])])


# 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()



# 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;

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



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question