Back to Python
2026-02-037 min read

MySQL HOME (Python Programming)

Learn MySQL HOME (Python Programming) step by step with clear examples and exercises.

Title: MySQL HOME (Python Programming)

Why This Matters

In this comprehensive lesson, we will delve into using MySQL with Python for managing database tasks such as creating, reading, updating, and deleting records. This skill is indispensable for web development projects that involve handling vast amounts of data in a structured format and for interview preparation, as well as real-world scenarios where you might encounter databases powered by MySQL.

Prerequisites

Before diving into the core concept, ensure you have the following prerequisites:

  1. A basic understanding of Python syntax and data structures (lists, tuples, dictionaries)
  2. Familiarity with SQL queries and database concepts (tables, columns, rows, primary keys, foreign keys)
  3. Installation of MySQL server on your local machine or access to a remote MySQL server
  4. Python's mysql-connector-python package installed (pip install mysql-connector-python)
  5. Understanding of how to navigate the terminal/command prompt and basic file manipulation in Python
  6. Familiarity with creating and managing databases using MySQL Workbench or similar tools
  7. Knowledge of how to write SQL queries for various database operations (e.g., SELECT, INSERT, UPDATE, DELETE)

Core Concept

In this section, we will cover the essential steps for connecting to a MySQL database using Python and performing basic CRUD operations.

Connecting to the Database

To connect to a MySQL database in Python, you'll need to use the mysql-connector-python package. First, import the necessary module:

import mysql.connector

Next, create a connection object and specify your database credentials (host, user, password, and database name):

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)

Creating a Table

Once connected to the database, you can create a new table using the cursor() method:

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, position VARCHAR(255), age INT, favorite_subject VARCHAR(255))")

Inserting Records

To insert records into the table, use the execute() method again with an SQL INSERT statement:

mycursor.execute("INSERT INTO employees (name, position, age, favorite_subject) VALUES (%s, %s, %s, %s)", ("John Doe", "Software Engineer", 25, "Computer Science"))

Reading Records

To read records from the table, use the fetchall() method:

mycursor.execute("SELECT * FROM employees")
employees = mycursor.fetchall()
for employee in employees:
print(employee)

Updating Records

To update an existing record, use the execute() method with an SQL UPDATE statement:

mycursor.execute("UPDATE employees SET position = %s, age = %s WHERE name = %s", ("Senior Software Engineer", 26, "John Doe"))

Deleting Records

To delete a record, use the execute() method with an SQL DELETE statement:

mycursor.execute("DELETE FROM employees WHERE name = %s", ("John Doe",))

Worked Example

In this example, we will create a simple Python script that connects to a MySQL database, creates a table called employees, inserts records for three employees with their names, positions, ages, and favorite subjects. The script also demonstrates reading the records, updating an employee's record, and deleting an employee from the table.

import mysql.connector

mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)

mycursor = mydb.cursor()

Create table

mycursor.execute("CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, position VARCHAR(255), age INT, favorite_subject VARCHAR(255))")

Insert records

mycursor.execute("INSERT INTO employees (name, position, age, favorite_subject) VALUES (%s, %s, %s, %s)", ("John Doe", "Software Engineer", 25, "Computer Science"))

mycursor.execute("INSERT INTO employees (name, position, age, favorite_subject) VALUES (%s, %s, %s, %s)", ("Jane Smith", "Data Analyst", 23, "Mathematics"))

mycursor.execute("INSERT INTO employees (name, position, age, favorite_subject) VALUES (%s, %s, %s, %s)", ("Alice Johnson", "Project Manager", 30, "Business Administration"))

Commit changes to ensure they are saved in the database

mydb.commit()

Read records

mycursor.execute("SELECT * FROM employees")

employees = mycursor.fetchall()

for employee in employees:

print(employee)

Update an employee's record (John Doe's position to "Team Lead")

mycursor.execute("UPDATE employees SET position = %s WHERE name = %s", ("Team Lead", "John Doe"))

Commit changes to ensure they are saved in the database

mydb.commit()

Read records again to show the update

mycursor.execute("SELECT * FROM employees")

updated_employees = mycursor.fetchall()

for employee in updated_employees:

print(employee)

Delete an employee (Alice Johnson)

mycursor.execute("DELETE FROM employees WHERE name = %s", ("Alice Johnson",))

Commit changes to ensure they are saved in the database

mydb.commit()

Read records again to show the deletion

mycursor.execute("SELECT * FROM employees")

deleted_employees = mycursor.fetchall()

for employee in deleted_employees:

print(employee)

Common Mistakes

  1. Forgetting to import the mysql-connector-python module
  2. Incorrect database credentials (host, user, password, and database name)
  3. Not using placeholders (%s) in SQL statements for parameterized queries
  4. Missing or incorrect table names in SQL statements
  5. Forgetting to commit changes after executing multiple SQL statements
  6. Using the wrong data type for a column (e.g., storing text in an INT column)
  7. Not handling exceptions properly when working with MySQL operations
  8. Not closing the database connection after use
  9. Forgetting to define primary keys or unique constraints for columns
  10. Incorrectly handling NULL values in SQL statements (e.g., forgetting to use IS NULL or COALESCE)

Practice Questions

  1. Write a Python script that connects to a MySQL database, creates a new table called students, and inserts records for three students with their names, ages, and favorite subjects.
  2. Modify the provided worked example to add a new employee named "Bob Brown" with the position of "Database Administrator," age 35, and favorite subject "Database Management."
  3. Write an SQL SELECT statement that retrieves the name, position, and age of all employees from the employees table whose age is greater than 25.
  4. Write an SQL UPDATE statement that sets the favorite subject of the employee named "Jane Smith" to "Statistics."
  5. Write an SQL DELETE statement that removes the employee with the name "Bob Brown" from the employees table.
  6. Write a Python script that connects to a MySQL database, creates a new table called products, and inserts records for three products with their names, prices, and categories.
  7. Modify the provided worked example to display the total number of employees in the employees table using an SQL COUNT statement.
  8. Write an SQL JOIN statement that retrieves the name, position, and favorite subject of all employees from the employees table and their corresponding product names from the products table for employees who are also customers (assume there is a customer_id column in both tables).
  9. Write an SQL INSERT statement that adds a new employee named "Charlie Davis" with the position of "Quality Assurance," age 28, favorite subject "Software Testing," and customer ID 101 to the employees table.
  10. Write an SQL UPDATE statement that increases the price of all products in the products table by 10%.

FAQ

  1. How can I handle errors when working with MySQL in Python?
  • Use try-except blocks to catch and handle exceptions raised by MySQL operations.
  1. How can I handle multiple tables in a single Python script?
  • Create separate cursor objects for each table or use multiple SQL statements with different table names.
  1. What is the difference between AUTO_INCREMENT and PRIMARY KEY in MySQL?
  • AUTO_INCREMENT is a column attribute that automatically increments the value of a column for new rows. PRIMARY KEY is a constraint that ensures uniqueness and integrity of data in a table.
  1. How can I handle transactions in MySQL using Python?
  • Use the start_transaction(), commit(), and rollback() methods provided by the cursor object to manage transactions.
  1. What is the purpose of parameterized queries in MySQL?
  • Parameterized queries help prevent SQL injection attacks by separating the SQL code from user-supplied data.
  1. How can I handle NULL values in SQL statements?
  • Use IS NULL or COALESCE to handle NULL values in SQL statements.
  1. What is the purpose of the commit() method in Python MySQL operations?
  • The commit() method saves any changes made to the database since the last commit or rollback.
  1. How can I close a database connection in Python after use?
  • Use the close() method provided by the connection object (mydb.close()).
  1. What is the purpose of the cursor object in Python MySQL operations?
  • The cursor object acts as an interface between your Python script and the MySQL server, executing SQL statements and fetching results.
  1. How can I optimize MySQL queries for better performance?
  • Use indexes on frequently queried columns, limit the number of rows returned in SELECT statements, and avoid using SELECT * from table; instead, specify only the required columns.
MySQL HOME (Python Programming) | Python | XQA Learn