Python and Databases | Learn Computer Academy

This is where Python's ability to store and retrieve real data becomes practical. If you haven't looked at the Introduction to SQL lesson yet, this is the right point to do it — from here on, this lesson assumes you know what a table, row, and basic SELECT statement are.

sqlite3: Built Into Python

Unlike PHP, which needs a separate extension for database access, Python's standard library includes sqlite3 — a connector for SQLite, a simple database stored in a single file, no separate server needed. It's the easiest way to start:

import sqlite3

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

cursor.execute("SELECT name, course FROM students")
for row in cursor.fetchall():
    print(row)

conn.close()

Connecting to MySQL

For MySQL specifically — the same database this site's own SQL section teaches — a third-party package handles the connection, installed with pip (from the Modules and Imports lesson):

pip install mysql-connector-python

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="school",
    )
    print("Connected successfully.")
except mysql.connector.Error as e:
    print(f"Connection failed: {e}")

Wrapping the connection in try/except (from the Exception Handling lesson) matters here for the same reason it did in PHP — a database can be temporarily unreachable for reasons unrelated to your code.

Isometric diagram showing a Python script icon connected through a small key/plug connector shape to a cylindrical database icon, illustrating a secure connection between a script and a database

Parameterized Queries — the Safe Way to Use Variables

Never build a query by directly gluing a variable into the SQL string — exactly the same warning as PHP's PDO lesson, and just as important here. Python's database connectors use %s placeholders instead of gluing variables in directly:

cursor = conn.cursor()

# NEVER do this:
# cursor.execute(f"SELECT * FROM students WHERE name = '{name}'")

# Do this instead:
cursor.execute("SELECT * FROM students WHERE name = %s", (name,))
result = cursor.fetchone()

This is not optional

Gluing a variable directly into a SQL string opens the door to SQL injection — a visitor entering something like anything' OR '1'='1 could rewrite your query's logic entirely. Parameterized queries close this off completely, because the database driver itself keeps data and code separate — this is not something to skip "for simple cases," in Python any more than it was in PHP.

INSERT, UPDATE, and DELETE

# INSERT
cursor.execute("INSERT INTO students (name, course) VALUES (%s, %s)", ("Priya", "Web Development"))
conn.commit()  # writes are not saved until you commit

# UPDATE
cursor.execute("UPDATE students SET course = %s WHERE name = %s", ("Graphic Design", "Priya"))
conn.commit()

# DELETE
cursor.execute("DELETE FROM students WHERE name = %s", ("Priya",))
conn.commit()

conn.commit() is a real difference from PHP's PDO, which commits automatically by default: Python's connectors require an explicit commit() after a write, or the change won't actually be saved.