How can I write a delete postgresql query in Python flask

I was trying to delete from a database table using flask and postgresql, but I keep having error in mysql syntax.Please how can I implement it. The two ways below did not work. Nb: The id is to be retrieved from a variable. I'm actually new to python from php.

my_id = 3
del = engine.execute("DELETE FROM my_table WHERE id = '{my_id}'")
del = engine.execute("DELETE FROM my_table WHERE id=:id", {"id":my_id})

Solution 1:

SQLAlchemy 1.x

You could just do this:

my_id = 3
result = engine.execute("DELETE FROM my_table WHERE id = %s", my_id)

or

result = engine.execute("DELETE FROM my_table WHERE id = %(id)s", {'id': my_id})

In both of these statements you are assuming that the underlying database connection library (for example psycopg2 or pg8000) are using %-formatting to bind query parameters. To avoid this dependency you can use sqlalchemy.text to build the query string:

import sqlalchemy as sa
result = engine.execute(sa.text("DELETE FROM my_table WHERE id = :id"), id=my_id)

letting sqlalchemy build the query and handle parameter binding and quoting.

You should NOT use ordinary python string formatting functions to bind query parameters:

engine.execute("DELETE FROM my_table WHERE id = %s" % my_id)
engine.execute("DELETE FROM my_table WHERE id = {}".format(my_id))
engine.execute(f"DELETE FROM my_table WHERE id = {my_id}")

Using these methods may expose your application to SQL injection attacks, because the query parameters may not be quoted correctly. A point of style: you can't use del as a variable name. Python already has the del statement, and attempting to reassign the name del results in a SyntaxError. Also, the ResultProxy object returned by a DELETE statement doesn't contain any rows, so there is little point assigning the result to a variable.

SQLAlchemy 2.0

In SQLAlchemy 2.0 (still in development), or in SQLAlchemy 1.4 with the engine's future flag set to True, raw SQL strings can no longer be executed directly*, and neither is it possible to use an implicit connection as in engine.execute.

Raw SQL statements must be wrapped by sqlalchemy.text, with parameters passed as dictionaries, so the code would look like this:

with engine.begin() as conn:
    conn.execute(sa.text('DELETE FROM my_table WHERE id = :id'), {'id': my_id})

Object-oriented Style

Regardless of the version you are using, you can operate on a Table object to delete rows

# Reflect an existing table from the database.
tbl = sa.Table('my_table', sa.MetaData(), autoload_with=engine)

with engine.begin() as conn:
    conn.execute(tbl.delete().where(tbl.c.id == my_id))
    conn.execute(sa.delete(tbl).where(tbl.c.id == my_other_id))

* You can still execute raw SQL using Connection.exec_driver_sql, if you really must.