Elixir - deleting rows in a ManyToMany intermediate table

Question

Hi,

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 ?

Thanks !

Edit:

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.

Answer 1

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