Setting Up SQLite in Python: A Complete Connection Guide

You already know how to write SQL — now you will run it from Python. The sqlite3 module ships with every Python installation, so there is nothing to install. By the end of this page you will have a working Python script that creates an employee database, inserts records, queries them, and handles the connection safely. You will also know how to browse your .db file visually in VS Code.

The sqlite3 Module

sqlite3 is Python’s built-in interface to SQLite databases. It follows the DB-API 2.0 standard, which means the patterns you learn here apply to every other Python database driver (psycopg2 for PostgreSQL, mysql-connector for MySQL, etc.).

import sqlite3  # no pip install needed

Core Concepts: Connection and Cursor

Working with SQLite in Python always follows the same four-step pattern.

Connect

sqlite3.connect() opens (or creates) a .db file and returns a connection object. The connection represents your session with the database.

conn = sqlite3.connect("employees.db")

Pass ":memory:" instead of a filename to create an in-memory database that lives only for the duration of your script — great for testing.

Create a cursor

A cursor is the object you use to send SQL statements to the database and retrieve results.

cursor = conn.cursor()

Execute and commit

Use cursor.execute() for single statements and cursor.executemany() for batch inserts. After INSERT, UPDATE, or DELETE you must call conn.commit() to persist the changes.

cursor.execute("INSERT INTO ...")
conn.commit()

Close the connection

Always close the connection when you are done to release the file lock.

conn.close()

The Context Manager Pattern (Recommended)

Using with sqlite3.connect(...) as a context manager automatically commits on success and rolls back on error. It also closes the connection when the with block exits — even if an exception is raised.

import sqlite3

with sqlite3.connect("employees.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT sqlite_version()")
    print(cursor.fetchone())  # ('3.x.x',)
# Connection is closed automatically here

Prefer the context manager pattern in all production code. It prevents the common bug of forgetting conn.commit() or conn.close() when an exception interrupts your script.

Fetching Results

After a SELECT query, use one of these methods to retrieve rows.

Method	Returns
`cursor.fetchone()`	The next single row as a tuple, or `None`
`cursor.fetchmany(n)`	Up to `n` rows as a list of tuples
`cursor.fetchall()`	All remaining rows as a list of tuples

cursor.execute("SELECT employee_name, salary FROM employee")

# Fetch all rows at once
rows = cursor.fetchall()
for name, salary in rows:
    print(f"{name}: ₹{salary:,.0f}")

Complete Working Script

The following script creates the department and employee tables, inserts data, and runs several queries. Run it once to create employees.db, then run it again — the IF NOT EXISTS guard prevents errors on subsequent runs.

import sqlite3

# ── 1. Connect (creates employees.db if it doesn't exist) ────────────────────
conn = sqlite3.connect("employees.db")
cursor = conn.cursor()

# ── 2. Enable foreign-key enforcement (off by default in SQLite) ─────────────
cursor.execute("PRAGMA foreign_keys = ON")

# ── 3. Create tables ──────────────────────────────────────────────────────────
cursor.execute("""
    CREATE TABLE IF NOT EXISTS department (
        department_id   INTEGER PRIMARY KEY,
        department_name TEXT    NOT NULL
    )
""")

cursor.execute("""
    CREATE TABLE IF NOT EXISTS employee (
        employee_id   INTEGER PRIMARY KEY,
        employee_name TEXT    NOT NULL,
        salary        REAL    NOT NULL,
        city          TEXT    NOT NULL,
        joining_date  TEXT    NOT NULL,
        department_id INTEGER,
        FOREIGN KEY (department_id) REFERENCES department(department_id)
    )
""")

# ── 4. Insert departments ─────────────────────────────────────────────────────
departments = [
    (1, "Engineering"),
    (2, "Human Resources"),
    (3, "Sales"),
    (4, "Finance"),
    (5, "Marketing"),
]
cursor.executemany(
    "INSERT OR IGNORE INTO department VALUES (?, ?)",
    departments
)

# ── 5. Insert employees ───────────────────────────────────────────────────────
employees = [
    (101, "Rahul Sharma",   65000, "Hyderabad",     "2022-01-15", 1),
    (102, "Priya Reddy",    72000, "Bengaluru",      "2021-08-20", 1),
    (103, "Arjun Kumar",    58000, "Chennai",        "2023-02-10", 1),
    (104, "Sneha Patel",    81000, "Pune",           "2020-11-18", 1),
    (105, "Anitha Rao",     56000, "Hyderabad",      "2023-04-08", 2),
    (106, "Karthik Nair",   61000, "Kochi",          "2021-12-01", 2),
    (107, "Ajay Verma",     50000, "Hyderabad",      "2024-01-10", 3),
    (108, "Neha Kapoor",    64000, "Mumbai",         "2021-10-21", 3),
    (109, "Harsha Vardhan", 76000, "Hyderabad",      "2021-06-05", 4),
    (110, "Gopal Krishna",  53000, "Visakhapatnam",  "2024-02-12", None),
]
cursor.executemany(
    """INSERT OR IGNORE INTO employee
       (employee_id, employee_name, salary, city, joining_date, department_id)
       VALUES (?, ?, ?, ?, ?, ?)""",
    employees
)

# ── 6. Commit ─────────────────────────────────────────────────────────────────
conn.commit()

# ── 7. Query and display ──────────────────────────────────────────────────────
print("── All Employees ────────────────────────────")
cursor.execute("""
    SELECT employee_id, employee_name, salary, city
    FROM employee
    ORDER BY salary DESC
""")
for row in cursor.fetchall():
    emp_id, name, salary, city = row
    print(f"  [{emp_id}] {name:<20} ₹{salary:>8,.0f}  {city}")

print("\n── Employees in Hyderabad ───────────────────")
cursor.execute("""
    SELECT employee_name, salary
    FROM employee
    WHERE city = 'Hyderabad'
""")
for name, salary in cursor.fetchall():
    print(f"  {name}: ₹{salary:,.0f}")

print("\n── Total employees:", end=" ")
cursor.execute("SELECT COUNT(*) FROM employee")
print(cursor.fetchone()[0])

# ── 8. Close ──────────────────────────────────────────────────────────────────
conn.close()
print("\nDatabase closed.")

Expected Output

── All Employees ────────────────────────────
  [104] Sneha Patel          ₹  81,000  Pune
  [109] Harsha Vardhan       ₹  76,000  Hyderabad
  [102] Priya Reddy          ₹  72,000  Bengaluru
  ...

── Employees in Hyderabad ───────────────────
  Rahul Sharma: ₹65,000
  Anitha Rao: ₹56,000
  Ajay Verma: ₹50,000
  Harsha Vardhan: ₹76,000

── Total employees: 10

Database closed.

Using Parameterized Queries

Never use Python string formatting to build SQL with user-supplied values. That opens your app to SQL injection. Instead, pass parameters using ? placeholders.

❌ Unsafe (never do this)
✅ Safe (parameterized)

city = input("Enter city: ")
# If the user types: Hyderabad' OR '1'='1
# every row is returned
cursor.execute(f"SELECT * FROM employee WHERE city = '{city}'")

city = input("Enter city: ")
cursor.execute("SELECT * FROM employee WHERE city = ?", (city,))
rows = cursor.fetchall()

SQLite handles escaping automatically. The ? is never treated as part of the query syntax.

Getting Results as Dictionaries

By default, sqlite3 returns rows as plain tuples. Set conn.row_factory = sqlite3.Row to get dictionary-like objects where you can access columns by name.

conn = sqlite3.connect("employees.db")
conn.row_factory = sqlite3.Row   # <-- enable named columns

cursor = conn.cursor()
cursor.execute("SELECT employee_name, salary, city FROM employee LIMIT 3")

for row in cursor.fetchall():
    print(f"{row['employee_name']} earns ₹{row['salary']:,.0f} in {row['city']}")

conn.close()

Rahul Sharma earns ₹65,000 in Hyderabad
Priya Reddy earns ₹72,000 in Bengaluru
Arjun Kumar earns ₹58,000 in Chennai

Inspecting Your Database in VS Code

Install the SQLite Viewer extension to browse your .db file visually without writing any SQL.

Install the extension

Open VS Code → Extensions (Ctrl+Shift+X / Cmd+Shift+X) → Search for “SQLite Viewer” by Florian Klampfer → Install.

Open your .db file

In the VS Code Explorer, click on employees.db. SQLite Viewer opens a table browser automatically.

Browse tables and rows

You can see every table, click through rows, and verify that your Python script inserted data correctly — no terminal needed.

The SQLite Viewer extension is read-only. To run SQL queries interactively against your .db file you can also install the SQLite extension by Alex Covizzi and use the Command Palette (Ctrl+Shift+P) → “SQLite: Open Database”.

Database Schema Reference

┌──────────────────────┐         ┌─────────────────────────────────┐
│      department      │         │            employee              │
├──────────────────────┤         ├─────────────────────────────────┤
│ department_id  PK    │◄────────│ department_id  FK               │
│ department_name      │         │ employee_id    PK               │
└──────────────────────┘         │ employee_name                   │
                                 │ salary                          │
                                 │ city                            │
                                 │ joining_date                    │
                                 └─────────────────────────────────┘

You now have a working Python + SQLite setup and a populated database to query in the pages that follow.

Next: Querying Data with SELECT

Learn to filter, sort, aggregate, and group your employee data using the full power of the SELECT statement.

Project Development

SQL & Databases

Data Analysis

Setting Up SQLite in Python: A Complete Connection Guide

The sqlite3 Module

Core Concepts: Connection and Cursor

The Context Manager Pattern (Recommended)

Fetching Results

Complete Working Script

Expected Output

Using Parameterized Queries

Getting Results as Dictionaries

Inspecting Your Database in VS Code

Database Schema Reference

Next: Querying Data with SELECT

​The sqlite3 Module

​Core Concepts: Connection and Cursor

​The Context Manager Pattern (Recommended)

​Fetching Results

​Complete Working Script

​Expected Output

​Using Parameterized Queries

​Getting Results as Dictionaries

​Inspecting Your Database in VS Code

​Database Schema Reference

Next: Querying Data with SELECT

The sqlite3 Module

Core Concepts: Connection and Cursor

The Context Manager Pattern (Recommended)

Fetching Results

Complete Working Script

Expected Output

Using Parameterized Queries

Getting Results as Dictionaries

Inspecting Your Database in VS Code

Database Schema Reference