Storing Data with SQLite

In previous lessons, we used text files and JSON to store and exchange data. These formats are portable and human-readable, which makes them useful for configuration data, logging, and lightweight storage. However, they become less efficient as data volume increases. Retrieving a single value from a large JSON file often requires loading the entire file into memory. As datasets grow, this approach becomes inefficient and increases memory and processing costs.

In addition, plain text formats do not strictly enforce data types or structural constraints. A field intended to store numeric values may contain a string, and there is no built-in mechanism to prevent such inconsistencies. Over time, this can lead to data integrity issues that are difficult to detect and correct.

This is where a database becomes essential. In this lesson, we will use SQLite, a powerful relational database engine built directly into Python. Unlike traditional client–server database systems that require separate installation and configuration, SQLite stores all data within a single file on disk. Despite its simplicity, it supports features such as structured schemas, enforced data types, indexed queries, and efficient record retrieval using SQL.

Why SQLite?

When data is stored in CSV or JSON files, it is typically organized as a flat file. To locate a user with a specific ID, the program must scan the file sequentially until it finds a matching record. This approach becomes slower as the file size increases.

SQLite fundamentally changes this model. Instead of treating data as a linear stream of text, it stores information in ...