MySQL with Python

Python MySQL Libraries

🔹 Installation and Setup

Before using MySQL with Python, you need to install a MySQL connector library. The most popular choice is mysql-connector-python, which is the official MySQL driver. Install it using pip, Python's package manager. Once installed, you can import it in your Python scripts and start connecting to databases.

# Install mysql-connector-python
pip install mysql-connector-python

# Or install PyMySQL
pip install PyMySQL

# Install SQLAlchemy with MySQL support
pip install sqlalchemy pymysql

🔹 Connecting to MySQL

Establishing a connection to MySQL is the first step in any database operation. You need to provide connection parameters including host, username, password, and database name. Always handle connection errors properly and close connections when done to free up resources and prevent connection leaks.

import mysql.connector
from mysql.connector import Error

try:
    # Create connection
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='testdb'
    )
    
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Connected to MySQL Server version {db_info}")
        
        cursor = connection.cursor()
        cursor.execute("SELECT DATABASE();")
        record = cursor.fetchone()
        print(f"You're connected to database: {record}")

except Error as e:
    print(f"Error while connecting to MySQL: {e}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

🔹 Creating Tables

Once connected, you can create database tables using SQL CREATE TABLE statements executed through Python. Define your table structure with columns, data types, and constraints. Use the cursor object to execute SQL commands and commit changes to make them permanent in the database.

import mysql.connector

# Connect to database
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

cursor = db.cursor()

# Create table
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""

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

cursor.close()
db.close()

🔹 Inserting Data

Insert data into MySQL tables using INSERT statements with parameterized queries. Parameterized queries prevent SQL injection attacks and handle data types automatically. You can insert single records or multiple records at once using executemany(). Always commit your changes to save them permanently.

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

cursor = db.cursor()

# Insert single record
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = ("John Doe", "[email protected]", 28)

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

print(f"{cursor.rowcount} record inserted, ID: {cursor.lastrowid}")

# Insert multiple records
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = [
    ("Alice Smith", "[email protected]", 25),
    ("Bob Johnson", "[email protected]", 32),
    ("Carol White", "[email protected]", 29)
]

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

print(f"{cursor.rowcount} records inserted")

cursor.close()
db.close()

🔹 Querying Data

Retrieve data from MySQL using SELECT statements. Execute queries with the cursor and fetch results using fetchone(), fetchall(), or fetchmany() methods. You can iterate through results, filter data with WHERE clauses, and format output as needed for your application.

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

cursor = db.cursor()

# Fetch all records
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

print("All users:")
for row in results:
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}, Age: {row[3]}")

# Fetch with WHERE clause
sql = "SELECT * FROM users WHERE age > %s"
cursor.execute(sql, (25,))
results = cursor.fetchall()

print("\nUsers older than 25:")
for row in results:
    print(f"{row[1]} - {row[2]}")

# Fetch one record
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
user = cursor.fetchone()
print(f"\nUser with ID 1: {user}")

cursor.close()
db.close()

🔹 Updating and Deleting Data

Modify existing records with UPDATE statements and remove records with DELETE statements. Use WHERE clauses to target specific records. Always use parameterized queries for security. Check cursor.rowcount to see how many records were affected by your operation.

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

cursor = db.cursor()

# Update record
sql = "UPDATE users SET age = %s WHERE name = %s"
values = (30, "John Doe")

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

print(f"{cursor.rowcount} record(s) updated")

# Delete record
sql = "DELETE FROM users WHERE age < %s"
cursor.execute(sql, (20,))
db.commit()

print(f"{cursor.rowcount} record(s) deleted")

cursor.close()
db.close()

🔹 Using Context Managers

Context managers automatically handle connection and cursor cleanup, ensuring resources are properly released even if errors occur. Using the 'with' statement makes your code cleaner and more reliable. This is the recommended approach for database operations in Python as it prevents resource leaks.

import mysql.connector
from mysql.connector import Error

try:
    with mysql.connector.connect(
        host="localhost",
        user="root",
        password="password",
        database="mydb"
    ) as connection:
        
        with connection.cursor() as cursor:
            # Execute query
            cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
            results = cursor.fetchall()
            
            for row in results:
                print(f"Name: {row[1]}, Email: {row[2]}")
            
            # Insert data
            sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
            cursor.execute(sql, ("David Lee", "[email protected]", 27))
            connection.commit()
            print("Record inserted successfully")

except Error as e:
    print(f"Database error: {e}")