Performing CRUD (Create, Read, Update, Delete) operations in Python with MySQL

Performing CRUD operations in Python with MySQL is a key skill for developing data-driven applications. Here's a complete, step-by-step tutorial using mysql-connector-python.

Prerequisites

Install MySQL Connector:

pip install mysql-connector-python

Start MySQL Server and create a database (or do it via Python as shown below).

Step-by-Step: CRUD Operations

1. Connect to MySQL

import mysql.connector

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

2. Create Table (if not exists)

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
)
""")

CRUD Operations in Python

C - Create (Insert Data)

sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("Alice", "alice@example.com")
cursor.execute(sql, val)
conn.commit()
print("Inserted:", cursor.rowcount)

R - Read (Select Data)

cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

for row in results:
    print(row)

U - Update Data

sql = "UPDATE users SET email = %s WHERE name = %s"
val = ("alice.new@example.com", "Alice")
cursor.execute(sql, val)
conn.commit()
print("Updated:", cursor.rowcount)

D - Delete Data

sql = "DELETE FROM users WHERE name = %s"
val = ("Alice",)
cursor.execute(sql, val)
conn.commit()
print("Deleted:", cursor.rowcount)

Close the Connection

cursor.close()
conn.close()

Best Practices

Always commit after INSERT, UPDATE, or DELETE.
Use parameterized queries to prevent SQL injection.
Use try-except blocks for error handling.
Use context managers or utility functions in production.

Example Output

Inserted: 1
(1, 'Alice', 'alice@example.com')
Updated: 1
Deleted: 1