- SQLAlchemy Tutorial
- SQLAlchemy - Home
- SQLAlchemy - Introduction
- SQLAlchemy Core
- Expression Language
- Connecting to Database
- Creating Table
- SQL Expressions
- Executing Expression
- Selecting Rows
- Using Textual SQL
- Using Aliases
- Using UPDATE Expression
- Using DELETE Expression
- Using Multiple Tables
- Using Multiple Table Updates
- Parameter-Ordered Updates
- Multiple Table Deletes
- Using Joins
- Using Conjunctions
- Using Functions
- Using Set Operations
- SQLAlchemy ORM
- Declaring Mapping
- Creating Session
- Adding Objects
- Using Query
- Updating Objects
- Applying Filter
- Filter Operators
- Returning List and Scalars
- Textual SQL
- Building Relationship
- Working with Related Objects
- Working with Joins
- Common Relationship Operators
- Eager Loading
- Deleting Related Objects
- Many to Many Relationships
- Dialects
- SQLAlchemy Useful Resources
- SQLAlchemy - Quick Guide
- SQLAlchemy - Useful Resources
- SQLAlchemy - Discussion
Working with Related Objects
In this chapter, we will focus on the related objects in SQLAlchemy ORM.
Now when we create a Customer object, a blank invoice collection will be present in the form of Python List.
c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "gk@gmail.com")
The invoices attribute of c1.invoices will be an empty list. We can assign items in the list as −
c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]
Let us commit this object to the database using Session object as follows −
from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() session.add(c1) session.commit()
This will automatically generate INSERT queries for customers and invoices tables −
INSERT INTO customers (name, address, email) VALUES (?, ?, ?) ('Gopal Krishna', 'Bank Street Hydarebad', 'gk@gmail.com') INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?) (2, 10, 15000) INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?) (2, 14, 3850)
Let us now look at contents of customers table and invoices table in the table view of SQLiteStudio −
You can construct Customer object by providing mapped attribute of invoices in the constructor itself by using the below command −
c2 = [ Customer( name = "Govind Pant", address = "Gulmandi Aurangabad", email = "gpant@gmail.com", invoices = [Invoice(invno = 3, amount = 10000), Invoice(invno = 4, amount = 5000)] ) ]
Or a list of objects to be added using add_all() function of session object as shown below −
rows = [ Customer( name = "Govind Kala", address = "Gulmandi Aurangabad", email = "kala@gmail.com", invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]), Customer( name = "Abdul Rahman", address = "Rohtak", email = "abdulr@gmail.com", invoices = [Invoice(invno = 9, amount = 15000), Invoice(invno = 11, amount = 6000) ]) ] session.add_all(rows) session.commit()