Python CRUD Operations in MySQL – Create, Read, Update & Delete

Introduction

MySQL is one of the most popular relational database management systems (RDBMS), while Python is a powerful programming language widely used for web development, automation, and data processing. By combining Python and MySQL, developers can build robust data-driven applications.

One of the most important concepts when working with databases is CRUD Operations. CRUD stands for:

• C – Create
• R – Read
• U – Update
• D – Delete

What are CRUD Operations?

CRUD operations represent the four basic functions used to manage data in a database.

Almost every application uses CRUD operations to manage data.

Prerequisites

Before performing CRUD operations, ensure the following:

• Python installed
• MySQL Server installed
• MySQL database created
• MySQL Connector installed

Install MySQL Connector:

pip install mysql-connector-python

Import MySQL Connector

import mysql.connector

Connecting Python to MySQL

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="1234",
    database="company"
)
print("Connected Successfully")

Output:

Creating a Cursor Object

A cursor is used to execute SQL queries.

cursor = conn.cursor()

Creating a Table

Let’s create an employee table.

query = """
CREATE TABLE employees(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
)
"""
cursor.execute(query)

CREATE Operation (Insert Data)

The Create operation inserts new records into a table.

Syntax

cursor.execute(sql_query)
conn.commit()

Example: Insert a Single Record

sql = """
INSERT INTO employees
(id, name, department, salary)
VALUES
(1, 'John', 'IT', 50000)
"""
cursor.execute(sql)
conn.commit()
print("Record Inserted")

Output:

Insert Multiple Records

sql = """
INSERT INTO employees
(id, name, department, salary)
VALUES (%s, %s, %s, %s)
"""
data = [
    (2, "Mike", "HR", 45000),
    (3, "Sara", "Finance", 60000),
    (4, "David", "IT", 55000)
]
cursor.executemany(sql, data)
conn.commit()

READ Operation (Fetch Data)

The Read operation retrieves data from a database.

Syntax

cursor.execute("SELECT * FROM table_name")

Fetch All Records

cursor.execute(
    "SELECT * FROM employees"
)
records = cursor.fetchall()
for record in records:
    print(record)

Output:

Fetch a Single Record

cursor.execute(
    "SELECT * FROM employees WHERE id=1"
)
record = cursor.fetchone()
print(record)

Output:

Fetch Employees from IT Department

cursor.execute(
    """
    SELECT *
    FROM employees
    WHERE department='IT'
    """
)
records = cursor.fetchall()
for record in records:
    print(record)

UPDATE Operation

The Update operation modifies existing records.

Syntax

UPDATE table_name
SET column=value
WHERE condition

Example: Update Employee Salary

sql = """
UPDATE employees
SET salary=70000
WHERE id=1
"""
cursor.execute(sql)
conn.commit()
print("Record Updated")

Output:

Update Department

sql = """
UPDATE employees
SET department='Marketing'
WHERE id=2
"""
cursor.execute(sql)
conn.commit()

DELETE Operation

The Delete operation removes records from a table.

Syntax

DELETE FROM table_name
WHERE condition

Example: Delete Employee

sql = """
DELETE FROM employees
WHERE id=4
"""
cursor.execute(sql)
conn.commit()
print("Record Deleted")

Output:

Delete Multiple Records

sql = """
DELETE FROM employees
WHERE department='HR'
"""
cursor.execute(sql)
conn.commit()

Complete CRUD Example

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="1234",
    database="company"
)
cursor = conn.cursor()

# Create

cursor.execute(
    """
    INSERT INTO employees
    VALUES
    (5,'Alex','IT',65000)
    """
)
conn.commit()

# Read

cursor.execute(
    "SELECT * FROM employees"
)
print(cursor.fetchall())

# Update

cursor.execute(
    """
    UPDATE employees
    SET salary=70000
    WHERE id=5
    """
)
conn.commit()

# Delete

cursor.execute(
    """
    DELETE FROM employees
    WHERE id=5
    """
)
conn.commit()
cursor.close()
conn.close()

Real-Life Example: Student Management System

A school management system performs CRUD operations regularly.

Create

Add a new student.

INSERT INTO students

Read

View student details.

SELECT * FROM students

Update

Modify student information.

UPDATE students

Delete

Remove student records.

DELETE FROM students

Why Use Parameterized Queries?

Instead of:

sql = f"""
INSERT INTO employees
VALUES({id}, '{name}')
"""

Use:

sql = """
INSERT INTO employees
VALUES(%s, %s)
"""
cursor.execute(
    sql,
    (id, name)
)

Benefits:

• Prevents SQL Injection
• Improves security
• Handles user input safely

Common Mistakes

1. Forgetting commit()

Incorrect:

cursor.execute(sql)

Correct:

cursor.execute(sql)
conn.commit()

2. Not Closing Connection

Incorrect:

conn = mysql.connector.connect(...)

Correct:

cursor.close()
conn.close()

3. Missing WHERE Clause

Dangerous:

DELETE FROM employees

This removes all records.

Use:

DELETE FROM employees
WHERE id=1

4. Using String Concatenation in SQL

Incorrect:

query = (
    "SELECT * FROM users "
    "WHERE name='" + name + "'"
)

This can lead to SQL injection.

5. Ignoring Exception Handling

Always use:

try:
    # database code
except Exception as e:
    print(e)

Best Practices

Conclusion

CRUD operations are the foundation of database management in Python applications. Using Python and MySQL together allows developers to efficiently create, read, update, and delete records while maintaining data integrity and security.

Related Python Tutorials