Python is one of the most popular programming languages, and MySQL is one of the most widely used relational databases. When combined, they allow you to build robust, data-driven applications.

In this tutorial, you'll learn:

• How to connect Python to a MySQL database

• How to install the required connector

• How to create databases and tables

• How to insert, retrieve, and manage data

• A complete working example

• Tips and common pitfalls

Prerequisites

Before you start, make sure you have:

✅ Python installed
✅ MySQL server installed and running
✅ Basic understanding of SQL (not mandatory)

Step 1: Install MySQL Connector for Python

To connect to MySQL, we use the mysql-connector-python package.

Install with PIP:

pip install mysql-connector-python

If you're using a virtual environment, activate it first.

Step 2: Connect to MySQL Database

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

# Print success message
print("Connected to MySQL!")

✅ Replace your_username and your_password with your MySQL credentials.

Step 3: Create a Database

cursor = db.cursor()
cursor.execute("CREATE DATABASE mydatabase")
print("Database created!")

Step 4: Connect to Your Database

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

Step 5: Create a Table

cursor = db.cursor()

cursor.execute("""
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

print("Table created successfully.")

Step 6: Insert Data into Table

sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Alice", "[email protected]")

cursor.execute(sql, values)
db.commit()

print(cursor.rowcount, "record inserted.")

Step 7: Retrieve Data from Table

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

for row in results:
    print(row)

Step 8: Update & Delete Records

Update:

sql = "UPDATE users SET email = %s WHERE name = %s"
values = ("[email protected]", "Alice")
cursor.execute(sql, values)
db.commit()

Delete:

sql = "DELETE FROM users WHERE name = %s"
values = ("Alice",)
cursor.execute(sql, values)
db.commit()

✅ Complete Working Example

import mysql.connector

# Connect to MySQL and database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

# Create table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
""")

# Insert data
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
users = [("John", "[email protected]"), ("Jane", "[email protected]")]
cursor.executemany(sql, users)
db.commit()

# Read data
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

# Update data
cursor.execute("UPDATE users SET email = '[email protected]' WHERE name = 'Jane'")
db.commit()

# Delete data
cursor.execute("DELETE FROM users WHERE name = 'John'")
db.commit()

⚠️ Common Pitfalls

Tips for Using Python with MySQL

• ✅ Always use prepared statements (%s) to prevent SQL injection.

• ✅ Use cursor.fetchall() or cursor.fetchone() to read results.

• ✅ Close the connection with db.close() when done.

• ✅ Use try-except blocks for better error handling.

Example:

try:
    db = mysql.connector.connect(...)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users")
    for row in cursor.fetchall():
        print(row)
except mysql.connector.Error as err:
    print("Error:", err)
finally:
    db.close()

Summary Table

Final Thoughts

Integrating MySQL with Python is a powerful skill that unlocks endless possibilities — from web apps to data analytics. This tutorial gave you a hands-on introduction to connecting, querying, and manipulating MySQL databases using Python.