SQLAlchemy Questions & Answers

 

1. What is SQLAlchemy?

Answer:

SQLAlchemy is a Python library used for interacting with databases. It provides:

• ORM (Object Relational Mapping)
• Core (SQL Expression Language)

It allows developers to write Python code instead of raw SQL.

2. What is ORM?

Answer:

ORM (Object Relational Mapping) maps:

• Tables → Python classes
• Rows → Python objects

Example:

user = User(name="John")

Instead of writing SQL:

INSERT INTO users VALUES ('John');

3. What is an Engine?

Answer:

• Entry point to the database
• Manages DB connections

engine = create_engine("sqlite:///test.db")

4. What is a Session?

Answer:

• A session is a workspace for DB operations
• Handles transactions and queries

5. Difference between flush() and commit()?

Answer:

Feature	flush()	commit()
Saves to DB	Yes	Yes
Permanent	No	Yes
Can rollback	Yes	No

6. What is declarative_base()?

Answer:

Used to create a base class for ORM models.

Base = declarative_base()


7.Difference between Core and ORM?
Answer:
Feature
 Core
ORM
Level
             Low
      High
Syntax
             SQL-like
      Python classes
Use Case
             Complex queries
      App logic
8.What is Lazy Loading?
Answer:
Loads related data only when accessed
                user.orders  # triggers query
9. What is Eager Loading?
Answer:
Loads related data in advance
from sqlalchemy.orm import joinedload
session.query(User).options(joinedload(User.orders)).all()
10.What is the N+1 Query Problem?
Answer:
One query for main data
N additional queries for related data
Fixed using eager loading.
11.What is relationship()?
Answer:
Defines relationships between tables.
orders = relationship("Order")
12.What is a Foreign Key?
Answer:
Links two tables
user_id = Column(Integer, ForeignKey("users.id"))
13. What is Alembic?
Answer:
Migration tool for SQLAlchemy
Manages schema changes
14.What is a Transaction?
Answer:
A group of DB operations executed as a single unit.
try:
    session.commit()
except:
    session.rollback()
15.Explain Session Lifecycle
Answer:
Create session
Perform operations
Commit/Rollback
Close session
16.What are different object states?
Answer:
Transient
Pending
Persistent
Detached
17.What is scoped_session?
Answer:
Provides thread-safe session handling
Used in web apps
18.How to optimize SQLAlchemy performance?
Answer:
Use indexes
Avoid N+1 queries
Use eager loading
Select required columns
Use bulk operations
19.What is connection pooling?
Answer:
Reuses DB connections
Improves performance
20.Difference between first() and one()?
Answer:
Method
Behavior
first()
        Returns first or None
one()
        Returns exactly one or error
21.What is expire_on_commit=False?
Answer:
Prevents object data from expiring after commit
Useful in async operations
22. Sync vs Async SQLAlchemy?
Answer:
Feature
        Sync
            Async
Blocking
        Yes
            No
Performance
        Good
            High

Coding Questions
1.Create a User Table
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
2.Insert Data
user = User(name="John")
session.add(user)
session.commit()
3.Fetch Data
session.query(User).filter(User.name=="John").all()
4.One-to-Many Relationship
orders = relationship("Order", back_populates="user")

Scenario-Based Questions
5.How do you handle high traffic API?
Answer:
Use async SQLAlchemy
Connection pooling
Query optimization
6.How do you fix slow queries?
Answer:
Add indexes
Use eager loading
Optimize joins
How do you manage schema changes?
Answer:
Use Alembic migrations