SQLite is a pragmatic choice when an application needs to store a small question pool without the setup and operational overhead of MongoDB or PostgreSQL. A local database file keeps the practice-test data close to the Python application while leaving room to add related tables later. section 3.6 section 5.3.1
Set up a questions database with a short, testable workflow:
- Create a new database file in DB Browser for SQLite, then import the downloaded CSV as a table named questions. section 5.3.1
- Verify CSV parsing before import: use comma separation, quote characters, UTF-8 encoding, column names, and trimmed fields. section 5.3.1
- Mark the id field as primary and unique so it can be indexed and used to tie in later tables. section 5.3.1
- Browse the imported rows before writing application code; confirm the stored question data matches expectations. section 5.3.1
Table 5.1 Format of our questions data. It maps directly to a database table with this information.
| id |
correct |
question |
a |
b |
c |
d |
| T1A01 |
2 |
Question? |
possible answer |
possible answer |
possible answer |
possible answer |
The imported CSV maps directly to a questions table: each row carries an id, correct-answer value, question text, and four answer choices. Inspect this shape before building queries around it. section 5.3.1
Fetch SQLite data in a Flask route
Keep the database path explicit, open the connection through a context manager, pass its cursor to a Questions object, and fetch rows before rendering the template. This turns a raw database dump into data the page can use while ensuring the connection closes after retrieval. section 5.3.5 section 5.3.5
When generating connection code with AI assistance, provide the exact database type and file path rather than a vague request. A prompt such as “connect to a SQLite database; the file name is data/questions.db” gives the tool useful context, but inspect its output: an initial suggestion may query an unrelated users table or require correction. For larger changes, use a dialog that supplies enough project context, then test and revise the generated code. section 5.3.2 section 5.3.2
SQLite fits an MVP here because the dataset is small and does not require complex operations or extensive safety measures. That is a scope decision, not a claim that larger systems should avoid MongoDB or PostgreSQL; either remains a viable alternative when requirements change. section 3.6
