Use SQLite when a Python tool needs persistent records and queries on one machine. Python's sqlite3 module connects to a local database file without running a separate server. Tables define the records, queries select them, and transactions let you apply related changes together.
Use a database when relationships matter
A JSON file is often enough for a single report. A database becomes useful when you need to ask repeated questions about a growing history: which source produced this record, when it was first observed, or which run changed it.
Start with a small schema. A practice reading log might have a source table and an observations table linked by a source ID. Choose names that describe the meaning of each row. Avoid a single table with dozens of unrelated fields merely because it is possible.
Pass query values as parameters
Pass external values through placeholders rather than inserting them into SQL text. In Python, connection.execute("SELECT title FROM items WHERE id = ?", (item_id,)) keeps the value separate from the statement.
Placeholders are for values, not arbitrary table names or SQL keywords. Keep those structural choices under application control. If a user chooses a sort order, map it to a small approved set rather than pasting raw input into a query.
Treat a transaction as a complete change
Some updates belong together. When recording a new observation and its revision, either both should succeed or neither should remain. A transaction lets you express that boundary and roll back incomplete work.
Enable foreign keys when your schema relies on them. Give related records explicit keys rather than hoping that matching text fields always describe the same entity. Test that invalid relationships are rejected.
Understand the files around the database
SQLite may use journal or write-ahead-log sidecar files, depending on its configuration. The account running the project needs appropriate access to the containing folder. A read/write database on a live synchronized folder can create complications that a beginner does not need.
Use the SQLite backup interface for a consistent backup while the database is in use. Do not assume that copying only the main file during active writes captures the whole current state. Test restoration into a separate copy before relying on a backup procedure.
A good beginner exercise
Create three synthetic source records and record two collection runs. Query the history by source and date, then deliberately attempt an invalid relationship. Finally, back up the database and inspect the restored copy. This teaches persistence and integrity without requiring a server or sensitive data.
For a change that writes several related rows, read how SQLite transactions commit and roll back as a unit.