我试图通过过滤其中一个关系中的列,从关联表中批量删除对象。我使用SQLAlchemy中的以下调用进行删除
db.session.query(UserPaper).join(Paper, (UserPaper.paper_id ==
Paper.id)).filter(UserPaper.user_id == user.id).filter(Paper.journal_id
== journal.id).delete()
并导致以下错误
^{pr2}$如果没有结尾的delete,SQLAlchemy查询是
SELECT userpapers.user_id AS userpapers_user_id, userpapers.paper_id AS
userpapers_paper_id, userpapers.created AS userpapers_created,
userpapers.read_at AS userpapers_read_at, userpapers.score AS userpapers_score
FROM userpapers JOIN papers ON userpapers.paper_id = papers.id
WHERE userpapers.user_id = :user_id_1 AND papers.journal_id = :journal_id_1
这是正确的。从这个错误可以看出,当我将delete()
追加到查询时,SQL语句的连接部分丢失了,数据库显然不知道如何找到papers.journal_id
列。我不明白为什么会这样?在
这是我的ORM对象的设置
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(255), unique=True)
papers = db.relationship("UserPaper", backref=db.backref('users'), lazy='dynamic')
class Paper(db.Model):
__tablename__ = 'papers'
id = db.Column(db.Integer, primary_key = True)
title = db.Column(db.String(1024))
journal_id = db.Column(db.Integer, db.ForeignKey('journals.id'))
class UserPaper(db.Model):
__tablename__ = 'userpapers'
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
paper_id = db.Column(db.Integer, db.ForeignKey('papers.id'), primary_key=True)
paper = db.relationship("Paper", backref=db.backref('user_paper'))
read_at = db.Column(db.DateTime)
score = db.Column(db.Integer)
class Journal(db.Model):
__tablename__ = 'journals'
id = db.Column(db.Integer, primary_key = True)
title = db.Column(db.String(100), index = True, unique = True)
papers = db.relationship('Paper', backref = 'journal', lazy = 'dynamic')
我在使用MySQL 5.6时遇到了同样的问题。它看起来像是一个bug/限制。但是,获得arround的一个更好的方法(与创建查询、循环遍历结果并逐个删除结果相比)是在两个后续查询中执行此任务:
它对我很有效,应该也能解决你的问题。在
相关问题 更多 >
编程相关推荐