Introduction to SQLite3 & Relational Databases

Until now, when you save data in Python, you stored it in lists, dictionaries, text files, or JSON files.

However, flat files have serious limitations:

1. Searching is slow: To find one student out of 500,000, you have to read the entire text file into RAM.
2. Data corruption: If two users write to a text file at the exact same millisecond, the data gets scrambled.
3. No relations: Connecting an orders.txt file to a customers.txt file requires dozens of manual lines of code.

A Relational Database Management System (RDBMS) stores structured data in tables with guaranteed consistency, relationships, and lightning-fast search queries.

---

1. Why SQLite3 is Special

Most relational databases (like PostgreSQL, MySQL, Oracle) require you to:

• Install a heavy background database server.
• Configure ports, firewalls, and usernames/passwords.

SQLite3 is different:

• Serverless: It runs directly inside your Python process.
• Single File: Your entire database lives in one single file on disk (e.g. store.db).
• Built-in Standard Library: Included in Python by default (import sqlite3) — zero pip install required!

+-----------------------------------------------------------+
| Python Script (import sqlite3)                            |
|                            ↓                              |
| Direct file read/write to database file (e.g. library.db) |
+-----------------------------------------------------------+

---

2. Core Relational Database Terminology

Term	What It Means	Real-World Example
Table	A 2D grid containing rows and columns	students table
Row (Record)	A single individual entry in the table	Student #101: ("Rahul", 21, "CSE")
Column (Field)	A specific property / attribute	student_id, email, gpa
Primary Key	A unique ID that never repeats	Aadhaar Number or user_id

---

3. SQL Data Types in SQLite

SQLite uses a clean, compact type system:

SQLite Type	Python Equivalent	Description
INTEGER	int	Whole numbers (10, -5, 0)
REAL	float	Decimal numbers (99.5, 3.14)
TEXT	str	Text strings ("Sai", "CSE")
BLOB	bytes	Binary data (images, PDFs)
NULL	None	Missing or empty value

---

Quick Summary

• Serverless SQL Database: SQLite3 stores full relational databases in a single local .db file without server setup.
• Standard Library: Built directly into Python (import sqlite3).
• Five Datatypes: INTEGER, REAL, TEXT, BLOB, NULL.

---

What's Next?

Let's learn how to establish database connections and create tables using SQL schemas in Connecting & Creating Tables!