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( "database+dialect://username:password@hostname:port/databasename")# 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
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.