Python SQLite Database

Connecting to a Database

sqlite3 is part of the Python standard library, so no pip install is needed. connect() opens (or creates, if it doesn't exist yet) a database file, and cursor() gives you an object used to run SQL commands against it.

import sqlite3

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

Creating a Table

Use execute() with a CREATE TABLE statement to define the table's columns and data types, just like in plain SQL.

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
""")

Inserting and Querying Data

INSERT adds new rows, and SELECT retrieves them. After a SELECT, use fetchall() (or fetchone()) on the cursor to pull the matching rows back into Python.

cursor.execute("INSERT INTO students (name, age) VALUES ('Aarav', 21)")
conn.commit()

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
    print(row)

Parametrised Queries

Never build SQL strings by inserting variables directly with an f-string or plus signs — it opens the door to SQL injection. Instead, use a ? placeholder in the query and pass the values as a separate tuple; sqlite3 handles the safe substitution for you.

name = "Diya"
age = 24
cursor.execute(
    "INSERT INTO students (name, age) VALUES (?, ?)",
    (name, age)
)
conn.commit()

cursor.execute("SELECT * FROM students WHERE age > ?", (20,))
adults = cursor.fetchall()
print(adults)

Closing the Connection

Once you're done, close the connection to free up the file. Wrapping database code in a try/except/finally block, or using the connection as a context manager, helps make sure it always closes cleanly, even if an exception occurs.

conn.close()