Correct MySQL query for multi table delete in Python

Question

Looking to be able to delete a message and any comments associated with it from a link on my page. From searching, I believe I might need to do an ON DELETE CASCADE in my python code? However, I'm not too sure how to go about it? This is the last query I tried

@app.route('/messages/<id>/delete')
def delete_message(id):

    query = 'DELETE FROM messages WHERE id = :id AND comments WHERE id = :id'
    data = {'id':id}
    mysql.query_db(query,data)

    return redirect('/wall')

Tables

USERS
id
first_name
last_name
email
pw_hash

MESSAGES
id
messages
user_id

COMMENTS
id 
comments
message_id
user_id

Any suggestions on how to write this out?

jps · Accepted Answer · 2017-08-31 13:11:09Z

1

You are on the right way with ON DELETE CASCADE. However, it's not something you do in your Python code but a matter of the right settings when you design your database. If we just focus on your tables messages and comments, the comments table has a foreign key relation to messages, that means the field message_id is linked to the field messages.id. This doesn't happen automatically, but in your DB-Admintool (e.g phpMyAdmin, HeidiSQL) you need to set this foreign key. And on this foreign key you can set the ON DELETE option to CASCADE in order to have your comments deleted when you delele the message.

Then your query

query = 'DELETE FROM messages WHERE id = :id'

will delete the message with that id and automatically also all comments for that message.

I think http://webdevzoom.com/create-edit-delete-foreign-key-phpmyadmin/ might help with some more explanations and illustrations.

edited Aug 31, 2017 at 13:11

answered Aug 31, 2017 at 12:10

jps

22.9k18 gold badges91 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Amato Over a year ago

@jps-Thanks! I went and redid my schema and added the CASCADE to it, and it works great.

Collectives™ on Stack Overflow

Correct MySQL query for multi table delete in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related