Python SQLAlchemy - Performing union with three queries

Last Updated : 03 Jun, 2022

In this article, we will see how to perform a union of three queries using SQLAlchemy in Python.

Since we are going to use MySQL in this post, we will also install a SQL connector for MySQL in Python. However, none of the code implementations changes with change in the database except for the SQL connectors.

pip install pymysql

We will use the sample sakila database from MySQL. In this article, we will cover 2 examples, one each for SQLAchemy Core and ORM layers. In both examples, we will extract the records from the payment table within the sakila database which have a `payment_id` of 1, 2, or 3 (as individual queries and then take the union of them). If you do not have the sakila database and want to follow along with this article without installing it then use the SQL script present in the link mentioned below to create the required schema and payment table along with the records.

Sakila Payment Table Script

For reference, the first ten records in the payment table look like this -

The SQL query which we are looking at in the below two examples is -

SELECT * FROM payment WHERE payment_id = 1
UNION
SELECT * FROM payment WHERE payment_id = 2
UNION
SELECT * FROM payment WHERE payment_id = 3;

Method 1: Using SQLAlchemy Core to perform union with three queries:

SQLAlchemy Core is a schema-centric model that means everything is treated as a part of the database i.e., rows, columns, tables, etc. In the above example, we have created the metadata object to access the database objects like the table. Using this object we get the metadata of the `payment` table. This metadata information is then used to query the table to get the union of the three queries. We first prepare individual queries with payment_id as 1, 2, and 3. Then these queries are passed as parameters in the sqlalchemy.union() method. As we can see in the output, we get the records having payment_id as 1, 2, or 3.

Syntax: sqlalchemy.sql.expression.union(*selects, **kwargs)¶

Return a UNION of multiple selectables. The returned object is an instance of CompoundSelect.

Python3

import sqlalchemy as db

# DEFINE THE ENGINE (CONNECTION OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")

# CREATE THE METADATA OBJECT TO ACCESS THE TABLE
meta_data = db.MetaData(bind=engine)
db.MetaData.reflect(meta_data)

# GET THE `payment` TABLE FROM THE METADATA OBJECT
payment = meta_data.tables['payment']

# PREPARING THE REQUIRED QUERY
query_1 = db.select(payment).filter(payment.c.payment_id == 1)
query_2 = db.select(payment).filter(payment.c.payment_id == 2)
query_3 = db.select(payment).filter(payment.c.payment_id == 3)

query = db.union(query_1, query_2, query_3)

# EXTRACTING RECORDS USING THE ENGINE AND QUERY
with engine.connect() as conn:
    result = conn.execute(query).all()

# PRINT THE RESULTANT RECORDS
for r in result:
    print(r.payment_id, "|", r.customer_id, "|", r.rental_id, "|", r.amount)

Output:

Method 2: Using SQLAlchemy ORM to perform union with three queries:

SQLAlchemy ORM uses an object-centric view that encapsulates the schema with business objects. It is a more pythonic implementation as we can see the tables represented in the class format. We have used this class object to query the payment table using the SQLAlchemy syntax mentioned above. We created individual queries to extract record(s) from the table having payment_id as 1, 2, or 3. Then the first query which is of `sqlalchemy.orm.Query` type is chained using the union() method. The other two queries are passed as parameters in the union() method. One or more `sqlalachemy.orm.Query` type objects can be passed as parameters to this method.

Syntax: sqlalchemy.orm.Query.union(*q)¶

Produce a UNION of this Query against one or more queries.

Python3

from sqlalchemy.orm import sessionmaker
import sqlalchemy as db
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# DEFINE THE ENGINE (CONNECTIO OBJECT)
engine = db.create_engine("mysql+pymysql://root:password@localhost/sakila")

# CREATE THE TABLE MODEL TO USE IT FOR QUERYING
class Payment(Base):

    __table__ = db.Table("payment", Base.metadata, autoload_with=engine)

# CREATE A SESSION OBJECT TO INITIATE QUERY IN DATABASE
Session = sessionmaker(bind=engine)
session = Session()

# PREPARING QUERY USING SQLALCHEMY
query_1 = session.query(Payment).filter(Payment.payment_id == 1)
query_2 = session.query(Payment).filter(Payment.payment_id == 2)
query_3 = session.query(Payment).filter(Payment.payment_id == 3)

# EXTRACT ALL THE RECORDS BY PERFORMING UNION OF THREE QUERIES
result = query_1.union(query_2, query_3).all()

# PRINT THE RESULTANT RECORDS
for r in result:
    print(r.payment_id, "|", r.customer_id, "|", r.rental_id, "|", r.amount)