Deleting rows in a ManyToMany intermediate table

I have two tables with a ManyToMany relation between them. Sometimes I need to refresh the database so I delete elements from both tables. However relations between deleted rows are still stored inside the automatically created intermediary table.

To clarify the problem, here is a small code:

from elixir import *

metadata.bind = "sqlite:///test.db"
metadata.bind.echo = True

options_defaults['shortnames'] = True

class A(Entity):
    name = Field(Unicode(128))
    blist = ManyToMany("B",cascade='all,delete, delete-orphan')

class B(Entity):
    name = Field(Unicode(128))
    alist = ManyToMany("A",cascade='all,delete, delete-开发者_如何学运维orphan') 

setup_all()
create_all()


a1 = A()
a1.name = u"john"

b1 = B()
b1.name = u"blue"

a1.blist.append(b1)

session.commit()

session.query(A).delete()
session.query(B).delete()

session.commit()

A dump of the sqlite database now contains:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a (
    id INTEGER NOT NULL, 
    name VARCHAR(128), 
    PRIMARY KEY (id)
);
CREATE TABLE b (
    id INTEGER NOT NULL, 
    name VARCHAR(128), 
    PRIMARY KEY (id)
);
CREATE TABLE b_alist__a_blist (
    a_id INTEGER NOT NULL, 
    b_id INTEGER NOT NULL, 
    PRIMARY KEY (a_id, b_id), 
    CONSTRAINT a_blist_fk FOREIGN KEY(a_id) REFERENCES a (id), 
    CONSTRAINT b_alist_fk FOREIGN KEY(b_id) REFERENCES b (id)
);
INSERT INTO "b_alist__a_blist" VALUES(1,1);
COMMIT;

I would like "b_alist__a_blist" table to be emptied either when a1 or b1 is deleted.

Is this possible without using ON DELETE statements that are not always supported with SQLite?

Since I'm certainly not the only one using a ManyToMany relationship with Elixir, the solution to this problem is probably trivial.

The code given above generates sqlalchemy warnings:

sqlalchemy/orm/properties.py:842: SAWarning: On B.alist, delete-orphan cascade is not supported on a many-to-many or many-to-one relationship when single_parent is not set. Set single_parent=True on the relationship().

self._determine_direction()

This is just because I'm now randomly trying to add cascade options in this ManyToMany relation. This should be a sign that delete-orphan is not the correct option.

I think I have found the answer. First, the problem is the same with sqlalchemy alone.

Then, this seems only to happen when using this syntax:

session.query(B).delete()

But one can obtain the desired behavior by using:

session.delete(b)  #where b is an instance of B

A simple iteration of session.delete(b) for each b might then do the trick.

Maybe someone can comment on this difference of session.query().delete() and session.delete()...