Python for Data Science & Automation · Module 7: Python SQL & Database Integration · Lesson 29 of 34

7.2 Connecting Python to MySQL

7.2 Connecting Python to MySQL

MySQL is a widely used relational database management system (RDBMS) for applications that require persistent, structured, multi-user data storage.

Python can communicate with MySQL through a database driver. This allows Python programs to create tables, insert records, execute SQL queries, update data, and build database-driven applications.

Why connect Python to MySQL?

Python is useful for application logic, automation, analytics, and data processing, while MySQL provides persistent relational data storage. Together they form a powerful application and data-processing stack.

1. Learning Objectives

By the end of this lesson, you will be able to:

  • Understand Python–MySQL architecture.
  • Install a MySQL connector for Python.
  • Connect Python to a MySQL server.
  • Create databases and tables.
  • Insert records from Python.
  • Retrieve records using SQL.
  • Update and delete records.
  • Use parameterized queries.
  • Work with transactions.
  • Handle database errors.
  • Use connection and cursor objects correctly.
  • Retrieve generated primary keys.
  • Execute joins and aggregate queries.
  • Use MySQL with pandas.
  • Build a practical Python–MySQL application.

2. Python–MySQL Architecture

Python Application
        |
        v
MySQL Connector
        |
        v
MySQL Server
        |
        v
MySQL Database
        |
        +---- Tables
        +---- Indexes
        +---- Relationships
        +---- Records

Python sends SQL statements through a MySQL database driver. MySQL processes the statements and returns results or status information to the Python program.

3. MySQL vs SQLite

Feature SQLite MySQL
Architecture Embedded database Client-server database
Separate server Not normally required Required
Storage Usually a database file Managed by MySQL Server
Concurrent applications More limited Designed for multi-user workloads
Learning SQL Excellent Excellent
Large applications Situational Well suited
Python integration Built into Python Requires a connector/driver

4. Prerequisites

Before connecting Python to MySQL, you generally need:

  1. Python installed.
  2. A running MySQL Server.
  3. A MySQL database user.
  4. A database driver for Python.

The exact installation process can differ depending on the operating system and development environment.

5. Install MySQL Connector for Python

One commonly used official driver is mysql-connector-python. 

pip install mysql-connector-python

With Conda, an environment can also be used for managing Python dependencies. 

conda install -c conda-forge mysql-connector-python
Best Practice:

Install database packages inside a dedicated virtual or Conda environment when working on independent projects.

6. Import the Connector

import mysql.connector

The imported module provides the Python interface used to establish a connection with MySQL.

7. Connect to MySQL

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="school_db"
)

print("Connected successfully")

The connection parameters identify the MySQL server and database that the application should use.

8. Understanding Connection Parameters

Parameter Purpose
host MySQL server hostname or IP address
user MySQL username
password Password for authentication
database Database to use after connecting
port MySQL server port when a non-default port is used

9. Connecting Without Selecting a Database

A Python application can initially connect to the MySQL server without specifying a particular database. 

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password"
)

This is useful when the program needs to create or inspect databases before selecting one.

10. Create a Database

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password"
)

cursor = connection.cursor()

cursor.execute(
    "CREATE DATABASE IF NOT EXISTS school_db"
)

print("Database created")

11. Select the Database

connection.database = "school_db"

Alternatively, the database can be specified when establishing the connection. 

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="school_db"
)

12. Connection and Cursor Objects

Python database programs commonly work with two important objects.

Object Purpose
Connection Represents the connection to MySQL
Cursor Executes SQL statements and retrieves results 
cursor = connection.cursor()

13. Create a Table

sql = """
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
)
"""

cursor.execute(sql)

connection.commit()

MySQL supports explicit data types and constraints that define the structure and integrity of table data.

14. MySQL Table Structure

Column Data Type Constraint
id INT AUTO_INCREMENT PRIMARY KEY
name VARCHAR(100) NOT NULL
age INT Optional
course VARCHAR(100) Optional

15. Insert a Record

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

values = (
    "Alex Morgan",
    20,
    "Computer Science"
)

cursor.execute(
    sql,
    values
)

connection.commit()

The MySQL Connector/Python parameter marker is %s.

Important:

The %s used here is a database-driver parameter placeholder. It is not Python string interpolation.

16. Insert Multiple Records

students = [
    (
        "Alex Morgan",
        20,
        "Computer Science"
    ),
    (
        "Emma Carter",
        21,
        "Data Science"
    ),
    (
        "Noah Wilson",
        19,
        "Information Technology"
    )
]

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

cursor.executemany(
    sql,
    students
)

connection.commit()

executemany() is useful when the same statement needs to be executed with multiple sets of values.

17. Retrieve All Records

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:

    print(row)

fetchall() returns all rows from the current result set.

18. Retrieve One Record

cursor.execute(
    "SELECT * FROM students"
)

row = cursor.fetchone()

print(row)

fetchone() retrieves one row from the result set.

19. Retrieve Selected Columns

cursor.execute(
    """
    SELECT
        name,
        course
    FROM students
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

Selecting only the required columns is generally preferable to retrieving unnecessary data.

20. Filter Records with WHERE

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE age > %s
    """,
    (20,)
)

rows = cursor.fetchall()

for row in rows:

    print(row)

21. Sort Records with ORDER BY

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY age DESC
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

22. Limit Query Results

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY age DESC
    LIMIT 5
    """
)

rows = cursor.fetchall()

LIMIT restricts the number of rows returned by the query.

23. Update Records

sql = """
UPDATE students
SET course = %s
WHERE id = %s
"""

cursor.execute(
    sql,
    (
        "Artificial Intelligence",
        1
    )
)

connection.commit()
Warning:

Always verify the WHERE condition in an UPDATE statement. Without it, multiple or all records may be modified.

24. Delete Records

sql = """
DELETE FROM students
WHERE id = %s
"""

cursor.execute(
    sql,
    (3,)
)

connection.commit()

A carefully designed WHERE condition is essential when deleting records.

25. Parameterized Queries

Applications should not build SQL statements by concatenating untrusted user input.

Do not write database code like this:

name = input(
    "Enter name: "
)

sql = (
    "SELECT * FROM students "
    + "WHERE name = '"
    + name
    + "'"
)

cursor.execute(sql)

Use parameter binding instead:

name = input(
    "Enter name: "
)

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

rows = cursor.fetchall()
Security Principle:

Parameterized queries help prevent SQL injection by keeping SQL instructions separate from supplied values.

26. SQL Injection

SQL injection is a security vulnerability in which malicious input can alter the intended SQL statement when application code improperly constructs queries.

Parameterized queries should be the standard approach for supplying external values to SQL statements. 

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

27. Commit Database Changes

connection.commit()

Changes made by statements such as INSERT, UPDATE, and DELETE should be committed when the transaction has completed successfully.

28. Rollback Transactions

try:

    cursor.execute(
        """
        UPDATE students
        SET course = %s
        WHERE id = %s
        """,
        (
            "Data Science",
            1
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

rollback() can undo changes that have not yet been committed.

29. Handle Database Errors

import mysql.connector

try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="school_db"
        )
    )

    print(
        "Connection successful"
    )

except mysql.connector.Error as error:

    print(
        "Database error:",
        error
    )

Database applications should handle expected connection and query failures rather than allowing them to terminate the application without useful diagnostics.

30. Check Whether the Connection Is Active

if connection.is_connected():

    print(
        "MySQL connection is active"
    )

This can be useful when a program needs to verify the state of a connection before performing database operations.

31. Close Cursor and Connection

cursor.close()
connection.close()

Resources should be released when database operations are complete.

32. A Safe Basic Connection Pattern

import mysql.connector


connection = None
cursor = None

try:

    connection = (
        mysql.connector.connect(
            host="localhost",
            user="app_user",
            password="your_password",
            database="school_db"
        )
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        "SELECT * FROM students"
    )

    rows = cursor.fetchall()

    for row in rows:

        print(row)

except mysql.connector.Error as error:

    print(
        "Database error:",
        error
    )

finally:

    if cursor is not None:

        cursor.close()

    if connection is not None and connection.is_connected():

        connection.close()

33. Primary Key and AUTO_INCREMENT

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
)

AUTO_INCREMENT allows MySQL to generate successive numeric identifiers for new records.

34. Retrieve the Generated ID

sql = """
INSERT INTO students
(name, age, course)
VALUES (%s, %s, %s)
"""

cursor.execute(
    sql,
    (
        "James Anderson",
        20,
        "Data Science"
    )
)

student_id = (
    cursor.lastrowid
)

connection.commit()

print(
    "New student ID:",
    student_id
)

The generated identifier can be useful when subsequent database operations depend on the newly inserted record.

35. Aggregate Functions

cursor.execute(
    """
    SELECT
        COUNT(*) AS total_students,
        AVG(age) AS average_age,
        MIN(age) AS minimum_age,
        MAX(age) AS maximum_age
    FROM students
    """
)

result = cursor.fetchone()

print(result)

Common SQL aggregate functions include:

  • COUNT()
  • SUM()
  • AVG()
  • MIN()
  • MAX()

36. GROUP BY

cursor.execute(
    """
    SELECT
        course,
        COUNT(*) AS total_students
    FROM students
    GROUP BY course
    ORDER BY total_students DESC
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

37. HAVING

cursor.execute(
    """
    SELECT
        course,
        COUNT(*) AS total_students
    FROM students
    GROUP BY course
    HAVING COUNT(*) > 1
    """
)

WHERE filters rows, whereas HAVING filters groups created by aggregation.

38. Multiple Related Tables

Relational database designs commonly divide information into separate but related tables. 

students
---------------------
id
name
course_id


courses
---------------------
id
course_name

A foreign key can establish the relationship between the two tables.

39. Foreign Key

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
)

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id)
        REFERENCES courses(id)
)

Foreign keys help maintain referential integrity between related tables.

40. INNER JOIN

cursor.execute(
    """
    SELECT
        students.name,
        courses.course_name
    FROM students
    INNER JOIN courses
        ON students.course_id = courses.id
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

The query returns matching records from both tables.

41. LEFT JOIN

cursor.execute(
    """
    SELECT
        students.name,
        courses.course_name
    FROM students
    LEFT JOIN courses
        ON students.course_id = courses.id
    """
)

rows = cursor.fetchall()

A LEFT JOIN keeps rows from the left table even when a matching record is not found in the right table.

42. MySQL Data Types

Type Example Use
INT Whole numbers
DECIMAL Exact numeric values such as prices
FLOAT / DOUBLE Floating-point numerical values
VARCHAR Variable-length text
TEXT Larger text content
DATE Calendar dates
DATETIME Date and time values
BOOLEAN Logical values

43. Store Dates and Times

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    created_at DATETIME
)

Date and time columns are important in reporting, scheduling, logging, and automation applications.

44. Environment Variables for Credentials

Database passwords should not normally be hard-coded into source code that is shared, committed to version control, or deployed. 

import os
import mysql.connector


connection = mysql.connector.connect(
    host=os.environ["MYSQL_HOST"],
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"]
)
Security Principle:

Keep credentials outside source code and restrict database accounts to the permissions required by the application.

45. Why Database Users Need Appropriate Permissions

An application should not automatically receive unrestricted database privileges.

A production application may only require permissions such as:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Administrative operations should be restricted to appropriate database administrators or deployment processes.

46. MySQL with pandas

MySQL can be combined with pandas for data-analysis workflows. 

import pandas as pd
import mysql.connector


connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="school_db"
)

query = """
SELECT
    id,
    name,
    age,
    course
FROM students
"""

df = pd.read_sql(
    query,
    connection
)

print(df)

The resulting DataFrame can then be processed using the pandas techniques learned in the earlier modules.

47. Parameterized Query with pandas

query = """
SELECT *
FROM students
WHERE age > %s
"""

df = pd.read_sql(
    query,
    connection,
    params=(20,)
)

print(df)

Parameters should still be used when external values are supplied to SQL queries executed through pandas.

48. MySQL to Data Science Workflow

MySQL Database
      ↓
SQL Query
      ↓
Python Connector
      ↓
pandas DataFrame
      ↓
Data Cleaning
      ↓
EDA
      ↓
Visualization
      ↓
Report

This workflow connects database engineering with the data-science skills covered earlier in this course.

49. MySQL in Automation

External Data
      ↓
Python Script
      ↓
Validation
      ↓
MySQL
      ↓
SQL Analysis
      ↓
pandas
      ↓
Excel / PDF
      ↓
Email

This architecture is useful when an automation process needs persistent storage rather than repeatedly creating standalone files.

50. Mini Project — Student Management Database

Build a simple Python application that stores and manages student information in MySQL.

Database Design

students
--------------------------------
id
name
age
course
email
created_at

51. Step 1 — Connect to MySQL

import mysql.connector


connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="school_db"
)

cursor = connection.cursor()

52. Step 2 — Create the Table

cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT,
        course VARCHAR(100),
        email VARCHAR(150) UNIQUE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """
)

connection.commit()

53. Step 3 — Insert Students

students = [
    (
        "Alex Morgan",
        20,
        "Computer Science",
        "alex@example.com"
    ),
    (
        "Emma Carter",
        21,
        "Data Science",
        "emma@example.com"
    ),
    (
        "Noah Wilson",
        19,
        "Information Technology",
        "noah@example.com"
    )
]

sql = """
INSERT INTO students
(name, age, course, email)
VALUES (%s, %s, %s, %s)
"""

cursor.executemany(
    sql,
    students
)

connection.commit()

54. Step 4 — Display Students

cursor.execute(
    """
    SELECT
        id,
        name,
        age,
        course,
        email
    FROM students
    ORDER BY name
    """
)

rows = cursor.fetchall()

for row in rows:

    print(row)

55. Step 5 — Search Students

search_term = input(
    "Enter student name: "
)

cursor.execute(
    """
    SELECT *
    FROM students
    WHERE name LIKE %s
    """,
    (
        f"%{search_term}%",
    )
)

rows = cursor.fetchall()

for row in rows:

    print(row)

56. Step 6 — Update a Student

cursor.execute(
    """
    UPDATE students
    SET course = %s
    WHERE id = %s
    """,
    (
        "Artificial Intelligence",
        1
    )
)

connection.commit()

57. Step 7 — Delete a Student

cursor.execute(
    """
    DELETE FROM students
    WHERE id = %s
    """,
    (3,)
)

connection.commit()

58. Complete CRUD Program

import mysql.connector


DB_CONFIG = {
    "host": "localhost",
    "user": "app_user",
    "password": "your_password",
    "database": "school_db"
}


def get_connection():

    return mysql.connector.connect(
        **DB_CONFIG
    )


def create_table():

    connection = (
        get_connection()
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT,
            course VARCHAR(100),
            email VARCHAR(150) UNIQUE,
            created_at DATETIME
                DEFAULT CURRENT_TIMESTAMP
        )
        """
    )

    connection.commit()

    cursor.close()
    connection.close()


def add_student(
    name,
    age,
    course,
    email
):

    connection = (
        get_connection()
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        INSERT INTO students
        (name, age, course, email)
        VALUES (%s, %s, %s, %s)
        """,
        (
            name,
            age,
            course,
            email
        )
    )

    connection.commit()

    cursor.close()
    connection.close()


def get_students():

    connection = (
        get_connection()
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        SELECT *
        FROM students
        ORDER BY name
        """
    )

    rows = cursor.fetchall()

    cursor.close()
    connection.close()

    return rows


def update_course(
    student_id,
    course
):

    connection = (
        get_connection()
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        UPDATE students
        SET course = %s
        WHERE id = %s
        """,
        (
            course,
            student_id
        )
    )

    connection.commit()

    cursor.close()
    connection.close()


def delete_student(
    student_id
):

    connection = (
        get_connection()
    )

    cursor = (
        connection.cursor()
    )

    cursor.execute(
        """
        DELETE FROM students
        WHERE id = %s
        """,
        (student_id,)
    )

    connection.commit()

    cursor.close()
    connection.close()


create_table()

add_student(
    "Alex Morgan",
    20,
    "Computer Science",
    "alex@example.com"
)

add_student(
    "Emma Carter",
    21,
    "Data Science",
    "emma@example.com"
)

update_course(
    1,
    "Artificial Intelligence"
)

for student in get_students():

    print(student)

59. CRUD Operations in Python–MySQL

Operation SQL Purpose
Create INSERT Add records
Read SELECT Retrieve records
Update UPDATE Modify records
Delete DELETE Remove records

60. Common Python–MySQL Methods

Method / Property Purpose
mysql.connector.connect() Create a MySQL connection
connection.cursor() Create a cursor
cursor.execute() Execute one SQL statement
cursor.executemany() Execute a statement with multiple parameter sets
cursor.fetchone() Retrieve one result row
cursor.fetchall() Retrieve all result rows
connection.commit() Commit changes
connection.rollback() Rollback uncommitted changes
cursor.lastrowid Retrieve the generated row identifier
connection.close() Close the connection

61. SQLite vs MySQL: When to Choose Which?

Scenario Possible Choice
Small local script SQLite
Learning SQL locally SQLite or MySQL
Multi-user application MySQL
Centralized application database MySQL
Simple local data storage SQLite
Web application backend MySQL can be appropriate
Large-scale relational workload Evaluate a server-based RDBMS

Database selection should ultimately depend on workload, concurrency, deployment architecture, operational requirements, scalability, and application needs.

62. Best Practices

  1. Use parameterized queries.
  2. Never hard-code production passwords in source code.
  3. Use least-privilege database accounts.
  4. Commit transactions deliberately.
  5. Roll back failed transactions where appropriate.
  6. Close cursors and connections.
  7. Validate external input.
  8. Use meaningful table and column names.
  9. Define primary and foreign keys appropriately.
  10. Add indexes based on actual query requirements.
  11. Keep database logic separate from application logic.
  12. Maintain database backups.
  13. Log important database failures in automated systems.

63. Common Mistakes

Mistake Problem Better Approach
Hard-coded credentials Credentials can be exposed Use environment variables or a secret-management solution
SQL string concatenation SQL injection risk Use parameterized queries
No transaction handling Partial changes may remain Use commit and rollback appropriately
Missing WHERE condition Unintended mass update/delete Verify SQL conditions
Leaving connections open Resources can be exhausted Close database resources
Excessive SELECT * Unnecessary data retrieval Select only required columns
No indexes on important query paths Queries can become inefficient Evaluate appropriate indexes

64. Python–MySQL Interview Questions

Q1. What is MySQL?

View Answer

MySQL is a relational database management system used to store, organize, query, and manage structured data.

Q2. Which Python package can connect Python to MySQL?

View Answer

mysql-connector-python provides a commonly used MySQL driver for Python.

Q3. What is the purpose of a cursor?

View Answer

A cursor executes SQL statements and retrieves query results.

Q4. Why is commit() required?

View Answer

It commits successful transaction changes so that they are persisted in the database.

Q5. Why should parameterized SQL be used?

View Answer

Parameterized SQL separates values from SQL syntax and helps prevent SQL injection.

Q6. What does executemany() do?

View Answer

It executes the same SQL operation with multiple sets of parameter values.

Q7. What is AUTO_INCREMENT?

View Answer

It allows MySQL to automatically generate successive numeric values for an appropriate integer column.

Q8. What is a foreign key?

View Answer

A foreign key references a key in another table and helps establish a relationship between tables.

Q9. How can MySQL data be loaded into pandas?

View Answer

A MySQL connection can be used with pandas SQL-reading functionality to execute a query and create a DataFrame.

Q10. What is the difference between commit and rollback?

View Answer

commit() saves the transaction's changes, while rollback() reverses uncommitted transaction changes.

65. Examination Questions — MCQs

Q1. Which package can be installed to connect Python to MySQL?

  1. mysql-connector-python
  2. mysql-python-database
  3. python-mysql-server
  4. mysql-connect-db

Answer: A

Q2. Which function establishes a MySQL connection?

  1. mysql.connector.connect()
  2. mysql.open()
  3. mysql.database()
  4. mysql.start()

Answer: A

Q3. Which method executes an SQL statement?

  1. cursor.execute()
  2. cursor.run()
  3. cursor.sql()
  4. cursor.query()

Answer: A

Q4. Which method saves database modifications?

  1. save()
  2. commit()
  3. store()
  4. write()

Answer: B

Q5. Which placeholder is used by MySQL Connector/Python for parameter values?

  1. ?
  2. %s
  3. {}
  4. $value

Answer: B

Q6. Which SQL clause filters individual rows?

  1. WHERE
  2. HAVING
  3. GROUP BY
  4. ORDER BY

Answer: A

Q7. Which SQL operation combines records from related tables?

  1. JOIN
  2. CONNECTROW
  3. LINK
  4. ATTACHROW

Answer: A

Q8. Which method retrieves all rows from a query result?

  1. fetchall()
  2. getrows()
  3. readall()
  4. rows()

Answer: A

Q9. Which constraint uniquely identifies a record?

  1. DEFAULT
  2. PRIMARY KEY
  3. INDEX ONLY
  4. TEXT

Answer: B

Q10. Which function can load SQL results into a pandas DataFrame?

  1. pd.read_sql()
  2. pd.mysql_import()
  3. pd.read_mysql_file()
  4. pd.load_database()

Answer: A

66. Practical Examination Tasks

Task 1 — Database Setup

Connect Python to MySQL and create a database named inventory_db.

Task 2 — Table Creation

Create a products table containing an ID, product name, category, price, and quantity.

Task 3 — CRUD

Implement Python functions for inserting, reading, updating, and deleting products.

Task 4 — Search

Create a parameterized search that finds products by category.

Task 5 — Aggregation

Calculate total inventory value using an SQL aggregate expression.

Task 6 — pandas Integration

Load the product data into a pandas DataFrame and calculate summary statistics.

67. Coding Challenge — Product Inventory

Build a Python application connected to MySQL with the following table: 

products
--------------------------------
id
name
category
price
quantity
created_at

Requirements

  1. Create the database and table.
  2. Insert at least ten products.
  3. Display all products.
  4. Search products by category.
  5. Find products above a specified price.
  6. Update product quantities.
  7. Delete a product.
  8. Calculate total inventory value.
  9. Load the data into pandas.
  10. Export an analysis report to Excel.

68. Real-World Python–MySQL Workflow

Data Source
     ↓
Python Application
     ↓
Validation
     ↓
Parameterized SQL
     ↓
MySQL Database
     ↓
SQL Queries
     ↓
pandas
     ↓
Analysis
     ↓
Excel / PDF / Dashboard

This architecture is particularly useful for applications that continuously collect, store, process, and report structured data.

69. Best Practices Checklist

  • ☐ Use parameterized queries.
  • ☐ Keep credentials out of source code.
  • ☐ Use least-privilege database accounts.
  • ☐ Validate external input.
  • ☐ Use transactions where appropriate.
  • ☐ Commit successful changes.
  • ☐ Roll back failed transactions.
  • ☐ Close cursors and connections.
  • ☐ Define appropriate keys and constraints.
  • ☐ Use indexes based on actual query requirements.
  • ☐ Back up important databases.
  • ☐ Separate database logic from application logic.

70. Key Takeaway

Python and MySQL complement each other well: Python handles application logic, automation, data processing, and analysis, while MySQL provides centralized relational data storage.

Mastering connections, cursors, CRUD operations, parameterized queries, transactions, relationships, and pandas integration provides a strong foundation for real-world database-driven Python applications.

Start This Course From the Beginning →