Course topics

By WebNest Studio Engineering Team

Python Tutorial

Database Connectivity with sqlite3

Almost every real application stores its data in a database. Python talks to relational databases through a common interface called the DB-API 2.0 (PEP 249): you open a connection, create a cursor, execute SQL, fetch results, commit changes, and close the connection. Once you know this pattern with one database, you know it for MySQL, PostgreSQL, SQL Server and Oracle too.

The easiest place to learn it is SQLite, a complete SQL database stored in a single file. The sqlite3 module is part of the standard library, so there is nothing to install and no server to run — which also means every example in this lesson runs right here in the browser.

Why it matters

Almost every application stores its data in a database, and Python talks to all the common ones through the same pattern: connect, get a cursor, execute SQL, fetch results, commit. sqlite3 ships with Python and needs no server, so it is the easiest place to learn that pattern before applying it to MySQL or PostgreSQL.

The DB-API Workflow

  • conn = sqlite3.connect("shop.db") — open (or create) a database file; ":memory:" creates a temporary in-memory database.
  • cur = conn.cursor() — a cursor executes statements and holds results. conn.execute() is a shortcut that creates one for you.
  • cur.execute(sql, params) runs one statement; cur.executemany(sql, rows) runs it for every row.
  • fetchone(), fetchmany(n) and fetchall() read results — or simply loop over the cursor.
  • conn.commit() saves changes, conn.rollback() discards them, conn.close() releases the connection.

Placeholders, Not String Formatting

Always pass values separately from the SQL: execute("SELECT * FROM users WHERE id = ?", (user_id,)). sqlite3 uses ? (positional) or :name (named) placeholders; MySQL and PostgreSQL drivers use %s and %(name)s. The driver sends the values safely, which prevents SQL injection and handles quoting and types for you. Note the trailing comma in (user_id,) — parameters must be a sequence, and (user_id) is just a number in brackets.

Rows, Types and Connections as Context Managers

By default rows come back as tuples. Set conn.row_factory = sqlite3.Row to access columns by name as well as by position. SQLite maps Python None, int, float, str and bytes to NULL, INTEGER, REAL, TEXT and BLOB. Using the connection in a with conn: block wraps it in a transaction — commit on success, rollback on an exception — but does not close it; use contextlib.closing() or call close() yourself.

Examples

Connect, create a table, insert and query

Python
import sqlite3

conn = sqlite3.connect(":memory:")        # use "school.db" for a file on disk
cur = conn.cursor()
cur.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        course TEXT NOT NULL,
        marks INTEGER CHECK (marks BETWEEN 0 AND 100)
    )
""")
cur.execute("INSERT INTO students (name, course, marks) VALUES (?, ?, ?)", ("Asha", "Python", 91))
print("new id:", cur.lastrowid)
conn.commit()

cur.execute("SELECT id, name, course, marks FROM students")
print(cur.fetchall())
print([column[0] for column in cur.description])
conn.close()
Output
new id: 1
[(1, 'Asha', 'Python', 91)]
['id', 'name', 'course', 'marks']

executemany, the fetch methods and sqlite3.Row

Python
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)")
conn.executemany("INSERT INTO products (name, category, price) VALUES (?, ?, ?)", [
    ("Pen", "stationery", 20.0),
    ("Notebook", "stationery", 60.0),
    ("Headphones", "electronics", 1499.0),
    ("Mouse", "electronics", 699.0),
    ("Backpack", "bags", 1250.0),
])
conn.commit()

cur = conn.execute("SELECT name, price FROM products ORDER BY price DESC")
print(cur.fetchone())
print(cur.fetchmany(2))
print(cur.fetchall())                      # whatever is left

conn.row_factory = sqlite3.Row             # rows now support row["column"]
query = """SELECT category, COUNT(*) AS items, ROUND(AVG(price), 1) AS avg_price
           FROM products GROUP BY category ORDER BY category"""
for row in conn.execute(query):
    print(row["category"], row["items"], row["avg_price"])
conn.close()
Output
('Headphones', 1499.0)
[('Backpack', 1250.0), ('Mouse', 699.0)]
[('Notebook', 60.0), ('Pen', 20.0)]
bags 1 1250.0
electronics 2 1099.0
stationery 2 40.0

A database file, named placeholders and context managers

Python
import sqlite3
from contextlib import closing

with closing(sqlite3.connect("library.db")) as conn:     # closing() calls conn.close()
    with conn:                                         # commit on success, rollback on error
        conn.execute("DROP TABLE IF EXISTS books")
        conn.execute("CREATE TABLE books (code TEXT PRIMARY KEY, title TEXT, year INTEGER)")
        conn.execute("INSERT INTO books VALUES (:code, :title, :year)",
                     {"code": "BK-001", "title": "Learning Python Basics", "year": 2026})

# A new connection sees the data because it was committed to the file.
with closing(sqlite3.connect("library.db")) as conn:
    print(conn.execute("SELECT title, year FROM books").fetchone())
    print(conn.execute("SELECT COUNT(*) FROM books").fetchone()[0], "book(s) stored")
Output
('Learning Python Basics', 2026)
1 book(s) stored

Common Mistakes

  • Building SQL with f-strings or + instead of placeholders, opening the door to SQL injection.
  • Writing (value) instead of (value,) for a single parameter.
  • Forgetting conn.commit(), so inserts and updates silently disappear when the program ends.
  • Assuming with conn: closes the connection — it only commits or rolls back.
  • Calling fetchall() on huge result sets instead of iterating over the cursor.

Exercise

Using an in-memory SQLite database, create a users table, insert two users with placeholders, and print the number of rows and then the names in alphabetical order.

Expected output: 2 then [('Asha',), ('Ravi',)]

Python · starter code
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

# TODO: create the table users(id INTEGER PRIMARY KEY, name TEXT)
# TODO: insert "Ravi" and "Asha" using ? placeholders, then commit

# TODO: print the number of rows
# TODO: print all names, ordered by name

connection.close()
Show hints
  • cursor.executemany("INSERT INTO users (name) VALUES (?)", [("Ravi",), ("Asha",)]) inserts both rows.
  • fetchone()[0] reads a single value such as a count, and fetchall() returns a list of tuples.
Show solution
Python · solution
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
cursor.executemany("INSERT INTO users (name) VALUES (?)", [("Ravi",), ("Asha",)])
connection.commit()

cursor.execute("SELECT COUNT(*) FROM users")
print(cursor.fetchone()[0])

cursor.execute("SELECT name FROM users ORDER BY name")
print(cursor.fetchall())

connection.close()

Quick quiz

1. Which placeholder does sqlite3 use for values in a query?

  • A. %s
  • B. ?
  • C. $1
  • D. {}
Show answer

B. Named placeholders such as :name are also supported.

2. What does sqlite3.connect(":memory:") create?

  • A. A file named :memory:
  • B. A connection to a server
  • C. A temporary database held in RAM that disappears when the connection closes
  • D. A read-only database
Show answer

C. It is convenient for tests and examples.

3. What must be done after an INSERT for the change to be saved?

  • A. Nothing
  • B. Close the cursor
  • C. Call connection.commit()
  • D. Call cursor.save()
Show answer

C. Uncommitted changes are lost when the connection closes.

Interview questions

What is the Python DB-API?

Show answer

It is a standard, defined in PEP 249, that database drivers follow, so that code for different databases looks the same. A driver provides connect(), which returns a connection; the connection provides cursor(), commit() and rollback(); and the cursor provides execute(), executemany() and the fetch methods. sqlite3, mysql-connector and psycopg all follow it.

Why must values be passed with placeholders and not put into the SQL string?

Show answer

When values are formatted into the string, text supplied by a user becomes part of the SQL and can change the meaning of the statement, which is SQL injection. With placeholders the driver sends the statement and the values separately, so a value is always treated as data. Placeholders also deal with quoting and type conversion correctly.

Key Points to Remember

  • DB-API pattern: connect → cursor → execute → fetch → commit → close.
  • sqlite3 is built in; ":memory:" gives a throwaway database, a file name gives a persistent one.
  • Always use placeholders (? or :name in sqlite3) for values.
  • fetchone, fetchmany and fetchall read results; cursors are also iterable.
  • sqlite3.Row gives name-based column access; with conn: manages the transaction.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering