Step-by-Step: Python MySQL Database Connection

Step 1: Install MySQL Connector

Install the required library via pip:

pip install mysql-connector-python

Step 2: Import the Library in Python

import mysql.connector

Step 3: Create a Connection to the Database

conn = mysql.connector.connect(
    host="localhost",        # your database server
    user="your_username",    # your MySQL username
    password="your_password",# your MySQL password
    database="your_db_name"  # the database you want to connect to
)

Note: Ensure MySQL server is running and the credentials are correct.

Step 4: Create a Cursor Object

cursor = conn.cursor()

The cursor lets you execute SQL queries.

Step 5: Execute Queries

Example: Create Table

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

Example: Insert Data

sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("John Doe", "john@example.com")
cursor.execute(sql, val)
conn.commit()  # Save changes

Example: Select Data

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

for row in results:
    print(row)

Step 6: Close the Connection

cursor.close()
conn.close()

Summary

Common Errors

• Access denied → Check username/password
• Can't connect → Check MySQL is running on the given host/port
• Database doesn't exist → Create it first via MySQL CLI or phpMyAdmin

Useful Tip

To dynamically handle results with column names:

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row["name"], row["email"])