CODEWITHSUNDEEP
mysqlpython-2.7
Robert Amato
Robert Amato

Reputation: 59

Correct MySQL query for multi table delete in Python

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?

Upvotes: 0

Views: 60

Answers (1)

jps
jps

Reputation: 22465

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.

Upvotes: 1

Related Questions