Python MySQL


 

Using MySQL with Python

Integrating MySQL with Python allows you to interact with MySQL databases, performing operations such as querying, inserting, updating, and deleting data. This is commonly done using the mysql-connector-python library or PyMySQL.

Key Concepts

  • Connection: Establishing a connection to the MySQL database.
  • Cursor: A pointer used to interact with the database, allowing you to execute SQL queries.
  • Transactions: Handling changes to the database, which can be committed or rolled back.

Installation

You need to install the MySQL connector library. You can do this using pip:

bash
pip install mysql-connector-python

Tutorial: Basic Operations with MySQL in Python

Here's a step-by-step guide on how to connect to a MySQL database and perform basic operations.

1. Import Required Libraries

import mysql.connector
from mysql.connector import Error

2. Establishing a Connection

You can connect to your MySQL database using the following code:

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            password=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection

# Example connection
connection = create_connection("localhost", "your_username", "your_password", "your_database")

3. Creating a Table

You can create a table using a cursor:

def create_table(connection):
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT,
        gender ENUM('Male', 'Female') NOT NULL
    );
    """
    cursor = connection.cursor()
    try:
        cursor.execute(create_table_query)
        print("Table created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Create the table
create_table(connection)

4. Inserting Data

You can insert data into the table as follows:

def insert_user(connection, name, age, gender):
    insert_user_query = f"""
    INSERT INTO users (name, age, gender) VALUES ('{name}', {age}, '{gender}');
    """
    cursor = connection.cursor()
    try:
        cursor.execute(insert_user_query)
        connection.commit()
        print("User inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Insert a user
insert_user(connection, "Alice", 30, "Female")

5. Querying Data

You can retrieve data from the table:

def fetch_users(connection):
    fetch_users_query = "SELECT * FROM users;"
    cursor = connection.cursor()
    cursor.execute(fetch_users_query)
    results = cursor.fetchall()
    for row in results:
        print(row)

# Fetch users
fetch_users(connection)

6. Updating Data

You can update existing records in the table:

def update_user_age(connection, user_id, new_age):
    update_user_query = f"""
    UPDATE users SET age = {new_age} WHERE id = {user_id};
    """
    cursor = connection.cursor()
    try:
        cursor.execute(update_user_query)
        connection.commit()
        print("User age updated successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Update a user's age
update_user_age(connection, 1, 31)

7. Deleting Data

You can delete records from the table:

def delete_user(connection, user_id):
    delete_user_query = f"DELETE FROM users WHERE id = {user_id};"
    cursor = connection.cursor()
    try:
        cursor.execute(delete_user_query)
        connection.commit()
        print("User deleted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Delete a user
delete_user(connection, 1)

8. Closing the Connection

Always remember to close the connection when you're done.

if connection.is_connected():
    connection.close()
    print("Connection closed")

Conclusion

In this tutorial, we:

  1. Established a connection to a MySQL database using Python.
  2. Created a table, inserted data, queried data, updated records, and deleted records.
  3. Managed the database connection effectively.

Using MySQL with Python provides a robust way to handle data for applications, allowing for efficient data manipulation and retrieval.