SQLAlchemy Core - Multiple Tables
Last Updated :
24 Apr, 2025
SQLAlchemy is an open-source library that provides a set of tools for working with relational databases. It offers a high-level Object-Relational Mapping (ORM) interface as well as a lower-level SQL Expression Language (Core) interface. SQLAlchemy Core is a lightweight and flexible SQL toolkit that provides a way to interact with relational databases using Python. In this article, we will explore how to work with multiple tables in SQLAlchemy Core and show some examples.
When working with a relational database, it's common to have multiple tables that are related to each other. For example, we might have a "students" table and a "fees" table, where each student has multiple fees associated with them. In SQLAlchemy Core, we can define multiple tables and create relationships between them.
Database ViewerExample 1:
Let's start with an example of defining two tables and creating a relationship between them. We'll create a "students" table and a "fees" table. Each student will have a unique ID, name, and age. Each fee will have a unique ID, amount, and a foreign key to the student ID. Here's the code to define these two tables:
In the below code, we have defined two tables - "students" and "fees". The "students" table has three columns - "id", "name", and "age". The "fees" table has three columns - "id", "amount", and "student_id". The "student_id" column is a foreign key that references the "id" column in the "students" table.
We have also defined a relationship between the two tables using the relationship function. The relationship function takes the name of the other table as an argument and defines the relationship between them.
Python3
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///gfg.db', echo=True)
Base = declarative_base()
class Student(Base):
__tablename__ = 'students'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
fees = relationship("Fee")
class Fee(Base):
__tablename__ = 'fees'
id = Column(Integer, primary_key=True)
amount = Column(Integer)
student_id = Column(Integer, ForeignKey('students.id'))
Base.metadata.create_all(engine)
#now let us insert some data into these tables and then query the data with the relationship
Session = sessionmaker(bind=engine)
session = Session()
s1 = Student(name='John Doe', age=20)
session.add(s1)
f1 = Fee(amount=100, student_id=s1.id)
f2 = Fee(amount=200, student_id=s1.id)
session.add_all([f1, f2])
session.commit()
student_fees = session.query(Student).filter_by(name='John Doe').one().fees
for fee in student_fees:
print(fee.amount)
Output:
output1
Example 2:
In this example, we have defined two tables: Student and Fees. The Student table has id, name, and age columns, and the Fees table has id, amount, and student_id columns. There is a one-to-many relationship between Students and Fees, where one student can have multiple fees.
To join these two tables, we use the join method of the query object. We specify the Fees table and the condition for the join using the join method. In this case, we join the two tables on the student_id column of the Fees table and the id column of the Student table.
We also use the order_by method to order the results by the name column of the Student table and the amount column of the Fees table.
Finally, we print the result, which is a list of tuples containing the name and amount of each student's fees.
This example demonstrates how to use SQLAlchemy Core to join multiple tables and retrieve data from them.
Python3
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Student(Base):
__tablename__ = 'students'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
fees = relationship('Fee', back_populates='student')
class Fee(Base):
__tablename__ = 'fees'
id = Column(Integer, primary_key=True)
amount = Column(Integer)
student_id = Column(Integer, ForeignKey('students.id'))
student = relationship('Student', back_populates='fees')
engine = create_engine('sqlite:///debo.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
students = [
Student(name='John Doe', age=20),
Student(name='Jane Smith', age=22),
Student(name='Bob Brown', age=25),
Student(name='Alice Jones', age=23)
]
fees = [
Fee(amount=5000),
Fee(amount=6000),
Fee(amount=4500),
Fee(amount=5500)
]
for i in range(len(students)):
students[i].fees.append(fees[i])
session.add(students[i])
session.commit()
from sqlalchemy.orm import joinedload
# Query all students and their fees using joinedload
students = session.query(Student).options(joinedload(Student.fees)).all()
for student in students:
print(f'{student.name} ({student.age}):')
for fee in student.fees:
print(f'- {fee.amount}')
# Query students and fees using a join
from sqlalchemy.orm import aliased
student = aliased(Student)
fee = aliased(Fee)
stmt = session.query(student, fee)\
.join(fee, student.id == fee.student_id)\
.order_by(student.name)\
.all()
for s, f in stmt:
print(f'{s.name} ({s.age}): {f.amount}')
Output:
output2
Similar Reads
SQLAlchemy Core - Multiple Table Updates
SQLAlchemy Core provides a powerful feature for performing updates on multiple tables in a database. This allows us to update related data in multiple tables in a single transaction, ensuring data consistency and integrity. In this approach, we define the tables using SQLAlchemy's Table object and c
6 min read
SQLAlchemy Core - Creating Table
In this article, we are going to see how to create table in SQLAlchemy using Python. SQLAlchemy is a large SQL toolkit with lots of different components. The two largest components are SQLAlchemy Core and SQLAlchemy ORM. The major difference between them is SQLAlchemy Core is a schema-centric model
3 min read
Sum multiple columns in SQLAlchemy
In this article, we are going to sum multiple columns and get results using the SQLAlchemy module of python. Installation To install the SQLAlchemy module, run the following command in your terminal: pip install sqlalchemy pymysql Note: pymysql is a dependency of SQLAlchemy which we need to install
3 min read
SQLAlchemy Core - Delete Statement
In this article, we are going to see how to use the DELETE statement in SQLAlchemy against a PostgreSQL database in python. Creating table for demonstration: Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as sh
2 min read
SQLAlchemy - Mapping Table Columns
In this article, we will see how to map table columns using SQLAlchemy in Python. You will need a database (MySQL, PostgreSQL, SQLite, etc) to work with. 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
5 min read
Drop Multiple Tables in MySQL
DROP statement in a relational database management system (RDBMS) is used to delete a database object such as a table, index, or view. It can be used to delete the complete database also. The general syntax of the DROP command is as follows: Syntax: DROP object object_name; Example: DROP DATABASE db
3 min read
SQLAlchemy Core - Functions
SQLAlchemy provides a rich set of functions that can be used in SQL expressions to perform various operations and calculations on the data. SQLAlchemy provides the Function API to work with the SQL functions in a more flexible manner. The Function API is used to construct SQL expressions representin
7 min read
Select NULL Values in SQLAlchemy
In this article, we will see how to select NULL values into a PostgreSQL database using SQLAlchemy in Python. For demonstration purposes first, let us create a sample table using SQLAlchemy in PostgreSQL as shown below Creating a table using SQLAlchemy in PostgreSQL:Import necessary functions from S
3 min read
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 rela
3 min read
MySQL INFORMATION_SCHEMA Tables
MySQL provides a powerful set of system tables known as the INFORMATION_SCHEMA tables that contain metadata about the database system, including the databases, tables, columns, indexes, privileges, and more. These tables are a valuable resource for database administrators and developers to query and
3 min read