SQLAlchemy Core - Multiple Table Delete

In this article, we are going to see how to perform multiple-table DELETE in SQLAlchemy against a PostgreSQL database in Python.

Creating table for demonstration - BOOKS

Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price, genre, book_name. Insert record into the tables using insert() and values() function as shown.

Output:

Creating table for demonstration - book_publisher

Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called book_publisher with columns publisher_id, publisher_name, and publisher_estd. Insert record into the tables using insert() and values() function as shown.

Output:

Implementing a query to perform multiple-table delete in SQLAlchemy

Performing multiple-table delete has a slightly different procedure than that of a conventional SQL query which is  shown below

from sqlalchemy import delete

delete(tablename_1).where(tablename_1.c.column_name== tablename_2.c.column_name).where(tablename_2.c.column_name== 'value')

Get the books and book_publisher table from the Metadata object initialized while connecting to the database. Pass the delete query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.

The SQLAlchemy query shown in the below code deletes the record with book_id in books table corresponding with publisher_name "Springer" in the book_publisher table. . Then, we can write a conventional SQL query and use fetchall() to print the results to check whether the table record is deleted properly.

Output: