SQLite with Python: Your Portable Database

The List That Forgets Everything

Suppose you write a small Python program to keep track of your friends' phone numbers. You start simply:

contacts = []
contacts.append(("Aisha", "9876543210"))
contacts.append(("Rohan", "9988776655"))
print(contacts)

This works perfectly — while the program is running. The moment you close the terminal, that contacts list is gone. Python variables live in RAM (the computer's working memory), and RAM is wiped clean every time a program ends. Run the script again tomorrow and contacts starts over as an empty list. If you have used a school library register or the CBSE mark-entry sheets your teachers maintain, you already know why this is unacceptable: real records must survive being closed and reopened.

Your first instinct might be to save the data to a plain text file or a CSV file instead. That is a genuine improvement — files survive after the program ends, because they are written to the disk, not to RAM. But this brings a new set of problems that every serious data-handling program eventually runs into, and understanding those problems is exactly what motivates the tool this chapter teaches: SQLite.

From Lists to Files — and Why Files Break Too

Imagine you store your class's marks in a CSV file called marks.csv:

roll_no,name,marks
1,Aisha Khan,92
2,Rohan Verma,78
3,Meera Iyer,85

Now try to answer a simple question: "Which students scored 80 or above?" With a CSV file, your Python program has to open the file, read every single line into memory, split each line on the comma, convert the marks column from text to a number, and then check the condition one row at a time. For 3 rows this is trivial. For 3,000 rows — say, every student across every section in a large school — your program re-reads and re-parses the entire file for every single question you ask it, and you have written the search logic yourself, by hand, with plenty of room for bugs (what if a name itself contains a comma? what if a row is missing a value?).

Now suppose two parts of your program try to update marks.csv at the same time — one part correcting Rohan's marks, another part adding a new student. Files were never designed for this. One write can silently overwrite the other, corrupting your data with no warning at all. A plain file has no concept of "this row and only this row should change," no concept of "undo this change if something goes wrong halfway through," and no built-in way to sort, filter, or combine data efficiently. These are exactly the problems a database is built to solve.

Meet SQLite: A Database That Fits in Your Pocket

A database is software that organizes data into tables — grids of rows and columns, much like the mark sheet above — and gives you a proper query language, SQL (Structured Query Language), to ask precise questions of that data: "give me every student with marks above 80, sorted highest first" becomes one line of SQL instead of a hand-written loop.

Most databases you may have heard of — MySQL, PostgreSQL, Oracle — work as a client-server system. A separate database server program has to be installed, configured, and kept running in the background at all times; your Python program then talks to that server over a network connection, even if the server happens to be on the same computer. This is powerful for large organizations, but it is heavy machinery for a school project, a personal expense tracker, or a small app running on a single laptop.

SQLite is different, and its name tells you why. It is a complete, real SQL database engine — but instead of running as a separate server, it is a small library that gets built directly into your program, and it stores the entire database as one ordinary file on disk, typically ending in .db or .sqlite. There is no server to install, no server to start before your program runs, and no network involved even conceptually. Your Python program opens the .db file the same way it would open any other file, works with it directly, and closes it when done. That single file is the database — every table, every row, every index lives inside it. Copy that one file to a USB drive, attach it to an email, or sync it to your phone, and you have moved the entire database, structure and data together. That is what "portable" means in this chapter's title.

This is not a toy technology. SQLite is one of the most widely deployed pieces of software on Earth: it is built into Android and iOS, used by every major web browser to store bookmarks and history locally, and used by countless apps — including messaging apps like WhatsApp — to store data on your own device without needing an internet connection or a server. And it comes free with every standard installation of Python: the sqlite3 module is part of Python's standard library, so import sqlite3 works with no separate installation at all.

Talking to SQLite from Python: connect, cursor, execute

Python's built-in sqlite3 module gives you three core objects to remember. A connection represents the open link to a specific .db file. A cursor is the object you actually send SQL commands through and read results from — think of the connection as "the file is open" and the cursor as "the pen you write and read with." And SQL statements are plain text strings following SQL's own grammar, not Python syntax, that you hand to the cursor with execute().

Here is the first real program — it creates a table named students inside a file called school.db:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        roll_no INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        marks INTEGER
    )
""")

connection.commit()
connection.close()

Trace through what happens: sqlite3.connect("school.db") looks for a file named school.db in the current folder; if it does not exist yet, SQLite creates it right there — an empty database file, ready to use. cursor() gives us the object we will run commands through. The CREATE TABLE statement defines three columns: roll_no as an INTEGER that is also the PRIMARY KEY (meaning every student must have a unique roll number, and SQLite will refuse to store two rows with the same one), name as TEXT that cannot be empty (NOT NULL), and marks as an INTEGER. The IF NOT EXISTS clause means running this script a second time will not crash with an error even though the table is already there.

Two lines deserve special attention because forgetting them is the single most common SQLite bug: connection.commit() and connection.close(). SQLite wraps changes in transactions — a batch of changes that only becomes permanent when you explicitly call commit(). If your program crashes, or you simply forget to call commit(), every change you made since the last commit is silently lost, even though your execute() calls appeared to succeed with no error. close() then releases the file so other programs (or the next run of your own program) can open it cleanly.

Because roll_no is declared INTEGER PRIMARY KEY, SQLite treats it as a direct alias for its own internal row identifier, which makes looking up a specific student by roll number extremely fast — a small but genuine performance detail specific to how SQLite implements primary keys.

Adding Rows: INSERT, and the Danger of Building Queries by Hand

Suppose we run this script the next day to add our first student:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    "INSERT INTO students (roll_no, name, marks) VALUES (?, ?, ?)",
    (1, "Aisha Khan", 92)
)

connection.commit()
connection.close()

Notice the ? symbols in the SQL string, with the actual values passed separately as a tuple. These are called parameter placeholders, and using them is not just a style preference — it is a correctness and security requirement. Here is why, with the mistake many beginners make first:

# DANGEROUS — do not do this
name_input = input("Enter a name to search: ")
query = "SELECT * FROM students WHERE name = '" + name_input + "'"
cursor.execute(query)

This looks harmless, but watch what happens if someone types ' OR '1'='1 into the input instead of a real name. The string concatenation builds this SQL:

SELECT * FROM students WHERE name = '' OR '1'='1'

Since '1'='1' is always true, this condition is true for every row, and the query returns the entire table — every student's data — regardless of what name was intended. This class of bug is called SQL injection, and it is one of the most well-documented security flaws in real software, not a made-up textbook danger. The fix is exactly the pattern shown earlier:

name_input = input("Enter a name to search: ")
cursor.execute("SELECT * FROM students WHERE name = ?", (name_input,))

With a , the sqlite3 module sends the SQL text and the value as two completely separate pieces to the database engine. Whatever the user typed is treated purely as data to compare against, never as part of the SQL grammar itself — so even a mischievous input like ' OR '1'='1 is simply searched for as a literal (and unusual) name, matching nothing. Always build queries with ? placeholders and pass values as a tuple; never paste user input directly into an SQL string with + or an f-string.

To insert several rows at once, use executemany() with a list of tuples instead of calling execute() in a loop:

students_data = [
    (2, "Rohan Verma", 78),
    (3, "Meera Iyer", 85),
    (4, "Vikram Singh", 64),
    (5, "Fatima Sheikh", 91),
]

cursor.executemany(
    "INSERT INTO students (roll_no, name, marks) VALUES (?, ?, ?)",
    students_data
)
connection.commit()

After this runs (and assuming connection/cursor were opened the same way as before), students.db holds five rows: Aisha Khan (92), Rohan Verma (78), Meera Iyer (85), Vikram Singh (64), and Fatima Sheikh (91) — and this data will still be there the next time any program opens school.db, unlike our very first list example that vanished the moment the program ended.

Asking Questions: SELECT, WHERE, ORDER BY

Reading data back uses SELECT, and the results come back through the cursor using fetchone() (one row), fetchall() (every matching row, as a list), or fetchmany(n) (the next n rows). Let's find every student scoring 80 or above, sorted from highest to lowest:

cursor.execute(
    "SELECT name, marks FROM students WHERE marks >= 80 ORDER BY marks DESC"
)
toppers = cursor.fetchall()
for name, marks in toppers:
    print(name, marks)

Trace it against our five rows: the WHERE marks >= 80 filter keeps Aisha (92), Meera (85), and Fatima (91) — Rohan (78) and Vikram (64) are excluded. ORDER BY marks DESC then sorts the survivors from highest marks to lowest. So toppers becomes a Python list of tuples: [("Aisha Khan", 92), ("Fatima Sheikh", 91), ("Meera Iyer", 85)], and the loop prints:

Aisha Khan 92
Fatima Sheikh 91
Meera Iyer 85

SQL also has built-in aggregate functions that compute a single summary value across many rows — no manual loop needed. To find the class average:

cursor.execute("SELECT AVG(marks) FROM students")
average = cursor.fetchone()[0]
print(f"Class average: {average:.2f}")

fetchone() here returns a single tuple with one value inside it, (82.0,), so [0] pulls out the number itself. The sum of all five marks is 92 + 78 + 85 + 64 + 91 = 410, and 410 ÷ 5 = 82.0, so the output is:

Class average: 82.00

Compare this to the CSV approach from earlier: there, you would have had to read every line, convert every value from text to a number yourself, and add a running total in a loop. Here, one line of SQL — AVG(marks) — does the entire computation inside the database engine itself, which is both less code and far less error-prone once tables grow to thousands of rows.

Common Misconception: "SQLite Is Just a Stripped-Down, Lite Version of SQL"

The name SQLite makes many students assume it is a scaled-back, toy database meant only for learning, with a "real" database like MySQL waiting on the other side once you graduate to serious programming. This is incorrect, and it is worth correcting explicitly. The "lite" in the name refers to how lightweight the engine is to deploy — no server, no configuration, no administrator — not to how capable it is as a database. SQLite fully supports transactions, multiple tables, joins, indexes, and the same core SQL syntax (SELECT, WHERE, ORDER BY, aggregate functions, and more) that large client-server databases use. It is precisely because it is so capable and so easy to embed that it ended up inside billions of devices — every Android phone, iPhone, and most desktop web browsers carry a working SQLite engine, often storing your own app data on your own device right now.

A second, more technical misconception worth knowing at this level: unlike many other SQL databases, SQLite does not strictly enforce the column types you declare. If you declare a column INTEGER, SQLite will still, in most cases, accept and store a piece of text in that column without an error — this is called type affinity rather than strict typing. In everyday use, sticking to the type you declared (as we have throughout this chapter) is still the right habit, but it is useful to know that SQLite's flexibility here is a deliberate design choice, not a bug.

Changing Your Mind: UPDATE and DELETE

Records change. Suppose Vikram Singh's answer sheet was re-evaluated and his marks should rise from 64 to 88:

cursor.execute(
    "UPDATE students SET marks = ? WHERE roll_no = ?",
    (88, 4)
)
connection.commit()

The WHERE roll_no = ? clause is essential here — it tells SQLite exactly which row to change. Leave out the WHERE clause entirely and UPDATE students SET marks = 88 would overwrite every student's marks to 88, which is almost never what you want. This is one of the most common and most damaging beginner mistakes with UPDATE and DELETE statements — always double-check the WHERE clause before running either one.

Now let's remove any student still scoring below 80 after the correction:

cursor.execute("DELETE FROM students WHERE marks < ?", (80,))
connection.commit()
print(f"Deleted {cursor.rowcount} student(s) below 80 marks.")

At this point the table holds Aisha (92), Rohan (78), Meera (85), Vikram (88, just updated), and Fatima (91). Checking each against marks < 80: only Rohan (78) qualifies, since Vikram was already raised to 88 before this statement ran. So exactly one row is deleted, cursor.rowcount reports 1, and the output is:

Deleted 1 student(s) below 80 marks.

The table now contains four students: Aisha Khan (92), Meera Iyer (85), Vikram Singh (88), and Fatima Sheikh (91).

Why "Portable"? The Whole Database Lives in One File

Return to the diagram above and notice the contrast it draws. With a client-server database, the data is only accessible while a separate server program is installed and running, and moving that data to another computer means exporting it, transferring the export, installing the same server software elsewhere, and importing it back in — several steps, several places to go wrong. With SQLite, school.db is a self-contained, ordinary file, exactly like a Word document or a photo. Copy it to a pen drive, attach it to an email, upload it to Google Drive, and open it again with the exact same three lines — sqlite3.connect(), cursor(), execute() — on any computer with Python installed, and every table, every row, and every constraint you defined is there, unchanged.

This is precisely why SQLite is the natural choice for a school project, a personal expense tracker that logs your pocket money in rupees, or a small offline attendance app: you get a genuine, fully-featured SQL database without needing anyone to set up or maintain a server. It is also why, on your own phone, apps you use every day quietly keep their local data — contacts, downloaded messages, cached content — inside SQLite .db files sitting in the app's private storage, with no server involved at all.

Check Yourself

Work through these before checking the answers below. Assume the students table currently holds: Aisha Khan (92), Meera Iyer (85), Vikram Singh (88), Fatima Sheikh (91) — the state reached at the end of the UPDATE/DELETE section above.

1. Predict the exact printed output of: cursor.execute("SELECT name FROM students ORDER BY marks DESC"); print(cursor.fetchall())
2. A classmate writes cursor.execute("INSERT INTO students VALUES (6, 'Arjun Rao', 70)") and then closes the program without calling connection.commit(). When they reopen school.db tomorrow, will Arjun's row be there? Why?
3. Spot the bug: query = f"SELECT * FROM students WHERE name = '{user_input}'". What is this bug called, and how should the line be rewritten?
4. What single SQL clause, if accidentally omitted, would cause cursor.execute("DELETE FROM students") to erase the entire table instead of one row?
5. Why can you move a SQLite database to a new computer just by copying one file, while a MySQL database usually cannot be moved this way?

Answers:

1. [('Aisha Khan',), ('Fatima Sheikh',), ('Vikram Singh',), ('Meera Iyer',)] — sorted 92, 91, 88, 85.
2. No. Without connection.commit(), the insert was never made permanent, so it is lost when the connection closes.
3. SQL injection — a malicious or unexpected user_input could alter the query's logic. Fix: cursor.execute("SELECT * FROM students WHERE name = ?", (user_input,)).
4. The WHERE clause — without it, DELETE FROM students removes every row in the table.
5. Because the entire SQLite database — structure and data — is stored in one ordinary file that your program opens directly; MySQL data lives inside a running server process on the original machine, not in a single portable file.

Summary

• Python variables (like a list) disappear when the program ends because RAM is temporary; databases store data on disk so it survives.
• Plain text/CSV files are hard to search efficiently, easy to corrupt with concurrent writes, and require you to hand-write filtering and sorting logic.
• SQLite is a real, fully-featured SQL database engine that needs no separate server — the whole database is one .db file, making it genuinely portable.
• Python's built-in sqlite3 module needs no installation: connect() opens the file, cursor() gives you a way to run commands, execute()/executemany() run SQL, and you must call commit() to make changes permanent and close() when done.
• CREATE TABLE defines columns and types; INSERT adds rows; SELECT ... WHERE ... ORDER BY retrieves and filters rows; UPDATE ... WHERE and DELETE ... WHERE modify or remove specific rows — always check the WHERE clause on the last two.
• Always pass values through ? placeholders, never by pasting them into the SQL string — this prevents SQL injection.
• SQLite's "lite" refers to how easy it is to embed, not to weak capability — it powers local data storage in Android, iOS, browsers, and countless everyday apps.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where sqlite with python: your portable database is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting sqlite with python: your portable database to at least 3 other topics you have studied.