34 questions · Database Access · All types, answers revealed
You are using sqlite3 in Python and need to ensure that data integrity is maintained when performing multiple related INSERT operations. Which method should you call on the connection object to commit your changes?
commit() is the standard method to finalize a transaction in sqlite3.
Why this answer
The commit() method is used to save the current transaction to the database.
Which Python library is the standard built-in interface for SQLite databases?
sqlite3 is the standard library module.
Why this answer
The 'sqlite3' module is part of the Python standard library.
How do you retrieve the number of documents matched by a PyMongo 'find' operation without fetching all documents into memory?
count_documents is the modern, non-deprecated method.
Why this answer
The count_documents() method on a collection is the recommended way to get a count.
Which of the following best describes the benefit of using parameterized queries in sqlite3?
Parameterized queries ensure input is treated as data, not code.
Why this answer
Parameterized queries prevent SQL injection by separating the query logic from the data.
In sqlite3, how do you handle a database error such as a constraint violation?
sqlite3 raises specific exceptions for database errors.
Why this answer
You should wrap database operations in a try-except block and catch sqlite3.Error or its subclasses.
Which TWO of these are valid parameters for the MongoClient constructor?
The host parameter is valid.
Why this answer
MongoClient accepts host and port, or a connection string.
What is the purpose of the 'upsert=True' option in MongoDB update operations?
Upsert is a combination of update and insert.
Why this answer
If no document matches the query, a new document is created with the update criteria.
You are implementing a custom type in SQLAlchemy. Which method must be overridden to convert data from the database type back to a Python object?
This converts DB results into Python objects.
Why this answer
The process_result_value method is responsible for type conversion during retrieval.
Which object in SQLAlchemy is responsible for maintaining a collection of loaded objects and managing their lifecycle?
The session tracks object states like 'persistent' and 'dirty'.
Why this answer
The Session object acts as a workspace for objects and tracks their state.
You are using SQLAlchemy with an asynchronous driver (asyncio). Which object must be used to perform database operations asynchronously?
AsyncSession supports awaitable methods for database access.
Why this answer
AsyncSession is the required class for asynchronous session management in SQLAlchemy.
Which THREE of these are valid relationship loading strategies in SQLAlchemy?
Subquery loading uses a separate query with a subselect.
Why this answer
lazy, joined, and subquery are three common loading strategies.
In SQLAlchemy, what does the 'metadata.create_all(engine)' command do?
This is the primary function of create_all.
Why this answer
It inspects the defined ORM models and emits CREATE TABLE statements to the database.
When using the sqlite3 module, what is the primary purpose of using a context manager (the 'with' statement) on a connection object?
The 'with' statement handles transaction control automatically.
Why this answer
In sqlite3, the connection context manager automatically commits or rolls back transactions.
Which TWO are common pitfalls when working with SQLite databases in a multi-threaded environment?
Standard sqlite3 connections are not thread-safe.
Why this answer
SQLite connections are not thread-safe by default, and lock contention can occur.
Which THREE are valid cascade options in SQLAlchemy?
Standard cascade.
Why this answer
delete, save-update, and merge are common cascade operations.
Which THREE operations are supported by the PyMongo 'bulk_write' method?
DeleteOne is a valid bulk write operation.
Why this answer
bulk_write handles InsertOne, UpdateOne, and DeleteOne operations.
Which THREE methods are part of the PEP 249 Python DB-API 2.0 interface?
Standard method on connection.
Why this answer
execute, fetchone, and commit are standard parts of the DB-API specification.
Which THREE features are provided by SQLAlchemy's Unit of Work pattern?
Identity maps keep object uniqueness per session.
Why this answer
The Unit of Work pattern manages changes to objects and ensures they are flushed to the DB in the correct order.
In SQLAlchemy, how do you prevent an object from being saved to the database during a session.commit()?
expunge removes the object from the session's persistence management.
Why this answer
You can use session.expunge(obj) to remove the object from the session's management.
Which THREE are characteristics of BSON (Binary JSON) as used by MongoDB?
The binary structure allows for fast parsing.
Why this answer
BSON is binary-encoded, supports more types than JSON, and is optimized for traversal.
Which TWO are common methods to handle BSON Date objects in PyMongo?
PyMongo handles datetime natively.
Why this answer
datetime.datetime objects are automatically converted to BSON Dates.
Which PyMongo method should be used to update a single document if you want to modify specific fields without replacing the entire document?
update_one uses operators like $set to modify fields.
Why this answer
The update_one method with the $set operator allows updating specific fields.
In a PyMongo application, what happens when you use 'insert_many' with 'ordered=False'?
Unordered inserts continue processing even if one document fails.
Why this answer
If 'ordered' is False, the driver continues to insert subsequent documents even if one fails.
Which TWO of the following are valid ways to execute a raw SQL query in SQLAlchemy?
Engine execute is a common way for raw SQL execution.
Why this answer
SQLAlchemy allows executing raw SQL via text() with engine.execute() or session.execute().
When connecting to a MongoDB instance using PyMongo, what is the role of the 'MongoClient' object?
MongoClient manages the connection pool and authentication.
Why this answer
The MongoClient acts as the entry point to the MongoDB server, allowing access to databases and collections.
When configuring a connection pool in SQLAlchemy, what does the 'pool_size' parameter define?
pool_size controls the connection pool capacity.
Why this answer
pool_size defines the number of persistent connections to keep open in the pool.
In SQLAlchemy, what is the difference between 'lazy='select'' and 'lazy='joined'' loading strategies?
Lazy loading (select) emits a new query on access.
Why this answer
joined loading performs an outer join to fetch related objects in the same query, whereas select loading fetches them only when accessed.
You are developing an application using SQLAlchemy and need to retrieve a single object by its primary key. Which method is preferred for this operation?
session.get() is designed specifically for primary key lookups.
Why this answer
The get() method on the Session object is the optimized way to retrieve an object by its primary key.
When using the MongoDB aggregation framework in PyMongo, which stage should be used to filter documents based on a condition?
$match is the standard filtering stage.
Why this answer
The $match stage is used to filter documents in an aggregation pipeline.
When performing bulk inserts in PyMongo, which method is the most efficient?
insert_many is optimized for bulk operations.
Why this answer
insert_many is designed to send multiple documents in a single batch to the server.
You are using SQLAlchemy's relationship() function. What does 'back_populates' achieve?
It enables bidirectional synchronization in the ORM.
Why this answer
It links two sides of a relationship so that changes in one are reflected in the other via the session's identity map.
Which command-line tool is typically used to inspect the contents of a SQLite database file?
sqlite3 provides the CLI tool for interaction.
Why this answer
The 'sqlite3' command-line interface is the standard tool to interact with .db files.
When using SQLAlchemy Declarative Base, how do you define a table name that differs from the class name?
This is the required attribute for table mapping.
Why this answer
The __tablename__ attribute is used to explicitly map a class to a specific table name.
In SQL, which clause is used to filter results based on a condition after grouping?
HAVING is for post-group filtering.
Why this answer
The HAVING clause filters aggregated data, whereas WHERE filters raw data.
Ready to test yourself?
Try a timed practice session using only Database Access questions.