Python - Connect MySQL Database

 

import mysql.connector
from mysql.connector import Error

def create_connection():
    """Create a database connection"""
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database'
        )
        print("Connected to MySQL database")
        return conn
    except Error as e:
        print(f"Error: {e}")
        return None

def select_data(conn):
    """SELECT example using 'with' statement"""
    with conn.cursor() as cursor:
        cursor.execute("SELECT id, name, age FROM users")
        rows = cursor.fetchall()
        print("SELECT result:")
        for row in rows:
            print(row)

def insert_data(conn, name, age):
    """INSERT example"""
    with conn.cursor() as cursor:
        sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
        cursor.execute(sql, (name, age))
        conn.commit()
        print(f"{cursor.rowcount} record inserted.")

def update_data(conn, user_id, new_age):
    """UPDATE example"""
    with conn.cursor() as cursor:
        sql = "UPDATE users SET age = %s WHERE id = %s"
        cursor.execute(sql, (new_age, user_id))
        conn.commit()
        print(f"{cursor.rowcount} record updated.")

def delete_data(conn, user_id):
    """DELETE example"""
    with conn.cursor() as cursor:
        sql = "DELETE FROM users WHERE id = %s"
        cursor.execute(sql, (user_id,))
        conn.commit()
        print(f"{cursor.rowcount} record deleted.")

def main():
    conn = create_connection()
    if conn:
        try:
            # INSERT
            insert_data(conn, "Bob", 30)
            
            # SELECT
            select_data(conn)
            
            # UPDATE
            update_data(conn, user_id=1, new_age=31)
            
            # DELETE
            delete_data(conn, user_id=1)
        finally:
            conn.close()
            print("Connection closed.")

if __name__ == "__main__":
    main()

 

Here’s a Python example using pymysql with with statements for safe and clean database operations:

First, install pymysql if you don’t have it yet:

pip install pymysql

---

Python Example with pymysql

Reusable MySQL Helper Class

import pymysql

class MySQLDatabase:
    def __init__(self, host, user, password, database):
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        print("Connected to MySQL database")

    def select(self, table, columns='*', where=None):
        """SELECT rows from table"""
        sql = f"SELECT {columns} FROM {table}"
        params = None
        if where:
            sql += f" WHERE {where[0]}"
            params = where[1]
        with self.conn.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()

    def insert(self, table, data):
        """INSERT a row into table"""
        keys = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({placeholders})"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()))
        self.conn.commit()
        print(f"{cursor.rowcount} record inserted.")

    def update(self, table, data, where):
        """UPDATE rows in table"""
        set_clause = ", ".join(f"{k}=%s" for k in data.keys())
        sql = f"UPDATE {table} SET {set_clause} WHERE {where[0]}"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()) + tuple(where[1]))
        self.conn.commit()
        print(f"{cursor.rowcount} record updated.")

    def delete(self, table, where):
        """DELETE rows from table"""
        sql = f"DELETE FROM {table} WHERE {where[0]}"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, where[1])
        self.conn.commit()
        print(f"{cursor.rowcount} record deleted.")

    def close(self):
        """Close connection"""
        self.conn.close()
        print("Connection closed")

 

✅ Highlights of this pymysql version:

1. DictCursor allows you to get results as dictionaries (row['name']) instead of tuples.

2. with conn.cursor() as cursor: automatically closes the cursor after the block.

3. conn.commit() is called after DML operations (INSERT, UPDATE, DELETE).

 

Example Usage

def main():
    db = MySQLDatabase(host='localhost', user='your_username',
                       password='your_password', database='your_database')

    # INSERT
    db.insert('users', {'name': 'David', 'age': 32})

    # SELECT
    rows = db.select('users')
    print("SELECT result:", rows)

    # UPDATE
    db.update('users', {'age': 33}, ("id=%s", (1,)))

    # DELETE
    db.delete('users', ("id=%s", (1,)))

    # Close connection
    db.close()

if __name__ == "__main__":
    main()

 

✅ Advantages of this Class

1. Reusable: Works for any table and any number of columns.

2. Safe: Uses parameterized queries to prevent SQL injection.

3. Clean: Handles cursor management automatically with with.

4. Flexible: Easy to extend with more helper methods if needed.

 

Let’s make a professional, advanced version of the MySQL helper class that supports:

• Batch inserts

• Transactions (commit and rollback)

• Using it as a context manager (with db as ...)

• Automatic cursor and connection management

Advanced MySQL Helper Class

import pymysql

class MySQLDatabase:
    def __init__(self, host, user, password, database):
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=False  # Manage transactions manually
        )
        print("Connected to MySQL database")

    # Context manager support
    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.conn.rollback()
            print("Transaction rolled back due to exception")
        else:
            self.conn.commit()
            print("Transaction committed")
        self.conn.close()
        print("Connection closed")

    # SELECT
    def select(self, table, columns='*', where=None):
        sql = f"SELECT {columns} FROM {table}"
        params = None
        if where:
            sql += f" WHERE {where[0]}"
            params = where[1]
        with self.conn.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()

    # INSERT single row
    def insert(self, table, data):
        keys = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({placeholders})"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()))
        print(f"{cursor.rowcount} record inserted.")

    # INSERT multiple rows (batch)
    def insert_many(self, table, data_list):
        if not data_list:
            return
        keys = ", ".join(data_list[0].keys())
        placeholders = ", ".join(["%s"] * len(data_list[0]))
        sql = f"INSERT INTO {table} ({keys}) VALUES ({placeholders})"
        values = [tuple(data.values()) for data in data_list]
        with self.conn.cursor() as cursor:
            cursor.executemany(sql, values)
        print(f"{cursor.rowcount} records inserted.")

    # UPDATE
    def update(self, table, data, where):
        set_clause = ", ".join(f"{k}=%s" for k in data.keys())
        sql = f"UPDATE {table} SET {set_clause} WHERE {where[0]}"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, tuple(data.values()) + tuple(where[1]))
        print(f"{cursor.rowcount} record updated.")

    # DELETE
    def delete(self, table, where):
        sql = f"DELETE FROM {table} WHERE {where[0]}"
        with self.conn.cursor() as cursor:
            cursor.execute(sql, where[1])
        print(f"{cursor.rowcount} record deleted.")

 

Example Usage with Context Manager

def main():
    with MySQLDatabase(host='localhost', user='your_username',
                       password='your_password', database='your_database') as db:

        # INSERT single row
        db.insert('users', {'name': 'Emma', 'age': 27})

        # INSERT multiple rows
        db.insert_many('users', [
            {'name': 'Frank', 'age': 30},
            {'name': 'Grace', 'age': 22}
        ])

        # SELECT
        rows = db.select('users')
        print("SELECT result:", rows)

        # UPDATE
        db.update('users', {'age': 28}, ("name=%s", ('Emma',)))

        # DELETE
        db.delete('users', ("name=%s", ('Grace',)))

        # If any exception occurs, transaction will rollback automatically

if __name__ == "__main__":
    main()

 

✅ Highlights of This Advanced Version

1. Context Manager (with): Handles commit/rollback and closes connection automatically.

2. Transactions: If an exception occurs inside with, changes are rolled back.

3. Batch Inserts: insert_many allows multiple rows to be inserted efficiently.

4. Flexible CRUD: Works with any table and columns.

5. Safe: Uses parameterized queries to prevent SQL injection.