Creating and Modifying Database Tables
A database is a persistent, disk-based file that stores data as key-value pairs, unlike a dictionary which lives in RAM and disappears when the program ends.
When the Program Ends
Imagine an application that tracks millions of customer records. A Python dictionary may seem attractive because it keeps keys and values in memory and provides quick access while the program is running. However, the dictionary lives in RAM. When the program ends, its contents disappear. Restarting the application means rebuilding the entire dictionary.
The central difference is persistence. A database is a disk-based file intended to retain data after the application ends, while a dictionary is held in RAM and disappears when the program ends.
Capacity Beyond Memory
Persistence is only one reason to use a database. A computer has a finite amount of RAM, and a dictionary lives entirely within that memory. If the data grows beyond the available memory, the program cannot keep all of it in the dictionary. Disk storage is far larger than computer memory, so databases can store vastly more data than dictionaries. This makes a database suitable for accumulated records, very large collections, and data that must remain available across program sessions.
Choosing Storage for Student Records
A student information application must retain records between launches and may grow to thousands of records. Should the records live only in a Python dictionary?
Check persistence: A dictionary would lose its records when the application ends, so it would not meet the requirement that records remain available.
Check capacity: The records would occupy RAM. As the collection grows, the fixed amount of available memory becomes a limitation.
Choose storage: A disk-based database addresses both requirements: it persists the records and supports substantially more data than memory alone.
Use a database rather than relying only on an in-memory dictionary.
How Indexing Avoids a Full Scan
Disk storage is slower to read than RAM, so persistence alone would not make a database useful if every lookup required checking every record. Database software addresses this with indexing. An index is a special data structure that acts like a roadmap: it organizes identifying values, such as customer IDs, so the system can move toward the requested record instead of scanning every row.
One Million Customer Records
A table contains one million customer records, each identified by a customer ID. Compare a lookup without an index with a lookup using an index.
Without an index: The database may need to read through records one by one until it finds the requested customer ID. In the worst case, this means checking all one million records.
With an index: The customer IDs are organized in a special structure, described in the source as often tree-like. Each comparison can eliminate much of the remaining search.
Result: The lookup changes from a slow linear search through the records to a much faster logarithmic search. The source illustrates this contrast as potentially about one million reads versus about 20 reads.
Indexing lets the database jump toward the requested record rather than scanning every record.
Indexing does not turn disk into RAM. It makes disk-based retrieval practical by reducing the amount of data the database must examine.
SQLite in Python Applications
SQLite is an embedded database system built into Python. It is designed for applications that need reliable data persistence without the complexity of operating a separate database server. In a Python application, the program can work with an SQLite database stored locally in the application environment. The application exchanges data with that database while the database provides disk-based persistence.
| Storage approach | Where data lives | After the program ends | Large-data limitation |
|---|---|---|---|
| Python dictionary | RAM | Data disappears | Limited by available memory |
| SQLite database | A local disk-based database file | Data remains available | Designed for substantially more data than memory alone |
Mistakes Beginners Make
Treating a dictionary as permanent storage
A dictionary lives in RAM and its contents disappear when the program ends.
Fix:
Use a persistent database when records must remain available between program sessions.Assuming that disk storage cannot support fast retrieval
Database indexing lets the system use an organized roadmap to jump toward the requested data.
Fix:
Consider how an index reduces the number of records that must be examined.Confusing SQLite with a separate database server
SQLite is an embedded database system built into Python and is designed to provide persistence without the complexity of a separate database server.
Fix:
Recognize SQLite as a local embedded option for suitable Python applications.Ignoring storage capacity
A dictionary is constrained by the computer's finite RAM, while disk storage is far larger.
Fix:
Evaluate both persistence and the expected size of the data before choosing in-memory storage.
Check Your Understanding
An application must store a large collection of user records, keep them after the application closes, and retrieve a record by its identifying key. Explain why a dictionary alone is not the best storage solution, describe the role of an index, and identify why SQLite could be a practical choice in a Python application.
Hints
- Start with what happens to data held only in RAM when the program ends.
- Then compare the capacity of memory with disk storage.
- Finally, explain how an index avoids scanning every record and why SQLite does not require a separate database server.
What do you think happens?
A program stores customer records only in a dictionary. What should you predict will happen after the program ends?
Reveal answer
Answer: The records disappear unless they were stored somewhere persistent.
The dictionary lives in RAM, and its contents disappear when the program ends.
The Storage Decision
- A database is a persistent, disk-based way to store structured data, whereas a dictionary stores data in RAM and loses it when the program ends. Databases can hold vastly more data because disk storage is much larger than memory. Indexes act as roadmaps that let database software locate records without scanning every row, making retrieval practical even for very large datasets. SQLite is an embedded database system built into Python, offering reliable local persistence without requiring a separate database server.
- Persistence: database records remain after the application ends.
- Capacity: disk-based databases can store far more data than an in-memory dictionary.
- Speed: indexes help the database reach a target record without examining every record.
- Practical tool: SQLite provides embedded database storage for Python applications.
Key Takeaways
- A dictionary is temporary RAM storage; a database is persistent disk-based storage.
- Databases can support much larger datasets because disk storage is far larger than computer memory.
- Indexing maps requested keys toward their records and avoids scanning every row.
- SQLite is an embedded database system built into Python for applications that need reliable local persistence.