删除 ManyToMany 中间表中的行
我有两个表,它们之间具有多对多关系。有时我需要刷新 数据库,因此我从两个表中删除元素。但是,已删除行之间的关系仍然存储在自动创建的中间表中。
为了澄清问题,这里有一个小代码:
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()
sqlite 数据库的转储现在包含:
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;
我希望在删除 a1 或 b1 时清空“b_alist__a_blist”表。
如果不使用 SQLite 并不总是支持的 ON DELETE 语句,这是否可能?
由于我当然不是唯一一个使用 Elixir 的多对多关系的人,因此这个问题的解决方案可能是微不足道的。
上面给出的代码生成 sqlalchemy 警告:
sqlalchemy/orm/properties.py:842: SA警告:在 B.alist 上,删除孤儿 不支持级联 多对多或多对一 当 single_parent 不是时的关系 放。设置 single_parent=True 关系()。
self._define_direction()
这只是因为我现在随机尝试在这个 ManyToMany 关系中添加级联选项。这应该表明删除孤立不是正确的选项。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我想我已经找到答案了。
首先,问题与单独的 sqlalchemy 相同。
然后,这似乎只在使用以下语法时才会发生:
但是可以通过使用以下方法获得所需的行为:
对每个 b 进行
session.delete(b)
的简单迭代可能会达到目的。也许有人可以评论
session.query().delete()
和session.delete()
的区别...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:
But one can obtain the desired behavior by using:
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()
andsession.delete()
...