Using MySQL with Python - Rishan Solutions

MySQL is a popular open-source relational database management system (RDBMS) used for web applications, analytics, and enterprise applications. Python provides easy integration with MySQL through the mysql-connector-python or PyMySQL libraries.

1. Why Use MySQL with Python?

Scalability – Supports large datasets efficiently.
Security – Provides user authentication and encryption.
Multi-User Support – Handles multiple concurrent connections.
Cross-Platform – Works with Windows, macOS, and Linux.
Easy Integration – Connects with Python applications seamlessly.

2. Installing MySQL and Python Connector

Install MySQL Server

Download and install MySQL Server from MySQL Official Website.
Set up the root user and password during installation.

Install Python MySQL Connector

pip install mysql-connector-python

pip install pymysql

mysql-connector-python is an official MySQL package.
PyMySQL is an alternative pure-Python MySQL client.

3. Connecting to a MySQL Database

Example: Establishing a Connection

import mysql.connector

# Connect to MySQL Server
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)

cursor = conn.cursor()  # Create a cursor object
print("Connected to MySQL database successfully!")

Change host, user, password, and database as per your setup.
cursor helps execute SQL queries.

4. Creating a Database

Before creating tables, ensure the database exists.

cursor.execute("CREATE DATABASE IF NOT EXISTS testdb")
print("Database created successfully!")

CREATE DATABASE IF NOT EXISTS ensures no duplication.

To use the database:

cursor.execute("USE testdb")

5. Creating a Table in MySQL

Tables store structured data.

cursor.execute("""
CREATE TABLE IF NOT EXISTS Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(255) UNIQUE
)
""")
print("Users table created successfully!")

AUTO_INCREMENT auto-generates unique id values.
VARCHAR(100) defines text column length.
PRIMARY KEY ensures unique row identification.

6. Inserting Data into MySQL

Single Record Insertion

cursor.execute("""
INSERT INTO Users (name, age, email) VALUES (%s, %s, %s)
""", ("John Doe", 25, "john@example.com"))

conn.commit()  # Save changes
print("User added successfully!")

Use %s placeholders to prevent SQL injection.
Always commit (conn.commit()) to save changes.

Insert Multiple Records

users = [
    ("Alice", 30, "alice@example.com"),
    ("Bob", 22, "bob@example.com"),
    ("Charlie", 28, "charlie@example.com")
]
cursor.executemany("INSERT INTO Users (name, age, email) VALUES (%s, %s, %s)", users)
conn.commit()
print("Multiple users added successfully!")

executemany() inserts multiple rows efficiently.

7. Retrieving Data from MySQL

Fetching data is essential for application logic.

Retrieve All Users

cursor.execute("SELECT * FROM Users")
users = cursor.fetchall()  # Get all results

for user in users:
    print(user)  # Output each row

fetchall() returns a list of tuples.

Retrieve a Single Record

cursor.execute("SELECT * FROM Users WHERE name = %s", ("Alice",))
user = cursor.fetchone()  # Get the first match
print(user)

fetchone() returns the first result.

Retrieve Specific Columns

cursor.execute("SELECT name, age FROM Users")
names_and_ages = cursor.fetchall()
print(names_and_ages)

Fetching only required columns improves performance.

8. Updating Data in MySQL

Modify records using UPDATE.

cursor.execute("UPDATE Users SET age = %s WHERE name = %s", (35, "Alice"))
conn.commit()
print("User updated successfully!")

Always use commit() after updates.

9. Deleting Data in MySQL

Remove records using DELETE.

cursor.execute("DELETE FROM Users WHERE name = %s", ("Bob",))
conn.commit()
print("User deleted successfully!")

Deleting is permanent, so use it carefully.

10. Using Transactions in MySQL

Ensure data consistency using transactions.

try:
    conn.start_transaction()  # Start transaction
    cursor.execute("UPDATE Users SET age = 40 WHERE name = 'Charlie'")
    cursor.execute("DELETE FROM Users WHERE name = 'Alice'")
    conn.commit()  # Commit changes
    print("Transaction successful!")
except:
    conn.rollback()  # Undo changes if an error occurs
    print("Transaction failed! Rolling back changes.")

start_transaction() begins a transaction.
rollback() undoes changes if an error occurs.

11. Exporting and Importing MySQL Data

Export Data to CSV

import csv

cursor.execute("SELECT * FROM Users")
with open("users.csv", "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerow([desc[0] for desc in cursor.description])  # Column names
    writer.writerows(cursor.fetchall())

print("Data exported to users.csv!")

Import Data from CSV to MySQL

with open("users.csv", "r") as file:
    reader = csv.reader(file)
    next(reader)  # Skip header
    cursor.executemany("INSERT INTO Users (id, name, age, email) VALUES (%s, %s, %s, %s)", reader)

conn.commit()
print("Data imported successfully!")

Easily migrate MySQL data between databases using CSV files.

12. Closing MySQL Connection

Always close connections after database operations.

conn.close()
print("Database connection closed.")

Helps prevent memory leaks and connection issues.

13. Advanced MySQL Features in Python

Feature	Description
Indexes	Speed up queries (`CREATE INDEX`)
Joins	Combine data from multiple tables
Stored Procedures	Execute reusable SQL scripts
Foreign Keys	Maintain relationships between tables
Full-Text Search	Improve search functionality