Module 5 • Week 15 • Relational schemas, cursor statements, commits, and queries connection

MySQL Databases & SQL

Connect Python to MySQL databases. Learn to execute SQL statements, cursor controls, and fetch records.

1. Relational Databases vs. Text Files

For large applications, storing data in text files is slow. We use relational databases like MySQL. Relational databases store data in structured tables consisting of rows and columns.

2. Connecting Python to MySQL

We use the mysql.connector package. The steps to execute SQL queries in Python are:

Establish connection: Connect to the database server using credentials (host, user, password, database).
Allocate cursor: Create a cursor object: mycursor = connection.cursor().
Execute queries: Execute SQL statements using the cursor: mycursor.execute(sql).
Commit changes: For modifying queries (like insert, update, delete), you must run connection.commit() to save changes to the database.

💻 Python Code Examples

Example 1: Establishing Database Connection

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password"
)
print(mydb)

Description: Establishes a connection to a local MySQL server.

Example 2: Creating a Database

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE school_db")

Description: Creates a new database named school_db using the cursor.

Example 3: Creating relational tables

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="school_db"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE students (name VARCHAR(50), age INT)")

Description: Creates a student table with name and age columns.

Example 4: Inserting rows with committing

sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
val = ("Peter", 22)
mycursor.execute(sql, val)
mydb.commit()

Description: Inserts a record and commits the transaction to save changes to disk.

Example 5: Selecting table records

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

Description: Selects all records and fetches them as a list of tuples.

Example 6: Fetching a single record

mycursor.execute("SELECT * FROM students")
row = mycursor.fetchone()
print(row)

Description: Fetches only the first row matching the query.

🧠 Active Retrieval Quizzes

Answer the following 10 multiple-choice questions to test your storage strength.

Question 1: What package is used to connect Python to a MySQL database?

Explanation: We use the mysql.connector library to connect Python to MySQL databases.

Question 2: What is the purpose of a database cursor object in Python?

Explanation: A cursor executes SQL statements and fetches query results.

Question 3: Why must you run connection.commit() after executing an INSERT query?

Explanation: Modifications must be committed to save changes permanently to the database.

Question 4: What method fetches all results returned by a SELECT query?

Explanation: The fetchall() method returns all rows matching a query as a list of tuples.

Question 5: What does mycursor.fetchone() return if no more records are found?

Explanation: fetchone() returns None if no records match or if all records have already been fetched.

Question 6: What parameters are required to connect Python to a database using mysql.connector.connect()?

Explanation: At a minimum, connecting to a database server requires host, user, and password parameters.

Question 7: What data type is used to represent table rows returned by database fetch methods?

Explanation: Each row fetched from the database is returned as a tuple containing values for each column.

Question 8: How can you pass values dynamically into a SQL insert query to prevent SQL injection?

Explanation: Using %s placeholders separates query logic from data values, preventing SQL injection.

Question 9: What statement is executed to define a new table in SQL?

Explanation: The CREATE TABLE statement defines a new table structure in SQL.

Question 10: Which cursor method closes the cursor, freeing up system resources?

Explanation: The close() method closes the cursor, freeing up system resources.

🖥️ Output Prediction Challenges

Read each code snippet carefully, analyze its execution flow, and predict the exact string result written to the console.

Challenge 1 of 5

Predict the output of the code snippet below. Type the exact output in the console.

import mysql.connector
print("Database Link Active")