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.
Python3
# import necessary packagesimport sqlalchemyfrom sqlalchemy import create_engine, MetaData, Table, Column, Numeric, Integer, VARCHAR, update, text, deletefrom sqlalchemy.engine import result# establish connectionsengine = create_engine(# initialize the Metadata Objectmeta = MetaData(bind=engine)MetaData.reflect(meta)# create a table schemabooks = Table( 'books', meta, Column('book_id', Integer, primary_key=True), Column('book_price', Numeric), Column('genre', VARCHAR), Column('book_name', VARCHAR))meta.create_all(engine)# insert records into the tablestatement1 = books.insert().values(book_id=1, book_price=12.2, genre='fiction', book_name='Old age')statement2 = books.insert().values(book_id=2, book_price=13.2, genre='non-fiction', book_name='Saturn rings')statement3 = books.insert().values(book_id=3, book_price=121.6, genre='fiction', book_name='Supernova')statement4 = books.insert().values(book_id=4, book_price=100, genre='non-fiction', book_name='History of the world')statement5 = books.insert().values(book_id=5, book_price=1112.2, genre='fiction', book_name='Sun city')# execute the insert records statementengine.execute(statement1)engine.execute(statement2)engine.execute(statement3)engine.execute(statement4)engine.execute(statement5)# Get the `books` table from the Metadata objectBOOKS = meta.tables['books'] |
Output:
Sample table – BOOKS
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.
Python3
# import necessary packagesimport sqlalchemyfrom sqlalchemy import create_engine, MetaData,Table, String, Column, Numeric, Integer, VARCHAR,update, text, deletefrom sqlalchemy.engine import result# establish connectionengine = create_engine(# store engine objectsmeta = MetaData()# create a tablebook_publisher = Table( 'book_publisher', meta, Column('publisher_id', Integer, primary_key=True), Column('publisher_name', String), Column('publisher_estd', Integer),)# use create_all() function to create# a table using objects stored in meta.meta.create_all(engine)# insert valuesstatement1 = book_publisher.insert().values( publisher_id=1, publisher_name="Oxford", publisher_estd=1900)statement2 = book_publisher.insert().values( publisher_id=2, publisher_name='Stanford', publisher_estd=1910)statement3 = book_publisher.insert().values( publisher_id=3, publisher_name="MIT", publisher_estd=1920)statement4 = book_publisher.insert().values( publisher_id=4, publisher_name="Springer", publisher_estd=1930)statement5 = book_publisher.insert().values( publisher_id=5, publisher_name="Packt", publisher_estd=1940)engine.execute(statement1)engine.execute(statement2)engine.execute(statement3)engine.execute(statement4)engine.execute(statement5)# Get the `book_publisher` table from the Metadata objectbook_publisher = meta.tables['book_publisher'] |
Output:
Sample table – book_publisher
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.
Python3
from sqlalchemy import delete# query to multiple table deletedelete_stmt = (delete(BOOKS).where( BOOKS.c.book_id == book_publisher.c.publisher_id).where( book_publisher.c.publisher_name == 'Springer'))# execute the statementengine.execute(delete_stmt)# write the SQL query inside the# text() block to fetch all recordssql = text("SELECT * from BOOKS")# Fetch all the recordsresult = engine.execute(sql).fetchall()# View the recordsfor record in result: print("\n", record) |
Output:
The output of multiple table delete
