10 SQLAlchemy Interview Questions and Answers

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides developers with the flexibility to work with databases using high-level abstractions while still allowing for fine-grained control when needed. SQLAlchemy’s versatility and efficiency make it a popular choice for managing database interactions in a wide range of applications, from small projects to large-scale enterprise systems.

This article offers a curated selection of SQLAlchemy interview questions designed to help you demonstrate your proficiency and understanding of this essential library. By reviewing these questions and their detailed answers, you will be better prepared to showcase your expertise and problem-solving abilities in any technical interview setting.

SQLAlchemy Interview Questions and Answers

1. Explain the difference between Core and ORM.

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) library for Python. It offers two main ways to interact with databases: Core and ORM.

SQLAlchemy Core:

• Provides a lower-level interface for database interaction.
• Centered around the SQL Expression Language for constructing and executing SQL statements programmatically.
• Ideal for developers needing fine-grained control over SQL queries.

SQLAlchemy ORM:

• Offers a higher-level, abstracted interface.
• Allows working with database records as Python objects, using classes to represent tables.
• Suitable for developers who prefer object-oriented programming and want to avoid raw SQL.

2. Write a basic example of how to define a table using ORM.

To define a table using SQLAlchemy’s ORM, create a class that maps to a database table. This class inherits from Base, an instance of declarative_base. Define the table name and columns within the class.

Example:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create an engine and a session
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

3. How do you establish a connection to a database? Provide an example.

To establish a connection to a database, create an engine. The engine provides database connectivity and behavior, such as connection pooling and transaction control.

Example:

from sqlalchemy import create_engine

# Create an engine that connects to the SQLite database
engine = create_engine('sqlite:///example.db')

# Connect to the database
connection = engine.connect()

# Perform database operations here

# Close the connection
connection.close()

In this example, create_engine creates an engine connecting to a SQLite database named example.db. The connect method establishes the connection, and close terminates it.

4. Describe how to perform a simple SELECT query using ORM.

To perform a simple SELECT query using SQLAlchemy’s ORM, define your database models and use session queries to retrieve data.

Example:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Define the database engine
engine = create_engine('sqlite:///example.db', echo=True)

# Define the base class for declarative models
Base = declarative_base()

# Define a simple User model
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Perform a simple SELECT query
users = session.query(User).all()

for user in users:
    print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}')

5. How would you add a new record to a table? Provide an example.

To add a new record to a table, create a session, define a model mapping to the table, and use the session to add and commit the new record.

Example:

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create an engine and a session
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

# Define a model
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Add a new record
new_user = User(name='John Doe', age=30)
session.add(new_user)
session.commit()

6. Write a query to update a record in a table using ORM.

To update a record, set up a session, query the record, modify its fields, and commit the changes.

Example:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Set up the engine and session
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query the record you want to update
user = session.query(User).filter_by(id=1).first()

# Update the fields
if user:
    user.name = 'New Name'
    user.age = 30

    # Commit the changes
    session.commit()

7. How can you define relationships between tables? Provide an example.

In SQLAlchemy, define relationships between tables using the relationship() function and foreign keys. These can be one-to-one, one-to-many, or many-to-many.

Example:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()

# One-to-Many Relationship
class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship('Child', back_populates='parent')

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parents.id'))
    parent = relationship('Parent', back_populates='children')

# Many-to-Many Relationship
association_table = Table('association', Base.metadata,
    Column('student_id', Integer, ForeignKey('students.id')),
    Column('course_id', Integer, ForeignKey('courses.id'))
)

class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    courses = relationship('Course', secondary=association_table, back_populates='students')

class Course(Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    students = relationship('Student', secondary=association_table, back_populates='courses')

# Creating the database
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

8. Describe how to use Alembic for database migrations.

Alembic is a tool for managing database migrations with SQLAlchemy. It helps maintain consistency between the database schema and application requirements.

To use Alembic:

• Install Alembic.
• Initialize Alembic in your project.
• Configure Alembic to connect to your database.
• Create migration scripts.
• Apply migrations to the database.

Example:

1. Install Alembic:

pip install alembic

2. Initialize Alembic in your project:

alembic init alembic

3. Configure Alembic by editing the alembic.ini file and setting the sqlalchemy.url to your database URL.

4. Create a new migration script:

alembic revision --autogenerate -m "create users table"

5. Apply the migration:

alembic upgrade head

9. Write a query to delete a record from a table using ORM.

To delete a record, query the record and use the session’s delete method.

Example:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

# Create an engine and a session
engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query the record you want to delete
user_to_delete = session.query(User).filter_by(id=1).first()

# Delete the record
if user_to_delete:
    session.delete(user_to_delete)
    session.commit()

10. Describe how to perform bulk inserts.

Bulk inserts in SQLAlchemy efficiently insert multiple records, reducing database round-trips and improving performance.

SQLAlchemy provides two methods for bulk inserts:

• bulk_insert_mappings: Insert multiple records using a list of dictionaries, each representing a record.
• bulk_save_objects: Insert multiple records using a list of mapped objects.

Example using bulk_insert_mappings:

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()

users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String),
              Column('age', Integer))

metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# List of dictionaries representing records
data = [
    {'id': 1, 'name': 'Alice', 'age': 30},
    {'id': 2, 'name': 'Bob', 'age': 25},
    {'id': 3, 'name': 'Charlie', 'age': 35}
]

session.bulk_insert_mappings(users, data)
session.commit()

Example using bulk_save_objects:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

Base.metadata.create_all(engine)

# List of mapped objects
users = [
    User(id=1, name='Alice', age=30),
    User(id=2, name='Bob', age=25),
    User(id=3, name='Charlie', age=35)
]

session.bulk_save_objects(users)
session.commit()