Python SQLAlchemy ORM - Relationship Loading Techniques

[@surendrareddyGoluguri]

Thanks Zeq for making this amazing content on youtube. Your SQL alchemy series helped me a lot to start working on this technology😊.

[@zeqtech]

No problem! I'm glad to hear it has helped 😎

[@tomasemilio]

Any tips for async sqlalchemy?
Also when i do get posts i want select but when i do post where id equals blaaa i want all the relationships. What is the best strategy

[@zeqtech]

I haven't touched async SQLALchemy, when I do I can make a video on it .
If you're saying you want all the relationships connected to the posts then you can do something like:
# Using a selectinload() could be fast, '*' applies the loading to all relationships
query = session.query(Post).options(selectinload('*')).filter(Post.id == 1).one_or_none()
That will select and load all the relationships to that posts table for the specific post. You will have to play around with what is the best method, there are factors that go into it like how many relationships you're loading from.
# Using a joinedload() could be faster
query = session.query(Post).options(joinedload('*')).filter(Post.id == 1).one_or_none()
You can also get specific to which relationships you want to load specifically:
query = (
session.query(Post)
.options(
selectinload(Post.detail),
joinedload(Post.other_relationship),
)
.filter(Post.id == 1)
.one_or_none()
)
The best strategy is really just to find out what works for your situation

[@MrSule26]

Hi, Zeq Tech
Thanks for the video. I've been trying using SQLAlchemy ORM with mapped_column for a project of mine and I've been having a lot of issues, but your videos are helping me a lot.
Could you answer this question of mine?
For example: I inserted this data on the database
itauBank = Bank(
name=BANK_NAMES["ITAU"],
account_types=[
AccountType(
name=ACCOUNT_TYPES["CHECKING_ACCOUNT"],
account_rules=AccountRule(
withdraw_fee=1,
minimum_initial_balance=50,
),
),
AccountType(
name=ACCOUNT_TYPES["SAVING_ACCOUNT"],
account_rules=AccountRule(
withdraw_fee=3,
minimum_initial_balance=200,
),
),
],
)
How can I make a select which you get the object Bank with the name = "Itau" and only the AccounType with the name = "Checking Account"? I've already tried a lot of thinks, but I can just filter the name of the bank, but not both at the same time

[@zeqtech]

No problem! Ah I see, yeah this will be kind of a beefy response lol.
You will need to join the 2 tables in order to filter on both attributes.
You can do something like this:
bank = session.query(Bank).join(AccountType).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account")).first()
but it will return the bank object so when you access the `account_types` variable it will give you all the accounts.
But if you just want to get the accounts then you can do any of these:
account = session.query(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account")).first()
account = session.scalar(select(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account")))
account = session.execute(select(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account"))).first()
Those only return the first entry, but if you want all entries then you can do these:
accounts = session.query(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account")).all()
accounts = session.scalars(select(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account"))).all()
accounts = session.execute(select(AccountType).join(Bank).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account"))).all()
if you add the:
bank: Mapped["Bank"] = relationship()
on the AccountType table, you can then access the bank for that specific account

[@zeqtech]

I also want to add as a possibility to this returning the bank object and only the checking account associated with it:
query = session.query(Bank).join(Bank.account_types).options(
contains_eager(Bank.account_types)
).filter(and_(Bank.name == "Itau", AccountType.name == "Checking Account"))
bank = query.first()
if bank:
print(bank)
for account_type in bank.account_types:
print(account_type)
The containes_eager allows you to perform those check conditions on the subquery
Other than that you can get it as a tuple like this:
bank_account_tuple = session.query(Bank, AccountType).join(AccountType).filter(and_(Bank.name=="Itau", AccountType.name=="Checking Account")).all()
print(bank_account_tuple)

[@MrSule26]

@@zeqtech I see. Thanks for the tips. The first answer helped me a lot. I'm making progress now.

[@Melvi25]

Hi ZT,
thank you for great and simple tutorials. The SQLAlch docs is really hard to read for newbie... core-way, orm-way old and new.. mostly all mixed in 1 tutorial..
Will you do also tutorial for quering with joins? Getting values from Second table when you have FK (ID from 2nd table) in Main table.
I think I set correctly relationship(back_populates=).. but still can't get "second.name" when quering Main table

[@zeqtech]

You're welcome! Yes I understand that!
Yes I will be making a video on that.
If you use the "back_populates" then you need to have the relationship variable in the second table too like this example on my github:
github.com/ZeqTech/sqlalchemy-tutorials/blob/main/ep_06_one_to_many_relationships/user_post_example.py

[@AnthonyTetteh-t7q]

Thanks ZT. Simple, precise and straight to the point.
It'd be really really helpful if you'd added the perf_counter benchmark throughout to weigh the performance

[@zeqtech]

No problem! Yes 👍
That's a good idea 💡

[@rainrope5069]

really fantastic SQLAlchemy series

[@zeqtech]

Thank you 👍

[@tieutuyendang4545]

Great tutorial! ❤👍

[@zeqtech]

Thank you so much babe 🤟❤️

[@greob]

Nice presentation, thanks for sharing!