SQLAlchemy Core - Set Operations

SQLAlchemy Core is a powerful tool for working with databases in Python. One of the key features of SQLAlchemy Core is its support for set operations, which allow you to perform complex queries on your data. In this article, we will explore the basics of set operations in SQLAlchemy Core and provide some examples to help you understand how to use them effectively.

Set Operations in Python

Set operations are a set of SQL commands that allow you to combine the results of two or more SELECT statements. The three most common set operations are UNION, INTERSECT, and EXCEPT.

• UNION: It combines the results of two or more SELECT statements and returns only the unique rows.
• INTERSECT: It returns only the rows that are common to both SELECT statements.
• EXCEPT: It returns only the rows that are unique to the first SELECT statement.

These operations can be used to filter and combine data from multiple tables or to retrieve specific data based on certain conditions. They are useful in situations where you need to retrieve data from multiple tables or when you want to filter data based on certain criteria.

UNION

One of the most common set operations is the UNION operation, which combines the results of two or more SELECT statements and returns only the unique rows. For example, if we wanted to find all the employees who are older than 25 OR are in the IT department, we may use the following query:

Example

This query would return a list of all employees who are older than 25 OR are in the IT department.

SELECT * FROM employees
WHERE age > 25
UNION
SELECT * FROM employees
WHERE department = 'IT'

INTERSECT

Another useful set operation is the INTERSECT operation, which returns only the rows that are common to both SELECT statements. For example, if we wanted to find all employees who are older than 25 AND are in the IT department, we may use the following query:

Example

This query would return a list of all employees who are older than 25 AND are in the IT department.

SELECT * FROM employees
WHERE age > 25
INTERSECT
SELECT * FROM employees
WHERE department = 'IT'

EXCEPT

Finally, the EXCEPT operation returns only the rows that are unique to the first SELECT statement. For example, if we wanted to find all employees who are older than 25 but are NOT in the IT department, we may use the following query.

Example

This query would return a list of all employees who are older than 25 but are NOT in the IT department.

SELECT * FROM employees
WHERE age > 25
EXCEPT
SELECT * FROM employees
WHERE department = 'IT'

Database Description

This code uses the SQLAlchemy library to create an SQLite database with a table called "employees". The table has columns for "id", "name", "age", and "department". It then creates a new connection to the database and creates the "employees" table. Finally, it inserts data into the table by creating a session and adding Employee objects to the session, then committing the session. The echo=True on the create_engine method will make the SQLAlchemy engine log all the statements it's executing to stdout, which helps in debugging.

# importing required modules
from sqlalchemy import create_engine, Column, Integer, String, select
from sqlalchemy.ext.declarative import declarative_base

# Create a new connection to the SQLite database
engine = create_engine('sqlite:///employees.db', echo=True)

Base = declarative_base()


# Defining the Employee table
class Employee(Base):
    __tablename__ = 'employees'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    department = Column(String)

# Create the Employee table
Base.metadata.create_all(engine)

# Insert data into the table
from sqlalchemy.orm import sessionmaker

# Creating a session
Session = sessionmaker(bind=engine)
session = Session()

# inserting values into the Employee table
session.add(Employee(name='John Doe', age=30, department='IT'))
session.add(Employee(name='Jane Smith', age=25, department='HR'))
session.add(Employee(name='John Smith', age=26, department='BD'))
session.add(Employee(name='Jane Doe', age=41, department='IT'))


session.commit()

The generated table may be displayed as follows:

# Querying the table
select_st = select([Employee])
result = engine.execute(select_st)

# Print column names
print(result.keys())

# Print rows
for row in result:
    print(row)

Output:

Union Operation in Python

Query: If we wanted to find all the employees who are older than 25 OR are in the IT department.

This code uses the SQLAlchemy library to create a connection to an SQLite database with a table called "employees", which has columns for "id", "name", "age", and "department". It then creates a new connection to the database and creates the "employees" table. After that, it performs a UNION operation on the employee table. The UNION operation combines the result of two SELECT statements. The first statement selects all employees with an age greater than 25 and the second statement selects all employees with a department of 'IT'. The result of the UNION operation is a new table containing all rows from both of the original SELECT statements, with no duplicate rows. The code then executes the union_query on the engine and fetches the result, then it prints the name, age, and department of the employees. The echo=True on the create_engine method will make the SQLAlchemy engine log all the statements it's executing to stdout, which helps in debugging.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import select, union, intersect, except_

# Create a new connection to the SQLite database
engine = create_engine('sqlite:///employees.db', echo=True)

Base = declarative_base()


class Employee(Base):
    __tablename__ = 'employees'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    department = Column(String)


Base.metadata.create_all(engine)

# UNION example

# Generating the two SELECT queries
select_1 = select([Employee]).where(Employee.age > 25)
select_2 = select([Employee]).where(Employee.department == 'IT')

# Performing the UNION operations
union_query = select_1.union(select_2)

# Executing the query
result = engine.execute(union_query).fetchall()

# Displaying the result
for employee in result:
    print(employee.name, employee.age, employee.department)

Output:

Intersect operation in Python

Query: If we wanted to find all employees who are older than 25 AND are in the IT department.

This code is using the SQLAlchemy library in Python to interact with an SQLite database containing employee information. It creates a connection to the database and defines an Employee class which maps to the 'employees' table in the database. It then uses the intersect() method to get the intersection of two SQL select queries. One query selects all employees with an age greater than 25 and the other selects all employees in the IT department. The resulting query returns the employees who are both older than 25 and work in the IT department, and the code then prints out their name, age, and department.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import select, union, intersect, except_

# Create a new connection to the SQLite database
engine = create_engine('sqlite:///employees.db', echo=True)

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    department = Column(String)

Base.metadata.create_all(engine)

# INTERSECT example

# Generating the two SELECT queries
select_1 = select([Employee]).where(Employee.age > 25)
select_2 = select([Employee]).where(Employee.department == 'IT')

# Performing the INTERSECT operation
intersect_query = select_1.intersect(select_2)

# Executing the query
result = engine.execute(intersect_query).fetchall()

# Displaying the result
for employee in result:
    print(employee.name, employee.age, employee.department)

Output:

Except Operation in Python

Query: If we wanted to find all employees who are older than 25 but are NOT in the IT department.

This code is using the SQLAlchemy library in Python to interact with an SQLite database containing employee information. It creates a connection to the database and defines an Employee class which maps to the 'employees' table in the database. It then uses the except_() method to get the difference between two SQL select queries. One query selects all employees with an age greater than 25 and the other selects all employees in the IT department. The resulting query returns the employees who are older than 25 and do not work in the IT department, and the code then prints out their name, age, and department.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import select, union, intersect, except_

# Create a new connection to the SQLite database
engine = create_engine('sqlite:///employees.db', echo=True)

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    department = Column(String)

Base.metadata.create_all(engine)

# EXCEPT example

# Generating the two SELECT queries
select_1 = select([Employee]).where(Employee.age > 25)
select_2 = select([Employee]).where(Employee.department == 'IT')

# Executing the query
except_query = select_1.except_(select_2)

# Executing the query
result = engine.execute(except_query).fetchall()

# Displaying the result
for employee in result:
    print(employee.name, employee.age, employee.department)

Output: