MySQL CRUD Tutorials in Python: A Step-by-Step Guide

CRUD operations (Create, Read, Update, Delete) are fundamental when working with databases. In this tutorial, we’ll explore how to perform CRUD operations using MySQL in a Python application. We’ll cover each step and provide practical examples with explanations to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

MySQL Server: MySQL should be installed and running. You can download it from the official MySQL website.
Python: Ensure you have Python installed on your system. You can download Python from the official Python website.
MySQL Connector: Install the mysql-connector-python package, which is the official MySQL connector for Python. You can install it using pip:
```
pip install mysql-connector-python
```

Step 1: Connecting to MySQL

To use MySQL in a Python application, establish a connection to the database. Replace the placeholders with your MySQL server and database details.

import mysql.connector

# Database configuration
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database_name'
}

# Create a connection
try:
    connection = mysql.connector.connect(**db_config)

    if connection.is_connected():
        print("Connected to MySQL")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 2: Creating a Table

Let’s create a simple users table to demonstrate CRUD operations.

try:
    cursor = connection.cursor()

    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL
    )
    """

    cursor.execute(create_table_query)
    print("Table 'users' created successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 3: Inserting Data

Now, let’s insert a new user into the users table.

try:
    cursor = connection.cursor()

    insert_query = "INSERT INTO users (username, email) VALUES (%s, %s)"
    data = ("john_doe", "[email protected]")

    cursor.execute(insert_query, data)
    connection.commit()
    print("Data inserted successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 4: Querying Data

Retrieve data from the users table.

try:
    cursor = connection.cursor()

    select_query = "SELECT * FROM users"

    cursor.execute(select_query)

    for (id, username, email) in cursor:
        print(f"ID: {id}, Username: {username}, Email: {email}")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 5: Updating Data

Update a user’s email in the users table.

try:
    cursor = connection.cursor()

    update_query = "UPDATE users SET email = %s WHERE username = %s"
    data = ("[email protected]", "john_doe")

    cursor.execute(update_query, data)
    connection.commit()
    print("Data updated successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 6: Deleting Data

Delete a user from the users table.

try:
    cursor = connection.cursor()

    delete_query = "DELETE FROM users WHERE username = %s"
    data = ("john_doe",)

    cursor.execute(delete_query, data)
    connection.commit()
    print("Data deleted successfully")

except mysql.connector.Error as e:
    print(f"Error: {e}")

Step 7: Error Handling and Cleanup

Proper error handling is crucial when working with databases. Close the database connection when done.

finally:
    if 'cursor' in locals():
        cursor.close()

    if 'connection' in locals() and connection.is_connected():
        connection.close()
        print("Connection closed")

Conclusion

In this tutorial, we’ve covered the basics of performing CRUD operations using MySQL in a Python application. You’ve learned how to connect to MySQL, create a table, insert data, query data, update records, and delete records. These fundamental skills will serve as a solid foundation for building more complex database-driven applications with MySQL and Python.