Python & Databases

SQL is useful on its own, but Python is where it becomes an application: a search box, a checkout button, a report, a background job. The sqlite3 module is Python's standard-library tool for talking to SQLite databases, so you can run real SQL from Python without installing a package.

Open a database conversation

A connection is Python's open conversation with a database, like walking up to the library desk and keeping your place until the work is done. The special SQLite name ":memory:" creates a temporary database that lives only for the current run, which is perfect for practice in the browser.

A transaction is a batch of database changes treated as one unit, and a commit is the step that saves those changes, like stamping a stack of checkout slips after you have checked them. A cursor is a small result reader returned by a query; you can loop over it row by row.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.execute("""
CREATE TABLE reading_log (
    entry_id INTEGER PRIMARY KEY,
    reader TEXT NOT NULL,
    title TEXT NOT NULL,
    minutes INTEGER NOT NULL
);
""")
 
connection.execute("""
INSERT INTO reading_log (reader, title, minutes)
VALUES ('Mina', 'Practical Python', 35);
""")
connection.execute("""
INSERT INTO reading_log (reader, title, minutes)
VALUES ('Ravi', 'Clean APIs', 28);
""")
connection.commit()
 
cursor = connection.execute("""
SELECT reader, title, minutes
FROM reading_log
ORDER BY reader;
""")
 
for reader, title, minutes in cursor:
    print(f"{reader} read {title} for {minutes} minutes")
 
connection.close()

Mina read Practical Python for 35 minutes
Ravi read Clean APIs for 28 minutes

That is the core driver rhythm: connect, execute SQL, fetch or loop through rows, commit changes, close the connection. close() is cleanup; it tells Python you are done with that database conversation.

Pass values separately

A parameterized query is a SQL statement where changing values are passed separately from the SQL text. SQLite uses ? as a placeholder, a marker where the database driver will safely put one value.

The second argument to execute() is a tuple of values. For one value, the comma in (reader,) matters: it makes a one-item tuple instead of just parentheses around a string.

The helper below uses .fetchall(), which fetches all result rows into a list so you can loop over them later.

import sqlite3
 
def add_entry(connection, reader, title, minutes):
    connection.execute(
        """
        INSERT INTO reading_log (reader, title, minutes)
        VALUES (?, ?, ?);
        """,
        (reader, title, minutes),
    )
    connection.commit()
 
def entries_for(connection, reader):
    return connection.execute(
        """
        SELECT title, minutes
        FROM reading_log
        WHERE reader = ?
        ORDER BY minutes DESC;
        """,
        (reader,),
    ).fetchall()
 
connection = sqlite3.connect(":memory:")
connection.execute("""
CREATE TABLE reading_log (
    entry_id INTEGER PRIMARY KEY,
    reader TEXT NOT NULL,
    title TEXT NOT NULL,
    minutes INTEGER NOT NULL
);
""")
 
add_entry(connection, "Mina", "Practical Python", 35)
add_entry(connection, "Mina", "Data Stories", 22)
add_entry(connection, "Noor", "Clean APIs", 31)
 
for title, minutes in entries_for(connection, "Mina"):
    print(f"{title}: {minutes} minutes")
 
connection.close()

Practical Python: 35 minutes
Data Stories: 22 minutes

Parameters are for values: names, cities, dates, numbers, and search text. They are not for table names, column names, SQL keywords, or whole WHERE clauses.

Try it - safe searches

Change search_text to "Noor" and then to "' OR 1=1 --". The second one looks like a nasty SQL fragment, but because it is passed as a value, SQLite treats it as plain text.

The injection trap

A SQL injection is a bug where untrusted text becomes part of the SQL command instead of staying a value. It is like letting a visitor write on the staff instruction card, not just fill in one box on a form.

Here is the unsafe version. The f-string glues attacker_text directly into SQL, so SQLite reads part of the "name" as SQL logic.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE reading_log (
    entry_id INTEGER PRIMARY KEY,
    reader TEXT NOT NULL,
    title TEXT NOT NULL
);
 
INSERT INTO reading_log (reader, title) VALUES
    ('Mina', 'Practical Python'),
    ('Ravi', 'Clean APIs'),
    ('Noor', 'Data Stories');
""")
 
attacker_text = "' OR 1=1 --"
 
unsafe_sql = f"""
SELECT reader, title
FROM reading_log
WHERE reader = '{attacker_text}';
"""
 
print("Rows returned by unsafe query:")
for reader, title in connection.execute(unsafe_sql):
    print(f"{reader}: {title}")
 
connection.close()

Rows returned by unsafe query:
Mina: Practical Python
Ravi: Clean APIs
Noor: Data Stories

The fix is not "clean the string harder." The fix is to pass the value separately, so the driver sends it as data.

import sqlite3
 
connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE reading_log (
    entry_id INTEGER PRIMARY KEY,
    reader TEXT NOT NULL,
    title TEXT NOT NULL
);
 
INSERT INTO reading_log (reader, title) VALUES
    ('Mina', 'Practical Python'),
    ('Ravi', 'Clean APIs'),
    ('Noor', 'Data Stories');
""")
 
attacker_text = "' OR 1=1 --"
 
rows = connection.execute(
    """
    SELECT reader, title
    FROM reading_log
    WHERE reader = ?;
    """,
    (attacker_text,),
).fetchall()
 
if rows:
    for reader, title in rows:
        print(f"{reader}: {title}")
else:
    print("No exact reader match")
 
connection.close()

No exact reader match

Now the whole attacker string is treated as one reader name. Weird name, no matching row, no leaked table.

PostgreSQL and MySQL shapes

SQLite runs inside your Python process. PostgreSQL and MySQL are database servers, separate running database programs that your Python code connects to over a local or network connection.

A database driver is the package that teaches Python how to speak to a specific database server. In a real project you install the driver in your environment, keep passwords in environment variables, and use the driver's placeholder style. These shapes are static because the browser runtime has no PostgreSQL or MySQL server and no third-party database drivers.

PostgreSQL shape with the psycopg driver:
 
connect:
    psycopg.connect("postgresql://app_user:<password>@localhost:5432/library")
 
parameterized query:
    cursor.execute(
        "SELECT title FROM books WHERE genre = %s",
        ("Python",),
    )
 
MySQL shape with a MySQL driver:
 
connect:
    mysql.connector.connect(
        host="localhost",
        port=3306,
        user="app_user",
        password="<from environment>",
        database="library",
    )
 
parameterized query:
    cursor.execute(
        "SELECT title FROM books WHERE genre = %s",
        ("Python",),
    )

The shape stays familiar: open a connection, execute parameterized SQL, fetch rows, commit writes, close resources. The details change by driver; the safety habit does not.

You now know the raw database-driver pattern: connect, execute, parameterize, fetch, commit, and close. Next, ORMs, tools that map database tables to Python objects, show why teams often put a higher-level layer over this repetitive SQL plumbing.