PyMySQL Driver: Connect MySQL with Python – Installation & Guide

Introduction

MySQL is one of the most popular relational databases, widely used in web development and enterprise solutions. To work with MySQL in Python, developers often use PyMySQL — a pure Python implementation of the MySQL protocol. It's easy to use, actively maintained, and compatible with popular libraries like SQLAlchemy and Pandas.

PyMySQL is a MySQL driver that lets you connect to a database, execute SQL queries, manage transactions, and integrate with web frameworks without complex setup. Unlike other drivers, PyMySQL is written entirely in Python, making it cross-platform and simple to install.

What is PyMySQL

PyMySQL is a Python client library that implements the MySQL Client/Server protocol. It provides an interface for connecting to a MySQL server and executing SQL queries. The library follows the Python Database API 2.0 specification (PEP 249), ensuring compatibility with other Python applications.

Key Features of PyMySQL

• Pure Python: No external C/C++ dependencies required
• Compatibility: Supports MySQL versions 5.5 and above
• Security: Built-in protection against SQL injection
• Performance: Optimized for fast query execution
• Ease of Use: Intuitive and straightforward API

Installation and Connecting to MySQL

Installation

pip install pymysql

For additional functionality, it's also recommended to install:

pip install pymysql[rsa]  # for RSA authentication support

Connecting to the Database

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test_db',
    port=3306,
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

DictCursor returns results as dictionaries instead of tuples, making data handling more convenient.

Connection Parameters

Creating a Database and Tables

# Create database
with connection.cursor() as cursor:
    cursor.execute("CREATE DATABASE IF NOT EXISTS test_db")

# Create table
with connection.cursor() as cursor:
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE,
            age INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
connection.commit()

CRUD Operations with PyMySQL

Inserting Data

# Insert a single record
with connection