Flask SQLAlchemy ondelete='CASCADE'不删除“用户”的“帖子”。（使用SQLite数据库）

Question

这是我的桌子。我正在使用SQLite数据库

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    username = db.Column(db.String(30), unique=True, nullable=False)
    password = db.Column(db.String(120), nullable=False)
    date_joined = db.Column(db.DateTime, nullable=False,
                            default=datetime.utcnow)
    posts = db.relationship('Post', backref='author',
                            lazy=True, passive_deletes=True)

    def __repr__(self):
        return f"User('{self.email}', '{self.username}', '{self.date_joined}')"


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    body = db.Column(db.Text, nullable=False)
    link = db.Column(db.String(300))
    date_posted = db.Column(db.DateTime, nullable=False,
                            default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey(
        'user.id', ondelete='CASCADE'), nullable=False)

    def __repr__(self):
        return f"Post('{self.title}', '{self.body}', '{self.link}', '{self.date_posted}')"

这是对“用户”路由的删除，它删除用户，但不删除用户创建的帖子

@login_required
@app.route('/delete/account', methods=['GET', 'POST'])
def delete_account():
    if current_user.is_authenticated:
        db.session.query(User).filter_by(id=current_user.id).delete()
        db.session.commit()
        flash('Your account has been deleted!', 'success')
        return redirect(url_for('home'))
    return render_template('settings.html', title='Account Settings')

Answer 1

您可以在用户模型中使用属性cascade来实现以下目标：

posts = db.relationship('Post', backref='author',
                            lazy=True, cascade='delete')

Answer 2

SQLite3需要一些引导来强制执行外键。你基本上需要执行

PRAGMA foreign_keys=ON

在每个新连接上。我做了如下的事情

db = SQLAlchemy()
app = Flask(__name__)
... configure app
db.init_app(app)
if app.config['SQLALCHEMY_DATABASE_URI'].startswith('sqlite'):
    from sqlalchemy import event  # noqa
    from sqlalchemy.engine import Engine  #noqa

    @event.listens_for(Engine, "connect")
    def _sqlite_fk_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

This gist提供了一个变体

Answer 3

您需要进行单独的查询以删除帖子

db.session.query(Post).filter(Post.user_id == current_user.id).delete()