4. Interview Questions- ORM Querying Guide

Source: https://docs.sqlalchemy.org/en/20/orm/queryguide/index.html

---

1. What method is used to start a query in SQLAlchemy ORM?

Answer: Use session.query() or select() in conjunction with session.execute(select(...)) for SQLAlchemy 2.0 style querying.

---

2. What is the recommended querying style in SQLAlchemy 2.0?

Answer: The select() construct combined with session.execute().

stmt = select(User).where(User.name == "Alice")
session.execute(stmt)

---

3. How do you filter rows in SQLAlchemy 2.0?

Answer: Use where() with expressions inside select().

select(User).where(User.id == 1)

---

4. How do you retrieve a single result from a query?

Answer: Use session.scalar(stmt) for a single scalar result or session.execute(stmt).scalar_one() to ensure exactly one row is returned.

---

5. What is the difference between scalar_one() and scalar_one_or_none()?

Answer:

• scalar_one(): Returns one result or raises an error if zero or more than one.

• scalar_one_or_none(): Returns one result or None; raises if more than one.

---

6. How do you retrieve all rows from a select statement?

Answer: Use .all() on the result object from session.execute():

result = session.execute(select(User)).scalars().all()

---

7. What does .scalars() do on a result object?

Answer: It extracts just the scalar entities from each row, e.g., the model objects instead of full row tuples.

---

8. How do you order query results in SQLAlchemy ORM?

Answer:

select(User).order_by(User.name.desc())

---

9. How do you limit the number of rows returned in SQLAlchemy?

Answer: Use .limit(n) on the select:

select(User).limit(10)

---

10. What is the difference between select(User) and session.query(User)?

Answer:

• select(User): Modern SQLAlchemy 2.0 Core + ORM API.

• session.query(User): Legacy ORM-style querying from SQLAlchemy 1.x.

---

11. How do you join two tables in SQLAlchemy 2.0?

Answer:

select(User).join(User.addresses)

---

12. How do you filter based on a joined table's column?

Answer:

select(User).join(User.addresses).where(Address.city == "Paris")

---

13. What does distinct() do in a query?

Answer: It ensures that the returned rows are unique based on all columns selected.

---

14. How can you perform an IN clause in a SQLAlchemy query?

Answer:

select(User).where(User.id.in_([1, 2, 3]))

---

15. How do you use SQL functions like count() in SQLAlchemy?

Answer:

from sqlalchemy import func
select(func.count(User.id))

---

16. What method is used to paginate results (i.e., skip and limit)?

Answer: Use .offset(n).limit(m) on the query:

select(User).offset(10).limit(5)

---

17. What does .unique() do on a result object?

Answer: It filters out duplicate ORM instances from the results (useful when joined loading creates multiple references to the same object).

---

18. Can you use ORM objects in select() without importing the table explicitly?

Answer: Yes, in SQLAlchemy 2.0, ORM classes like User can be passed directly into select().

---

19. How do you count rows while using select() with ORM models?

Answer:

select(func.count()).select_from(User)

---

20. What is the recommended way to iterate over results in a memory-efficient way?

Answer: Use:

for row in session.execute(select(User)).scalars():
    ...

This streams results instead of loading all at once.

---

Here are 20 more interview questions and answers based on the SQLAlchemy ORM Query Guide (v2.0), focusing on joins, subqueries, result processing, ORM loading strategies, and aggregation.

---

21. How do you perform a left outer join in SQLAlchemy ORM using select()?

Answer:

select(User).outerjoin(User.addresses)

---

22. What’s the purpose of using select_from() in SQLAlchemy queries?

Answer: It sets the FROM clause explicitly, useful when selecting from joined or aliased tables.

---

23. How do you alias a table or model in SQLAlchemy 2.0 ORM queries?

Answer:

from sqlalchemy.orm import aliased
user_alias = aliased(User)
select(user_alias).where(user_alias.name == "Alice")

---

24. How do you construct a subquery in SQLAlchemy ORM?

Answer:

subq = select(Address.user_id).where(Address.city == "Tokyo").subquery()
select(User).where(User.id.in_(subq))

---

25. How do you write a correlated subquery in SQLAlchemy ORM?

Answer: By using .correlate() to link subquery to outer query context.

subq = select(func.count(Address.id)).where(Address.user_id == User.id).correlate(User).scalar_subquery()
select(User.name, subq)

---

26. How do you use exists() in SQLAlchemy ORM queries?

Answer:

select(User).where(select(Address).where(Address.user_id == User.id).exists())

---

27. How do you check for NULL values in a column?

Answer:

select(User).where(User.name.is_(None))

---

28. What’s the use of with_only_columns() in a select statement?

Answer: It changes the column list in the output without modifying the FROM clause.

---

29. What is the difference between .fetchall(), .scalars().all(), and .scalar_one()?

Answer:

• .fetchall(): Returns all rows as tuples.

• .scalars().all(): Returns scalar values (like ORM objects).

• .scalar_one(): Returns exactly one scalar result, raises if not exactly one.

---

30. How do you perform a group by aggregation in SQLAlchemy ORM?

Answer:

select(User.name, func.count(Address.id)).join(User.addresses).group_by(User.name)

---

31. What does .first() return in SQLAlchemy ORM?

Answer: The first result row or None if no rows match.

---

32. Can you use ORM select() with .columns() like Core?

Answer: Yes, but in ORM queries, .columns() is usually avoided in favor of direct class references or attributes.

---

33. How do you preload related data to avoid N+1 issues?

Answer: Use eager loading with selectinload(), joinedload(), or subqueryload().

---

34. How do you use selectinload() in a query?

Answer:

from sqlalchemy.orm import selectinload
session.execute(select(User).options(selectinload(User.addresses)))

---

35. How do you load a nested relationship eagerly?

Answer:

select(User).options(selectinload(User.addresses).selectinload(Address.city))

---

36. What’s the purpose of contains_eager()?

Answer: It tells SQLAlchemy that a JOIN has already occurred and maps the joined data directly into the relationship.

---

37. How can you sort by an aggregated value like count of child rows?

Answer:

select(User, func.count(Address.id)).join(User.addresses).group_by(User.id).order_by(func.count(Address.id).desc())

---

38. What does select(...).distinct() do?

Answer: It ensures returned rows are distinct based on the columns being selected.

---

39. What’s the benefit of using session.scalars() over session.execute().scalars()?

Answer: session.scalars() is a shorthand available in SQLAlchemy 2.0 that simplifies the typical execute(...).scalars() pattern.

---

40. Can you combine ORM models and Core tables in a single query?

Answer: Yes, SQLAlchemy allows mixing ORM models and Core Table objects in queries.

---

41. How do you perform an UPDATE using SQLAlchemy ORM with select()?

Answer:

stmt = update(User).where(User.name == "Alice").values(active=True)
session.execute(stmt)
session.commit()

---

42. How do you perform a DELETE operation in SQLAlchemy ORM?

Answer:

stmt = delete(User).where(User.name == "Bob")
session.execute(stmt)
session.commit()

---

43. How do you update an ORM object directly instead of using update()?

Answer:

user = session.get(User, 1)
user.name = "NewName"
session.commit()

---

44. What’s the difference between get() and get_or_404()?

Answer: get() returns the row or None. get_or_404() is not built into SQLAlchemy—it’s typically part of frameworks like Flask-SQLAlchemy.

---

45. How do you execute a bulk INSERT with ORM-mapped objects?

Answer:

session.add_all([User(name="A"), User(name="B")])
session.commit()

---

46. What’s the advantage of using bulk_insert_mappings()?

Answer: It bypasses ORM identity management and is faster for large inserts.

session.bulk_insert_mappings(User, [{"name": "A"}, {"name": "B"}])

---

47. How can you detect whether a query is returning too many rows (performance issue)?

Answer: Use .limit() for paging, and .count() to estimate total rows. Also, monitor query plans and logs for N+1 problems.

---

48. How can you efficiently fetch only a few fields instead of whole ORM objects?

Answer:

select(User.id, User.name)

Or:

session.execute(select(User.name)).scalars()

---

49. What happens if you call .one() on a query returning multiple rows?

Answer: Raises MultipleResultsFound.

---

50. How do you avoid loading ORM entities entirely but still perform ORM-aware queries?

Answer: Use with_entities() (legacy) or specify only columns in select() to work with lightweight rows instead of full ORM objects.

---