Python SQLite gives you a real relational database that lives inside one file, with no server to install and no setup at all. The built-in sqlite3 module lets you create tables and run insert, select, update, and delete queries. You will learn parameterized queries that block SQL (Structured Query Language) injection, plus context managers that commit safely and roll back when something goes wrong.

“SQLite is the most used database engine in the world. It is in your phone, your browser, and your operating system.”

D. Richard Hipp, SQLite creator

Last Updated: July 2026 | Tested on: Python 3.14.6 | Difficulty: Intermediate | Reading Time: 17 minutes

At some point, saving your data to JSON (JavaScript Object Notation) or CSV (Comma-Separated Values) files stops being enough. You want to search it, filter it, sort it, and join one table to another without writing that logic by hand every time. You also want a guarantee that a crash halfway through a save will not leave half-written garbage on disk. That is what a database gives you, and Python ships one in the standard library: SQLite. The queries themselves are plain SQL, so if SELECT and JOIN are still new to you, the SQL basics tutorial covers them before you wire Python into the mix.

Think of SQLite as a single notebook you carry around in your bag. Everything lives on one page-numbered file, you open it, write in it, close it, and the whole thing fits in your pocket. A big database like PostgreSQL is more like a library building with a front desk, staff, and opening hours. Both store information, but for one person working on one machine, you do not need the whole building. You need the notebook.

SQLite is a full relational database that lives in a single file. No server to install, no configuration, no separate process to keep running. It is already on your machine, and Python’s sqlite3 module wraps it. For single-user apps, command-line tools, prototypes, desktop software, and tests, SQLite is the right pick. The moment you need many servers writing at the same time, you reach for PostgreSQL or MySQL instead (the next post covers that jump).

Every operation below uses parameterized queries (never string formatting), context managers for safe transactions, and the exact patterns you will reuse in every database project. The version of SQLite bundled with Python 3.14.6 is engine 3.50, so everything here runs out of the box with no installs.

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

The diagram traces the full Python SQLite lifecycle: connect to the database file, create a cursor, run your SQL, commit the transaction, then close the connection. Each step is one method call, namely connect(), cursor(), execute(), commit(), and close(). Learn this once and you will reuse it for every database you ever touch. The with statement shortcut shown later in the post handles the commit and close steps for you automatically.

Connect and Create a Table

Two lines of Python SQLite code get you a working database. You call sqlite3.connect() with a file name, and you get a connection. If the file does not exist yet, SQLite creates it for you on the spot. Then you ask that connection for a cursor, which is the little pen you use to write SQL into the notebook.

Before we store anything, we need a table. A table is just a grid with named columns, like a spreadsheet tab labelled “members” that has columns for id, name, age, and role.

📄 create_db.py: create a database and a table

import sqlite3

# Connect: creates the file if it does not exist yet
conn = sqlite3.connect("team.db")
cursor = conn.cursor()

# Create the table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL,
        role TEXT DEFAULT 'developer'
    )
""")

conn.commit()
print("Table created successfully!")
conn.close()

▶ Output

Table created successfully!

What happened here: Running this once drops a file called team.db right next to your script, and that file now holds an empty members table. The IF NOT EXISTS guard means you can run the script a second time and it will not complain that the table already exists. The id INTEGER PRIMARY KEY AUTOINCREMENT column hands every new row a unique number automatically, so you never have to track ids yourself. NOT NULL says a row is invalid without a name and an age, and DEFAULT 'developer' fills in the role when you do not provide one.

The conn.commit() line is what actually saves the change to disk, and we will come back to why that line matters so much.

CRUD Operations

CRUD stands for Create, Read, Update, Delete. Those four verbs cover almost everything you will ever do with a database. Picture the attendance register at a gym front desk: write in a new member, look someone up, correct a phone number, strike out someone who left. That is CRUD with a pen. The Python SQLite script below runs all four against the members table we just made, starting with a team lead named Rahul and four teammates.

Notice the question marks in every query. Those are placeholders, and they are the single most important habit in this whole post. You write ? where a value goes, then pass the real values as a separate tuple. SQLite slots them in safely. We will see exactly why that matters in the SQL injection section, but build the muscle memory now: values go in the tuple, never inside the query string.

📄 crud.py: insert, select, update, and delete with parameterized queries

import sqlite3

conn = sqlite3.connect("team.db")
conn.row_factory = sqlite3.Row  # Lets us read columns by name
cursor = conn.cursor()

# CREATE: insert a single row (ALWAYS use ? placeholders)
cursor.execute(
    "INSERT INTO members (name, age, role) VALUES (?, ?, ?)",
    ("Rahul", 28, "lead")
)

# CREATE: insert many rows at once
team = [
    ("Niranjan", 26, "developer"),
    ("Viraj", 30, "developer"),
    ("Aditi", 25, "designer"),
    ("Anvay", 27, "developer"),
]
cursor.executemany(
    "INSERT INTO members (name, age, role) VALUES (?, ?, ?)",
    team
)
conn.commit()

# READ: fetch everyone
cursor.execute("SELECT * FROM members")
for row in cursor.fetchall():
    print(f"  {row['name']} ({row['age']}) - {row['role']}")

# READ: fetch with a filter
cursor.execute("SELECT * FROM members WHERE age > ?", (26,))
seniors = cursor.fetchall()
print(f"\nMembers over 26: {[r['name'] for r in seniors]}")

# UPDATE: change one person's role
cursor.execute(
    "UPDATE members SET role = ? WHERE name = ?",
    ("senior developer", "Niranjan")
)
conn.commit()

# DELETE: remove one person
cursor.execute("DELETE FROM members WHERE name = ?", ("Aditi",))
conn.commit()

# Verify the final count
cursor.execute("SELECT COUNT(*) FROM members")
print(f"\nTotal members: {cursor.fetchone()[0]}")

conn.close()

▶ Output

  Rahul (28) - lead
  Niranjan (26) - developer
  Viraj (30) - developer
  Aditi (25) - designer
  Anvay (27) - developer

Members over 26: ['Rahul', 'Viraj', 'Anvay']

Total members: 4

What happened here: Four different jobs ran top to bottom. execute() runs one statement, while executemany() runs the same insert for every tuple in the list, which is the fast way to add a batch. Setting conn.row_factory = sqlite3.Row is a small quality-of-life win: instead of reading a row as row[1] and counting columns in your head, you write row['name'] and the code reads like plain English. The SELECT ...

WHERE age > ? query filtered the list down to the three people over 26. After the update and the delete, the final count is 4, because we started with 5 and removed Aditi. Every change that needs to stick (the inserts, the update, the delete) is followed by conn.commit().

Context Manager: Auto-Commit and Rollback

Calling conn.commit() by hand works, but it is easy to forget, and forgetting it silently throws your data away. The with statement fixes that. When you use a connection as a context manager, it commits for you if the block finishes cleanly, and it rolls back for you if an exception is raised inside the block. This is the pattern you ship to production.

Think of it like the autosave in a payment app. You tap to pay, and if the whole thing goes through, the transaction is saved. If your network drops halfway, the app does not leave you charged for half a coffee. It either all happens or none of it happens. A database transaction works the same way, and the with block is what flips that autosave on. In the script below, two new hires join the team, an intern named Prathamesh and an architect named Vinay, with no commit call anywhere in sight.

📄 safe_db.py: the production pattern

import sqlite3

def add_member(name: str, age: int, role: str = "developer"):
    with sqlite3.connect("team.db") as conn:
        conn.execute(
            "INSERT INTO members (name, age, role) VALUES (?, ?, ?)",
            (name, age, role)
        )
        # Commits automatically when the block ends cleanly
        # Rolls back automatically if an exception is raised

add_member("Prathamesh", 24, "intern")
add_member("Vinay", 31, "architect")

# Read the table back to prove both rows were saved
with sqlite3.connect("team.db") as conn:
    conn.row_factory = sqlite3.Row
    for row in conn.execute("SELECT name, age, role FROM members ORDER BY id"):
        print(f"  {row['name']} ({row['age']}) - {row['role']}")

▶ Output

  Rahul (28) - lead
  Niranjan (26) - senior developer
  Viraj (30) - developer
  Anvay (27) - developer
  Prathamesh (24) - intern
  Vinay (31) - architect

What happened here: Notice there is no conn.commit() call inside add_member(), yet both Prathamesh and Vinay show up in the read-back. The with block committed for us when each call finished without error. You also see Niranjan now reads “senior developer” from the update we ran earlier, and Aditi is gone, so the file remembered everything across separate script runs. One small note that trips people up: the context manager commits or rolls back the transaction, but it does not close the connection. For short scripts that is fine, since the connection is released when the program ends. In long-running apps you still close it yourself.

SQL Injection: Why Parameterized Queries Matter

This is the section that turns a placeholder habit into a security skill. SQL injection is not a Python SQLite quirk; it happens in any database code that pastes user input straight into a query string. A normal user types their name. An attacker types SQL, and your query happily runs it. The classic example is the search box that lets a stranger read, change, or wipe your data.

Here is the everyday version. Imagine a security guard who lets people in by reading a name off a slip of paper. A normal visitor writes “Rahul”. A sneaky visitor writes “Rahul, and also open the safe”. A careless guard reads the whole slip out loud as one instruction. A careful guard treats the entire slip as just a name, looks for a visitor literally called “Rahul, and also open the safe”, finds nobody, and turns them away. The ? placeholder is the careful guard.

❌ NEVER do this: f-string in SQL is a wide open door

# BAD: string formatting lets the user inject SQL.
# This line is commented out on purpose, because it really would
# run the attacker's SQL and could DROP your table.
user_input = "'; DROP TABLE members; --"
# cursor.execute(f"SELECT * FROM members WHERE name = '{user_input}'")
# The text above would close the string, run DROP TABLE, then
# comment out the rest. Your members table would be gone.

Now the safe version, and this one actually runs so you can see the placeholder do its job. We feed the query a classic injection string and watch it get treated as plain text.

✅ The fix: the ? placeholder treats input as data, not code

import sqlite3

conn = sqlite3.connect("team.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

# A malicious string a user might type into a search box
user_input = "Rahul' OR '1'='1"

# GOOD: the ? slots user_input in as a single piece of DATA
cursor.execute("SELECT name FROM members WHERE name = ?", (user_input,))
rows = cursor.fetchall()
print(f"Parameterized search for that text found: {[r['name'] for r in rows]}")

# A normal exact name still works as expected
cursor.execute("SELECT name FROM members WHERE name = ?", ("Rahul",))
print(f"Exact match for 'Rahul': {[r['name'] for r in cursor.fetchall()]}")

conn.close()

▶ Output

Parameterized search for that text found: []
Exact match for 'Rahul': ['Rahul']

What happened here: The attacker string Rahul' OR '1'='1 is the oldest trick in the book. Pasted into a query with an f-string, that OR '1'='1' is always true, so it would dump every row in the table. Passed through a ? placeholder, SQLite searches for a member whose name is exactly the text Rahul' OR '1'='1, finds nobody with that literal name, and returns an empty list. The dangerous SQL never runs, because the database only ever saw it as a value to match.

The plain name Rahul still works fine. Same query, same code, but the input can no longer break out and take control. The rule is short: always use ?, never an f-string or + in your SQL.

Common Mistakes

Mistake 1: Forgetting to commit after a write

This is the number one SQLite surprise. You insert a row, you close the connection, you re-open the database, and the row is just gone. No error, no warning, nothing. The insert happened in a transaction that was never committed, so SQLite quietly rolled it back. It is the database version of typing a full page in an editor and shutting the laptop without hitting Save. Here is a script that proves it.

❌ The bug: close without commit, and the row vanishes

import sqlite3

# Open, insert, then close WITHOUT committing
conn = sqlite3.connect("team.db")
conn.execute(
    "INSERT INTO members (name, age, role) VALUES (?, ?, ?)",
    ("Ghost", 0, "none")
)
conn.close()  # Never committed, so this write is thrown away

# Re-open and check whether "Ghost" survived
conn = sqlite3.connect("team.db")
cursor = conn.execute("SELECT COUNT(*) FROM members WHERE name = ?", ("Ghost",))
print(f"Ghost rows found after closing without commit: {cursor.fetchone()[0]}")
conn.close()

▶ Output

Ghost rows found after closing without commit: 0

✅ The fix: commit, or let the with block do it for you

# Option 1: commit explicitly after the write
conn.commit()

# Option 2 (better): use a context manager and never think about it
with sqlite3.connect("team.db") as conn:
    conn.execute(
        "INSERT INTO members (name, age, role) VALUES (?, ?, ?)",
        ("Real", 30, "developer")
    )
    # Auto-commits here when the block ends cleanly

What happened here: The first script inserted Ghost, then closed the connection without committing, so the count comes back 0. The row never made it to disk. This is exactly the kind of bug that wastes an afternoon, because the insert code looks perfectly correct. Either call conn.commit() after every write, or wrap the work in a with sqlite3.connect(...) block so the commit is automatic. The context manager is the safer default, because you cannot forget a line you never have to write.

Mistake 2: Building queries with f-strings

We covered the security side above, but it is worth repeating as a standalone rule, because it is the mistake that does the most damage. Never put a value into a query with an f-string, % formatting, or + concatenation. Always use ? placeholders and pass the values as a tuple. It is safer, and it is also faster, because SQLite can reuse the compiled query plan.

Best Practices

• DO use ? placeholders for every value, and pass the values as a tuple. Never an f-string or + in SQL.
• DO wrap writes in with sqlite3.connect(...) as conn: so commits and rollbacks happen for you.
• DO set conn.row_factory = sqlite3.Row so you can read columns by name like row['name'].
• DO add NOT NULL and sensible DEFAULT values when you create a table, so bad rows are rejected at the source.
• DON’T forget conn.commit() when you write by hand. An uncommitted insert is silently thrown away.
• DON’T reach for SQLite when many machines need to write at once. That is the job for PostgreSQL or MySQL.

Conclusion

You now have the full Python SQLite loop in your hands: connect to a file, get a cursor, run create, read, update, and delete with ? placeholders, and commit your changes. The single file means zero setup, the placeholders keep injection attacks out, and the with block keeps your transactions honest by committing on success and rolling back on failure.

Two habits carry over to every database you will ever use, not just SQLite. First, always parameterize your queries. Second, treat a write as unfinished until it is committed. Burn those in now, and the move to bigger engines later will feel like swapping the notebook for the library building, while the way you think about the data stays exactly the same.

Next up is MySQL and PostgreSQL, where you connect to a real database server over the network. After that, SQLAlchemy shows you how an ORM (Object-Relational Mapping) lets you work with Python objects instead of raw SQL strings, building right on top of everything you just learned here. And whenever you want the full roadmap, from beginner basics to the AI and ML chapters, visit the Python + AI/ML tutorial series home.

Practice Exercises

1. Exercise 1: Create a books table with columns for title, author, and year. Insert five books with executemany(), then print every book published after 2010.
2. Exercise 2: Write an add_book(title, author, year) function that uses a with block and parameterized queries. Call it a few times, then confirm with a SELECT COUNT(*) that every book was saved.
3. Exercise 3: Prove SQL injection cannot touch your data. Search the books table for the literal text x' OR '1'='1 using a ? placeholder and show that it returns zero rows, then update one book’s year and delete another, all inside a single with block.

Frequently Asked Questions

Interview Questions on Python SQLite

How interviewers actually probe this topic: real scenarios, with answers you can say out loud.

Q: Your script inserts 500 rows, prints a success message, and exits cleanly. The next morning the table is empty. What went wrong?

The writes were never committed. In the default transaction mode, sqlite3 opens an implicit transaction on the first INSERT, and closing the connection (or letting the process end) without conn.commit() rolls that transaction back silently. The fix is to call conn.commit() after the writes, or wrap them in with sqlite3.connect(...) as conn: so the commit happens automatically when the block exits cleanly.

Q: Your app starts throwing sqlite3.OperationalError: database is locked under load. What do you check first?

SQLite allows only one writer at a time, so first look for transactions that stay open too long: a connection that ran a write and never committed will hold the lock and block everyone else. Check that every write commits promptly and every connection gets closed. If writes are genuinely frequent, raise the timeout argument of sqlite3.connect() so writers wait instead of failing, and enable WAL mode (PRAGMA journal_mode=WAL) so readers stop blocking the writer. If you truly need many concurrent writers, that is the signal to move to PostgreSQL or MySQL.

Q: What is the difference between execute() and executemany(), and when would you use each?

execute() runs one statement with one set of parameters. executemany() takes the same parameterized statement plus a sequence of tuples and runs it once per tuple, looping at the C level, which makes it the right tool for batch inserts. Both keep everything inside a single transaction until you commit, so a 1000-row executemany() either lands completely or not at all.

Q: Does using the connection as a context manager close it? What does the with block actually manage?

No, and this catches a lot of people. with sqlite3.connect(...) as conn: manages the transaction, not the connection: it commits if the block finishes cleanly and rolls back if an exception escapes, but the connection stays open afterwards. In a short script that is harmless because the process exit releases it. In a long-running app you still call conn.close() yourself, or wrap the connection in contextlib.closing().

Q: Why are parameterized queries faster as well as safer than f-string queries?

Safety comes from the placeholder treating input as data, so injected SQL never executes. Speed comes from statement reuse: the sqlite3 module keeps a cache of compiled statements, and a query with ? placeholders is the same SQL text every time, so SQLite parses and plans it once and reuses that plan for every new set of values. An f-string produces a different SQL string per call, so every call pays the full parse-and-plan cost again.

Q: Can you use a ? placeholder for a table or column name, for example in ORDER BY?

No. Placeholders only work where a value can appear, never for identifiers like table names, column names, or the sort direction. If those parts must be dynamic, validate the input against a hard-coded allowlist of known names and only then place it into the SQL string. Anything else reopens the injection door you just closed.

Further reading: for the full reference, see SQL reference (SQLite).

Related Posts

Previous: SQL Indexes, Query Plans, and Schema Design That Scales

Next: Python: MySQL and PostgreSQL Connecting and Querying

Series Home: Python + AI/ML Tutorial Series