Python-SQL Interface

Introduction

You already know how to write a SELECT query directly in SQL. Python and MySQL are two separate programs, though — connecting them needs a bridge, so a Python program can send that same kind of query and get results back as ordinary Python data. The mysql.connector module is that bridge.

Concept

Two objects work together for every database interaction:

• Connection — represents the actual link between Python and the MySQL server (created with your host, username, password, and database name).
• Cursor — created from a Connection, and is the object that actually executes queries and retrieves their results. Think of the Connection as the phone line, and the Cursor as the person talking through it.

Worked Example

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="school"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM STUDENT")

result = mycursor.fetchall()
for row in result:
    print(row)

mycursor.execute("SELECT * FROM STUDENT")
first_row = mycursor.fetchone()
print("Just one row:", first_row)

fetchall() returns every matching row as a list of tuples — one tuple per row, exactly matching how you'd already expect a SQL row to look. fetchone() instead returns just a single tuple — the next unread row from the same query — which is why execute() is called a second time above, to give fetchone() a fresh result set to read from.

Note: fetchone() returns just the next single row; fetchall() returns every remaining row at once, as a list. Both are read from the SAME cursor after execute() runs a SELECT.

Summary

A Connection links Python to the MySQL server; a Cursor, created from it, executes queries and retrieves results. fetchall() returns every row as a list of tuples — the standard way Python code reads back SQL query results.

Fetch Methods

• fetchall(): Returns ALL rows (List of Tuples).
• fetchone(): Returns ONE row (Single Tuple) or None.
• rowcount: Returns the number of rows affected/retrieved.

Introduction

Reading data with SELECT is only half of Python-SQL connectivity — actually changing data (INSERT, UPDATE, DELETE) from Python needs one extra, easy-to-forget step, plus a real security habit worth building from the start.

Concept

After executing a data-changing query, you must call mydb.commit() on the Connection object (not the cursor) — without it, MySQL treats the change as temporary and it is never actually saved to the database.

Worked Example

import mysql.connector

mydb = mysql.connector.connect(host="localhost", user="root", password="password", database="school")
mycursor = mydb.cursor()

name = input("Enter name: ")
marks = input("Enter marks: ")

# %s placeholders -- NEVER build the query with f-strings/string concatenation
sql = "INSERT INTO STUDENT (Name, Marks) VALUES (%s, %s)"
mycursor.execute(sql, (name, marks))

mydb.commit()
print("Record inserted successfully.")

Warning: Building a query with an f-string like f"INSERT INTO STUDENT VALUES ('{name}')" is a real SQL Injection risk — a malicious value typed as input could alter the query's meaning entirely. The %s placeholder style, with values passed separately to execute(), is what actually prevents this.

Summary

mycursor.execute(sql, values) runs an INSERT/UPDATE/DELETE, but mydb.commit() — called on the Connection, not the Cursor — is what actually saves the change permanently. Using %s placeholders instead of building queries with string formatting is what protects against SQL Injection.

Secure Queries with `%s`

Never use string formatting (f-strings) to put user data directly into a query. It is dangerous (SQL Injection).
Instead, use the %s placeholder.