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.
# import necessary packages import sqlalchemy
from sqlalchemy import create_engine, MetaData,
Table, Column, Numeric, Integer, VARCHAR, update, text, delete from sqlalchemy.engine import result
# establish connections engine = create_engine(
# initialize the Metadata Object meta = MetaData(bind = engine)
MetaData.reflect(meta) # create a table schema books = 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 table statement1 = 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 statement engine.execute(statement1) engine.execute(statement2) engine.execute(statement3) engine.execute(statement4) engine.execute(statement5) # Get the `books` table from the Metadata object BOOKS = meta.tables[ 'books' ]
|
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.
# import necessary packages import sqlalchemy
from sqlalchemy import create_engine, MetaData,
Table, String, Column, Numeric, Integer, VARCHAR, update, text, delete from sqlalchemy.engine import result
# establish connection engine = create_engine(
# store engine objects meta = MetaData()
# create a table book_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 values statement1 = 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 object book_publisher = meta.tables[ 'book_publisher' ]
|
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.
from sqlalchemy import delete
# query to multiple table delete delete_stmt = (delete(BOOKS).where(
BOOKS.c.book_id = = book_publisher.c.publisher_id).where(
book_publisher.c.publisher_name = = 'Springer' ))
# execute the statement engine.execute(delete_stmt) # write the SQL query inside the # text() block to fetch all records sql = text( "SELECT * from BOOKS" )
# Fetch all the records result = engine.execute(sql).fetchall()
# View the records for record in result:
print ( "\n" , record)
|
Output: