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.
Python3
# 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:
Python3
# 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.
Python3
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.
Python3
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.
Python3
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:
Contact Us