5. Interview Questions- Using the Session

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

---

1. What is the purpose of the Session in SQLAlchemy ORM?

Answer: The Session is the ORM’s interface to the database. It manages object persistence, transactions, and connection resources.

---

2. How do you create a new session in SQLAlchemy 2.0?

Answer:

from sqlalchemy.orm import Session

with Session(engine) as session:
    ...

---

3. What is the benefit of using a with block for Session?

Answer: It ensures that the session is closed automatically, even if an error occurs—helping manage resources safely.

---

4. How do you persist a new object using the session?

Answer:

new_user = User(name="Alice")
session.add(new_user)
session.commit()

---

5. What’s the difference between add() and add_all() in a session?

Answer:

• add(obj): Adds a single object to the session.

• add_all([obj1, obj2]): Adds multiple objects in one call.

---

6. What does session.commit() do?

Answer: It flushes all pending changes to the database and commits the current transaction.

---

7. What’s the difference between session.commit() and session.flush()?

Answer:

• flush(): Sends SQL to the DB but doesn’t commit the transaction.

• commit(): Flushes changes and commits the transaction.

---

8. What does session.rollback() do?

Answer: It rolls back the current transaction, reverting uncommitted changes in the database and expiring ORM objects.

---

9. What happens if you close a session with session.close()?

Answer: It ends the session’s connection to the DB. ORM objects remain usable but detached (can’t issue new queries).

---

10. What is the difference between a “dirty” and a “new” object in the session?

Answer:

• New: Not yet flushed to the DB (session.new).

• Dirty: Modified but not committed (session.dirty).

---

11. How do you delete an object from the database?

Answer:

session.delete(obj)
session.commit()

---

12. What is expire_on_commit=True and when should you disable it?

Answer: It invalidates all ORM objects on commit(). You may disable it when you want objects to retain values after commit.

Session(engine, expire_on_commit=False)

---

13. How can you inspect the state of the session?

Answer:

session.new      # Unflushed new objects
session.dirty    # Modified objects
session.deleted  # Objects marked for deletion

---

14. What happens if you access a flushed but uncommitted object’s attribute?

Answer: You get the latest value; SQLAlchemy automatically populates values returned by the flush (e.g., autogenerated IDs).

---

15. Can you reuse a session across multiple requests in a web app?

Answer: No. Sessions should be short-lived and scoped to a single unit of work or request.

---

16. How do you refresh an object with values from the DB?

Answer:

session.refresh(obj)

---

17. What is the autoflush parameter in a session?

Answer: It controls whether SQLAlchemy flushes changes automatically before a query. Defaults to True.

Session(engine, autoflush=False)

---

18. How do you merge a detached instance back into a session?

Answer:

session.merge(detached_obj)

---

19. What does session.get(Model, primary_key) do?

Answer: It retrieves an object by primary key or returns None if not found.

---

20. What is the binds parameter in session configuration?

Answer: It allows you to map different tables to different engines within the same session.

Session(binds={User: user_engine, Order: order_engine})

---

Here are 20 more SQLAlchemy ORM interview questions and answers based on the Session documentation (v2.0), focusing on identity management, advanced session operations, scoping, and transactional behavior.

---

21. What is the identity map in a SQLAlchemy session?

Answer: It's an in-memory cache where each object is keyed by its primary key. Within a session, the same object is never loaded twice.

---

22. What happens when you query the same object twice within one session?

Answer: The session returns the same Python object instance from its identity map.

---

23. How do you remove an object from the session without deleting it from the database?

Answer:

session.expunge(obj)

---

24. What does session.clear() do?

Answer: It removes all objects from the session, including pending changes and identity tracking.

---

25. What is a detached object in SQLAlchemy?

Answer: An object that was once attached to a session but is no longer — either because the session was closed or it was expunged.

---

26. What happens if you modify a detached object and call session.commit()?

Answer: Nothing unless you add() or merge() it back into the session first.

---

27. How can you tell if an object is persistent, pending, or detached?

Answer:

from sqlalchemy.orm import inspect
inspect(obj).persistent   # True if in DB and attached to session
inspect(obj).pending      # True if new and not yet flushed
inspect(obj).detached     # True if not attached to any session

---

28. What’s the difference between Session.commit() and Session.flush() in terms of transaction boundaries?

Answer:

• flush() sends SQL but does not commit the transaction.

• commit() ends the current transaction and starts a new one.

---

29. Can you roll back only part of a session's changes?

Answer: No. SQLAlchemy rolls back the entire transaction. You’d need separate transactions or savepoints to isolate changes.

---

30. What is a sessionmaker and why is it useful?

Answer:

from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(bind=engine)

It’s a factory that produces new Session instances, useful for consistent session configuration.

---

31. What is the difference between Session() and sessionmaker()?

Answer:

• Session() creates a session directly.

• sessionmaker() is a configurable session factory, ideal for reuse.

---

32. How do you manually bind a Session to a specific engine?

Answer:

session = Session(bind=engine)

---

33. What’s the recommended practice for session management in web apps?

Answer: Use short-lived sessions tied to request/response cycles, often managed via dependency injection or middleware.

---

34. Can a session span multiple transactions?

Answer: Yes, a session can commit/rollback multiple times. Each commit or rollback defines a new transaction.

---

35. What happens if you try to use a session after it's been closed?

Answer: Most operations will fail with an error, especially queries and flushes.

---

36. What is autocommit and should it be used?

Answer: autocommit=True is deprecated in SQLAlchemy 2.0. Explicit transaction control is the preferred approach.

---

37. Can multiple threads share the same session instance?

Answer: No. Sessions are not thread-safe. Use a separate session per thread or request.

---

38. How does Session.begin() differ from using with Session()?

Answer:

with Session(engine) as session:
    with session.begin():
        ...

session.begin() defines an explicit transaction block within an existing session scope.

---

39. How do you execute raw SQL inside a session?

Answer:

session.execute(text("SELECT * FROM users WHERE id=:id"), {"id": 1})

---

40. What’s the difference between commit() and flush() when using add()?

Answer:

• add() stages the object.

• flush() sends it to the DB.

• commit() flushes + ends the transaction.

---

41. What does Session.begin() do?

Answer: It begins a new transaction explicitly. Used when you want to control the transaction scope inside a longer session lifecycle.

with session.begin():
    ...

---

42. Can you nest transactions in SQLAlchemy ORM sessions?

Answer: Yes, using savepoints via session.begin_nested().

with session.begin_nested():
    ...

---

43. What is the use of Session.in_transaction()?

Answer: It checks if the session is currently within a transaction block.

session.in_transaction()  # Returns a Transaction object or None

---

44. What is the Session.get_bind() method used for?

Answer: Returns the Engine or Connection associated with the session or for a specific ORM-mapped class.

---

45. How does SQLAlchemy determine which engine to use for a query?

Answer: Via the bind associated with the session or from binds configured during Session construction.

---

46. Can you change the bind for a session at runtime?

Answer: Yes, using execution_options or by manually providing bind= to operations like Session.execute().

---

47. What does Session.is_active represent?

Answer: It returns True if the session has not been rolled back or invalidated due to errors.

---

48. What happens if an exception occurs during a session’s transaction block?

Answer: The session automatically rolls back the transaction and the session remains usable.

---

49. When should you use Session.rollback() explicitly?

Answer: When an exception occurs outside a context manager or when you want to discard uncommitted changes manually.

---

50. Why is using with Session(engine) as session: considered best practice?

Answer: It ensures:

• Automatic cleanup with .close()

• Safe transaction rollback

• Clear scoping of database work

---