SQLite Integration Fundamentals with Python Quiz

Explore the essentials of integrating SQLite databases with Python, focusing on key concepts such as connections, queries, data types, and common operations. This quiz helps reinforce foundational skills necessary for efficient data management using SQLite in Python applications.

1. Establishing a SQLite Connection

   Which Python standard library module should you import to connect to a SQLite database file named 'inventory.db'?

   1. sqlite3
   2. sqlite
   3. mysqlite
   4. sqllite

   Explanation: The correct answer is sqlite3, which is the standard library module used to interact with SQLite databases in Python. Options like mysqlite and sqlite are either incorrect names or do not exist as standard modules, and sqllite is simply a misspelling. Only sqlite3 is recognized and used widely for SQLite database integration.

2. Creating and Using Cursors

   After establishing a connection with SQLite in Python, what method should you call on the connection object to execute SQL commands?

   1. openCursor()
   2. runQuery()
   3. cursor()
   4. executeSQL()

   Explanation: You should use the cursor() method to obtain a cursor object, which is needed to execute SQL statements. The executeSQL() and runQuery() methods are not part of the standard library interface, and openCursor() is not a valid method for this purpose. The cursor() method is essential for command execution and result retrieval.

3. Inserting Data Securely

   When inserting data into a SQLite table using Python, which approach helps prevent SQL injection risks?

   1. Writing SQL commands in uppercase letters
   2. Concatenating user input directly into the SQL string
   3. Formatting the query string with %s placeholders only
   4. Using parameterized queries with placeholders

   Explanation: Parameterized queries with placeholders safely separate query logic from user input, preventing SQL injection attacks. Concatenating or formatting user input directly into queries is insecure, and simply using uppercase letters has no effect on security. The placeholder method is the most effective and standard practice.

4. Executing Select Queries

   Which method on the cursor object retrieves all rows from the result of a SELECT query in Python?

   1. fetchone()
   2. fetchall()
   3. getall()
   4. readrows()

   Explanation: The fetchall() method returns all remaining rows in a query result set as a list. fetchone() retrieves a single row, so it's not suitable when multiple rows are expected. The getall() and readrows() methods do not exist in the standard cursor interface. Using fetchall() is the accepted approach for gathering all results.

5. Committing Changes to Database

   Which method should you call on your SQLite connection object to permanently save any INSERT, UPDATE, or DELETE operations?

   1. applyChanges()
   2. commit()
   3. finalize()
   4. save()

   Explanation: The commit() method is required to make changes permanent in the database after write operations. Methods like applyChanges(), save(), or finalize() do not exist for this purpose in the connection object. Not calling commit() may result in losing changes after the connection is closed.

6. Supported Data Types

   Which of the following is a valid column data type in SQLite when creating a table?

   1. STRING
   2. INTEGER
   3. DATETIME
   4. FLOATING

   Explanation: SQLite supports the data type INTEGER for storing whole numbers in a table. STRING and FLOATING are not recognized by SQLite as formal data types, and DATETIME is also not a built-in type, although date and time values can be stored as TEXT, REAL, or INTEGER. INTEGER is correct for numeric columns.

7. Context Manager Usage

   Which Python statement ensures that your SQLite connection is closed automatically after database operations are complete?

   1. for sqlite3.connect('data.db') as conn:
   2. open('data.db') as conn:
   3. with sqlite3.connect('data.db') as conn:
   4. while sqlite3.connect('data.db') as conn:

   Explanation: Using the with statement and sqlite3.connect creates a context manager, ensuring the connection is closed automatically. open() is used for file handling, not databases, and while and for statements are not appropriate for establishing and managing database connections. The with statement is both safe and convenient.

8. Handling Database File Absence

   What happens if you attempt to connect to a SQLite database file that does not exist using sqlite3.connect in Python?

   1. An error is raised and the program stops
   2. A new, empty database file is created automatically
   3. Data is written to a temporary in-memory database
   4. The system prompts for file creation permission

   Explanation: If the specified file does not exist, SQLite creates a new empty database file without raising an error. No prompt or permission is required, and the database is not created in memory unless specifically requested. Only automatic file creation occurs with the default method.

9. Accessing Query Results by Column Name

   How can you retrieve values by column name rather than by index from a row returned by a SELECT statement in SQLite with Python?

   1. By using the fetchdict() method of the cursor
   2. By naming columns in uppercase only in your query
   3. By setting the connection's row_factory to sqlite3.Row
   4. By casting results to a dictionary with dict()

   Explanation: Setting row_factory to sqlite3.Row allows you to access columns by name from the returned rows. The fetchdict() method does not exist in the standard module, and simply using uppercase column names does not change access behavior. Casting the results directly to a dictionary with dict() does not work unless row_factory is set properly.

10. Closing the Cursor Object

    After finishing database operations in Python, what is the recommended way to properly release resources associated with a cursor?

    1. Assign None to the cursor variable
    2. Use del cursor to delete the variable
    3. Restart the application
    4. Call the close() method on the cursor

    Explanation: Explicitly calling the close() method on the cursor ensures that resources are freed properly. Using del or assigning None only removes the variable reference and does not guarantee underlying resources are released. Restarting the application is unnecessary for cleanup. The close() method is the appropriate approach.