SQLAlchemy Core – Joins

SQLAlchemy Core is a Python toolkit that enables developers to create complex database applications. It provides several features, one of which is the ability to join tables. 

Joining tables allows developers to retrieve data from multiple tables simultaneously, which is useful when the data is related.

In the context of databases, a join is a way of combining rows from two or more tables based on a related column. Joins allow developers to retrieve data from multiple tables that have a relationship with each other. There are several types of joins that can be used, such as inner join, left join, right join, and outer join. Each type of join retrieves a different set of rows from the tables being joined.

To join tables using SQLAlchemy Core, developers must define the relationship between the tables using a foreign key constraint. In SQLAlchemy, relationships can be defined using the relationship() function.

Example:

Python

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Customer(Base):
    __tablename__ = 'customers'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    orders = relationship("Order", back_populates="customer")


class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey('customers.id'))
    product = Column(String)
    customer = relationship("Customer", back_populates="orders")

The Customers and Orders tables look as follows: 

Customer Table

order table

In this example, the Customer table has a one-to-many relationship with the Order table. Each customer can have multiple orders, but each order can only be associated with one customer.

To join these two tables using SQLAlchemy Core, developers can use the join() function. Here is an example of how to retrieve a list of all orders and the corresponding customer name for each order:

Python

from sqlalchemy import create_engine, Table, MetaData, select
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///example.db', echo=True)
Base.metadata.create_all(engine)

# Populate the database with sample data


Session = sessionmaker(bind=engine)
session = Session()

customers = [Customer(name='John'), Customer(name='Jane'),
             Customer(name='Bob'), Customer(name='Mike')]
session.add_all(customers)
session.commit()

orders = [
    Order(product='product_1', customer_id=customers[0].id),
    Order(product='product_2', customer_id=customers[1].id),
    Order(product='product_3', customer_id=customers[0].id),
    Order(product='product_4', customer_id=customers[2].id),
    Order(product='product_5', customer_id=customers[1].id)
]
session.add_all(orders)
session.commit()

# Perform a join query to retrieve data from multiple tables
stmt = select(Order.id, Order.product, Customer.name).select_from(
    Order).join(Customer, Order.customer_id == Customer.id)

results = session.execute(stmt)

for row in results:
    print(row)

session.close()

In this example, developers use the select() function to specify the columns to retrieve from the tables. They then use the select_from() function to specify the tables to join. Finally, they use the join() function to join the Orders table with the Customers table using the customer ID column.

OUTPUT : 

(1, ‘product_1’, ‘John’)
(2, ‘product_2’, ‘Jane’)
(3, ‘product_3’, ‘John’)
(4, ‘product_4’, ‘Bob’)
(5, ‘product_5’, ‘Jane’)

Result Table : 

OUTPUT

The output is a list of all orders and the corresponding customer name for each order. The first column represents the order ID, the second column represents the product name, and the third column represents the customer name.