SQL with Python — Connect, Query, and Automate School Data

The school wants a repeatable class report in a Python script rather than manually copying a query into an editor each time.

Python database drivers connect code to a database, bind values to SQL statements and return rows for the program to process.

Open a connection, execute a parameterized statement, fetch the results and close resources. Python sqlite3 uses question-mark or named placeholders; MySQL Connector/Python uses its documented placeholder convention. Transaction behavior should be chosen explicitly rather than inferred from another driver’s examples.

Related concepts: Prevent SQL Injection and Transactions and Connect with JDBC and ODBC.

• Automate data entry and reporting
• 🔍 Query large datasets with SQL’s power
• 🤖 Build school applications like report cards, dashboards, or fee trackers

Python · sqlite3 or MySQL driver as specified

Step 1: Import sqlite3

import sqlite3

Step 2: Connect to a database

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

Step 3: Create a table

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
  roll_no INTEGER PRIMARY KEY,
  name TEXT,
  class TEXT,
  city TEXT
)
""")
conn.commit()

Step 4: Insert student data

cursor.execute("INSERT INTO students (roll_no, name, class, city) VALUES (?, ?, ?, ?)", 
                (1, 'Aarav', '10A', 'Pune'))
conn.commit()

Step 5: Query the data

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

(1, 'Aarav', '10A', 'Pune')

Python · sqlite3 or MySQL driver as specified

Step 1: Install package

pip install mysql-connector-python

Step 2: Connect to MySQL server

import mysql.connector

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

Step 3: Fetch student marks

cursor.execute("SELECT name, marks FROM marks WHERE subject = 'Maths'")
results = cursor.fetchall()

for name, marks in results:
    print(f"{name} scored {marks} in Maths")

Aarav scored 90 in Maths  
Diya scored 85 in Maths

Python · sqlite3 or MySQL driver as specified

name = "Aarav"
cursor.execute("SELECT * FROM students WHERE name = %s", (name,))
print(cursor.fetchall())

• Auto-inserting daily attendance from a CSV file
• 📑 Generating report cards and emailing them to parents
• Bulk importing exam scores
• Visualizing student performance using matplotlib + SQL

• Always close your DB connection with conn.close()
• Use try/except for error handling
• Never use plain strings to insert values — always parameterize

Do not build SQL with Python string interpolation for user values. Closing a connection and committing a transaction are different actions. A sqlite3 connection context manager manages commit/rollback but does not automatically close the connection; use an explicit close or contextlib.closing.

Bind 10A as a Python sqlite3 query value and predict the returned names.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

Bind the value "10A" through the driver’s parameter API. The question mark is a parameter marker, not literal SQL text to replace with string concatenation.

SELECT name FROM practice_students WHERE class = ? ORDER BY roll_no;

The placeholder takes one value separately from the SQL text. Fetching returns two rows, and resource cleanup is still the application’s responsibility.

Python database drivers connect code to a database, bind values to SQL statements and return rows for the program to process.

The placeholder takes one value separately from the SQL text. Fetching returns two rows, and resource cleanup is still the application’s responsibility.

Continue your understanding with Prevent SQL Injection and Transactions and Connect with JDBC and ODBC.

Source for the engine-specific behavior: Python sqlite3 documentation.