How can I delete both the user and their post at the same time in my Flask App

Question

In my head this is supposed to be easy but it's challenging me a little bit. I am trying to delete a user along with their post on a blog. However, I am not having much success with this.

Here is my deleted user route:

@views.route('/deleteuser/<id>')
@login_required
def deleteuser(id):
   user = User.query.get_or_404(id)
   try:
        db.session.delete(user)

   except:
        db.session.delete(user)
    db.session.commit()
    flash ('The user was successfully deleted', 'success')
    return redirect (url_for('views.users'))

Here is my delete post Id:

@views.route('/delete/<id>')
@login_required
def delete(id):
   post = Post.query.get_or_404(id)
   try:
        os.unlink(os.path.join(current_app.root_path,'static/assets/images/' + post.image))
        db.session.delete(post)
    except:
        db.session.delete(post)
db.session.commit()
flash ('Post deleted succesfully', 'success')
return redirect (url_for('views.dashboard'

Is there a way I can deleted the user along with the post?

Again, I am still new to this so please be kind.

Answer 1

What you would need, is a "one-to-many relationship".

When you want to delete the user, simply go ahead and do a cascade delete and voila! both the user and his/her related post(s) would be deleted.

I am assuming that you don't know about one-to-many realtionship. If you know and only want to know about the deletion process, take a look at STEP (4).

Anyways, Consider the following procedures to create a one-to-many relationship in flask-sqlalchemy and how to perform a cascade deletion:

To start off with, I have a User and Post model like so:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
            ...
            ...

class Post(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
            ...
            ...

Now, its time to decide a relationship. Logically, one user can create many posts, right? So the most apt relationship would be to use one-to-many relationship. Let's go ahead and implement that:

1) Declare the relation from the User Model : Since its the Users who write the Posts, we must begin declaring relations from the User's side (conveniently and logically correct). Just add this line inside the User model:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
            ...
            ...
    posts = db.relationship('Post', backref='written_by')

Let's break this down a little bit. posts here, is a pseudo-column, which means this column does not exist in the User model. Its only there to create a relationship. 'Post' is the name of the model we want to attach the relationship with. In our case, its the Post model. Finally, backref is used to refer back to the User model. We will take a look at this, shortly.

2) Create a user_id column in Post : To access the columns of User from Post, we must have a ForeignKey of id from User. Let's declare that. Just add this line in the Post model:

class Post(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
            ...
            ...
    user_id = db.Column(db.Integer, db.ForeginKey('user.id'))

Lets see what this line does. This code simply creates a column called user_id which contains the id form User model. Please note that here, 'user.id' is actually referring to id from User model and writing 'User.id' instead will return error. Flask-Sqlalchemy has such syntactical irregularities. Stay frosty!

3) Insert the data : I am going to insert some data into User model. It is a straight-forward process and I believe you know this as well. I am going to add dummy data through my python shell (assume that I have a required column called username, for convenience):

>>> user1 = User(username='John', .., ...)
>>> user2 = User(username='Joe', .., ...)
>>> db.session.add_all([user1, user2])
>>> db.session.commit()

Now, let me add some posts. Lets imaging that user1 has written 2 posts while user2 has only written 1 post.

>>> post1 = Post(title='I like Python',.., .., written_by=user1)
>>> post2 = Post(title='I like C',.., .., written_by=user1)
>>> post3 = Post(title='Redis, Really?',.., .., written_by=user2)
>>> db.session.add_all([post1, post2, post3])
>>> db.session.commit()

Carefully observe couple of things here. While inserting the data into Post model, we pass in the required columns and then, finally we make use of the backref to assign this post to the respective user (since this is a one-to-many model). backref accepts object / instance. Now, remember that we have declared the backref as written_by and that we have already created the user1 instance. This is the justification of written_by=user1 in the above code.

[OPTIONAL] Our relationship is crafted and the tables are ready! If you type in the following, you should get the following results:

>>> post1.written_by.username  # testing backref
'John'

>>> post3.written_by.username  # testing backref
'Joe'

>>> user1.posts   # should return us the list of post objects written by user1
[<Post object>, <Post object>]

>>> user1.posts[0].title
'I like Python'

4) Cascade Deletion : Now, in order to delete a user and its corresponding posts, all you have to do is add cascade='all, delete' in the User relationship:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
            ...
            ...
    posts = db.relationship('Post', backref='written_by', cascade='all, delete') # <--- edit here

Now, lets go ahead and delete the user John. This should delete all his written posts as well:

>>> delete_user = User.query.filter_by(username='John').first()
>>> db.session.delete(delete_user)
>>> db.session.commit()

NOTE: If you tried to delete any of the posts instead of the user, then only that post will be deleted and the user would still exist.

So, long story short: You need to use a one-to-many realtionship and then use a cascade deletion from the table where you have declared relationship.