1. Interview Questions -SQLAlchemy ORM
source:
https://docs.sqlalchemy.org/en/20/orm/index.html
1. What is the purpose of DeclarativeBase in SQLAlchemy ORM?
DeclarativeBase in SQLAlchemy ORM?Answer: DeclarativeBase is a base class from which all ORM models inherit. It provides the foundational mapping capability to link Python classes with database tables.
2. How do you define a table name in an ORM model?
Answer: Use the __tablename__ attribute in the model class to define the table name.
class User(Base):
__tablename__ = "user_account"3. What import is required to define mapped columns in ORM models?
Answer: You should import Mapped and mapped_column from sqlalchemy.orm.
from sqlalchemy.orm import Mapped, mapped_column4. How do you define a primary key column in a model?
Answer: Use mapped_column(primary_key=True) to mark the column as a primary key.
id: Mapped[int] = mapped_column(primary_key=True)5. How can you define a column with a specific string length?
Answer: Use mapped_column(String(length)).
6. What is the advantage of using Mapped over plain type annotations in SQLAlchemy 2.0?
Mapped over plain type annotations in SQLAlchemy 2.0?Answer: Mapped provides type checking and helps SQLAlchemy understand that the field is part of the ORM mapping.
7. How do you declare a relationship between two models?
Answer: Use the relationship() function within the model.
8. What is back_populates used for in relationships?
back_populates used for in relationships?Answer: It sets up a bidirectional relationship by linking two related fields from different models.
9. How do you represent a foreign key in an ORM model?
Answer: Use mapped_column(ForeignKey("table.column")).
10. How do you define a string column with no length limit?
Answer: Simply use Text or String without specifying a length.
11. How do you create the tables defined by ORM models in the database?
Answer: Call Base.metadata.create_all(engine).
12. What does metadata.create_all() do?
metadata.create_all() do?Answer: It generates the SQL schema (CREATE TABLE statements) based on the ORM models.
13. How do you configure the base class to use a metadata object?
Answer: By inheriting from DeclarativeBase, which already includes metadata.
14. What is the purpose of Mapped[List[...]] in relationship fields?
Mapped[List[...]] in relationship fields?Answer: It indicates a one-to-many relationship, where the current model owns a list of related models.
15. Can you use forward declarations for models in relationships?
Answer: Yes, by quoting the class name as a string, e.g., "Address".
16. How does SQLAlchemy map columns to class attributes?
Answer: By using Mapped annotations and mapped_column definitions inside a model class.
17. Is the use of __init__ required when defining ORM models?
__init__ required when defining ORM models?Answer: No, SQLAlchemy can automatically generate the constructor based on the fields.
18. What is the role of Base.metadata?
Base.metadata?Answer: It holds all the schema-level objects like tables and constraints and is used to emit schema DDL.
19. How do you specify a nullable column?
Answer: By setting nullable=True (default), or nullable=False for non-nullable.
20. How do relationships handle collections by default?
Answer: By default, a relationship with List[...] maps to a Python list of related objects.
Here’s another set of 20 SQLAlchemy ORM interview questions and answers, continuing from the same SQLAlchemy ORM Quickstart v2.0 page, focused more on deeper and nuanced concepts:
21. What does ForeignKey("user_account.id") refer to in a mapped column?
ForeignKey("user_account.id") refer to in a mapped column?Answer: It specifies that the column is a foreign key referencing the id column of the user_account table.
22. Can a model have multiple foreign keys?
Answer: Yes, a model can have multiple foreign keys referencing one or more different tables.
23. How do you define a one-to-many relationship in SQLAlchemy?
Answer: Use relationship() in the parent model and a foreign key in the child model.
24. What happens if you omit back_populates in relationships?
back_populates in relationships?Answer: The relationship will still work unidirectionally, but bidirectional updates and synchronization won’t happen automatically.
25. What’s the difference between backref and back_populates?
backref and back_populates?Answer: backref auto-creates the reverse relationship, while back_populates requires explicit declaration in both models.
26. What does the String(30) in a column mean?
String(30) in a column mean?Answer: It defines a column of type VARCHAR with a maximum length of 30 characters.
27. Can you define constraints like unique=True on columns?
unique=True on columns?Answer: Yes, mapped_column(..., unique=True) adds a uniqueness constraint to that column.
28. How do you define default values for columns in ORM models?
Answer: Use the default parameter in mapped_column.
29. How can you enforce non-null constraints in a model field?
Answer: Use nullable=False in the column definition.
30. What data types can you use in mapped_column?
mapped_column?Answer: Common ones include Integer, String, Text, Boolean, Date, DateTime, Float, etc.
31. What does Mapped[int] indicate to SQLAlchemy?
Mapped[int] indicate to SQLAlchemy?Answer: It tells SQLAlchemy that the attribute is a mapped column of type int.
32. How are Python classes linked to SQL tables in SQLAlchemy ORM?
Answer: By inheriting from a base class (Base) and using __tablename__ along with column mappings.
33. What would happen if two models used the same __tablename__?
__tablename__?Answer: A conflict would occur during metadata creation, likely resulting in an error.
34. Can you mix traditional SQLAlchemy Core with ORM?
Answer: Yes, SQLAlchemy allows mixing ORM and Core features in the same application.
35. How does SQLAlchemy handle pluralization or table name inference?
Answer: It does not infer table names — you must explicitly set __tablename__.
36. What is the default collection type for relationships in SQLAlchemy ORM?
Answer: Python list.
37. What is the purpose of the DeclarativeBase metaclass?
DeclarativeBase metaclass?Answer: It tracks all models and generates metadata used to emit DDL statements like CREATE TABLE.
38. Can you extend multiple base classes for models in SQLAlchemy ORM?
Answer: No, models should inherit from a single declarative base to avoid conflicts.
39. Why are string-based annotations (e.g., "Address") used in relationships?
"Address") used in relationships?Answer: To enable forward references when the related class has not yet been defined.
40. What does Base.metadata.create_all(engine) actually use to build the schema?
Base.metadata.create_all(engine) actually use to build the schema?Answer: It uses the __table__ definitions attached to each model and executes the appropriate SQL via the provided engine.
41. Can you define a composite primary key using DeclarativeBase?
DeclarativeBase?Answer: Yes, by using multiple mapped_column(..., primary_key=True) in the model.
42. How do you map a class without defining __tablename__?
__tablename__?Answer: You must define __tablename__; otherwise, the class won't map to a table, and SQLAlchemy will raise an error during metadata creation.
43. What happens if Mapped is not used in column type hints?
Mapped is not used in column type hints?Answer: SQLAlchemy will not treat the attribute as part of the ORM mapping; it will be ignored.
44. How can you access all defined tables in your ORM setup?
Answer: Use Base.metadata.tables, which returns a dictionary of all mapped table names and their Table objects.
45. Is it necessary to use relationship() on both sides of a relation?
relationship() on both sides of a relation?Answer: No, but it is highly recommended if you need bidirectional navigation and synchronization.
46. How do you handle circular imports in relationship declarations?
Answer: Use a string (e.g., "User") to declare the related class in relationship() to avoid direct reference.
47. Can Mapped be used with generic types like Optional or List?
Mapped be used with generic types like Optional or List?Answer: Yes, such as Mapped[Optional[str]] or Mapped[List["ModelName"]].
48. How do you avoid automatic pluralization of relationships?
Answer: SQLAlchemy does not auto-pluralize by default. You control naming manually.
49. What does the __init__ method do in SQLAlchemy ORM models?
__init__ method do in SQLAlchemy ORM models?Answer: If not explicitly defined, SQLAlchemy auto-generates __init__ to accept mapped columns as keyword arguments.
50. How do you associate a model with an existing table that was already created in the DB?
Answer: Use __table__ = Table(...) instead of __tablename__, and map the existing table via reflection or manual table definition.
Last updated