"Delete from" deletes everything

Question

I have a python function that deletes a row in mysql table using name attribute as a condition:

def delete(table: str, name: str):
    cursor.execute(f"DELETE FROM {table} WHERE name = {name}")
    conn.commit()

I have one row with a name attribute equal to "Name". When I use this function with "Name" it deletes every single row in a table.

I'm guessing that it has to do with passed string being same as attribute. But what would be the solution to that problem except renaming attributes?

Answer 1

So for one, I think you are missing quotes around name, as well as a semicolon.

For further reading you should also take a look at Python parameterized query and Prepared Statement

I do agree with the comments, that table should not be an injected argument for security reasons!

def delete(table: str, name: str):
    query = f"DELETE FROM {table} WHERE name = ?"
    print(query)
    cursor.execute(query, (name,))
    conn.commit()`

EDIT FULL WORKING EXAMPLE:

import sqlite3            
conn = sqlite3.connect("test")

query_create = '''CREATE TABLE IF NOT EXISTS projects (
    id integer PRIMARY KEY,
    name text NOT NULL,
    begin_date text,
    end_date text
);'''
    
conn.execute(query_create)

query_insert = '''insert into projects (id, name, begin_date, end_date) values (1,"name","date","date")'''

conn.execute(query_insert)
query_select = '''select * from projects'''

cur = conn.execute(query_select)
print(cur.fetchall())


def delete(table: str, name: str):
    query = f"DELETE FROM {table} WHERE name = ?"
    print(query)
    conn.execute(query, (name,))

    
delete('projects', 'name')

cur = conn.execute(query_select)
print(cur.fetchall())

Gives Output:

[(1, 'name', 'date', 'date')]
DELETE FROM projects WHERE name = ?
[]