Introduction

Working with databases is one of the most common tasks in application development, and MySQL remains one of the most widely deployed relational databases in the world. If you are building a Python 3 application on Windows that needs to store or retrieve structured data, you will need a reliable way to connect to MySQL. This article walks you through two popular driver libraries, shows you how to perform basic CRUD operations, and introduces connection pooling for production workloads.

Installing the Driver Libraries

Python does not ship with a MySQL driver, so you need to install one. The two most popular choices are mysql-connector-python (Oracle's official pure-Python driver) and PyMySQL (a community-maintained pure-Python driver). Both install cleanly on Windows without a C compiler.

1# Oracle's official connector
2pip install mysql-connector-python
3
4# Community alternative
5pip install PyMySQL

Choose one for your project. mysql-connector-python offers tighter integration with MySQL-specific features, while PyMySQL is lightweight and often used as a drop-in replacement in frameworks like Django and SQLAlchemy.

Connecting with mysql-connector-python

Once the package is installed, you create a connection by supplying the host, user, password, and database name. The pattern is the same whether you are on Windows, macOS, or Linux.

1import mysql.connector
2
3config = {
4    "host": "localhost",
5    "user": "root",
6    "password": "your_password",
7    "database": "my_app_db",
8}
9
10connection = mysql.connector.connect(**config)
11cursor = connection.cursor()
12
13cursor.execute("SELECT VERSION()")
14version = cursor.fetchone()
15print(f"MySQL version: {version[0]}")
16
17cursor.close()
18connection.close()

The cursor object is your handle for executing SQL statements and fetching results. Always close both the cursor and the connection when you are finished to free server resources.

Connecting with PyMySQL

PyMySQL follows the same DB-API 2.0 interface, so the code looks nearly identical.

1import pymysql
2
3connection = pymysql.connect(
4    host="localhost",
5    user="root",
6    password="your_password",
7    database="my_app_db",
8    cursorclass=pymysql.cursors.DictCursor,
9)
10
11with connection:
12    with connection.cursor() as cursor:
13        cursor.execute("SELECT VERSION()")
14        result = cursor.fetchone()
15        print(result)

Using DictCursor returns each row as a dictionary keyed by column name, which makes your code easier to read compared to positional tuples.

Performing CRUD Operations

Below is a complete example that creates a table, inserts a row, reads it back, updates it, and finally deletes it. The example uses mysql-connector-python, but the SQL and cursor API are the same with PyMySQL.

1import mysql.connector
2
3connection = mysql.connector.connect(
4    host="localhost",
5    user="root",
6    password="your_password",
7    database="my_app_db",
8)
9cursor = connection.cursor()
10
11# Create
12cursor.execute("""
13    CREATE TABLE IF NOT EXISTS users (
14        id INT AUTO_INCREMENT PRIMARY KEY,
15        name VARCHAR(100),
16        email VARCHAR(100)
17    )
18""")
19
20# Insert
21cursor.execute(
22    "INSERT INTO users (name, email) VALUES (%s, %s)",
23    ("Alice", "[email protected]"),
24)
25connection.commit()
26
27# Read
28cursor.execute("SELECT * FROM users WHERE name = %s", ("Alice",))
29print(cursor.fetchone())
30
31# Update
32cursor.execute(
33    "UPDATE users SET email = %s WHERE name = %s",
34    ("[email protected]", "Alice"),
35)
36connection.commit()
37
38# Delete
39cursor.execute("DELETE FROM users WHERE name = %s", ("Alice",))
40connection.commit()
41
42cursor.close()
43connection.close()

Notice that every write operation is followed by connection.commit(). MySQL connections created by these drivers default to autocommit off, so forgetting to commit is a frequent source of "missing data" bugs.

Connection Pooling

Opening a new database connection for every request is expensive. Connection pooling keeps a set of connections alive and hands them out on demand. mysql-connector-python includes a built-in pool.

1from mysql.connector import pooling
2
3pool = pooling.MySQLConnectionPool(
4    pool_name="my_pool",
5    pool_size=5,
6    host="localhost",
7    user="root",
8    password="your_password",
9    database="my_app_db",
10)
11
12# Borrow a connection from the pool
13conn = pool.get_connection()
14cursor = conn.cursor()
15cursor.execute("SELECT COUNT(*) FROM users")
16print(cursor.fetchone())
17cursor.close()
18conn.close()  # returns the connection to the pool

For PyMySQL, you can achieve the same effect with the DBUtils or SQLAlchemy connection pool wrappers.

Common Pitfalls

• Forgetting to commit: Both drivers default to autocommit off. If you execute INSERT, UPDATE, or DELETE statements and do not call connection.commit(), the changes are silently rolled back when the connection closes.
• Hardcoding credentials: Putting passwords directly in source code is a security risk. Use environment variables or a .env file loaded with python-dotenv instead.
• Not using parameterized queries: Building SQL with string concatenation (for example, f"SELECT * FROM users WHERE id = {user_id}") opens the door to SQL injection. Always use %s placeholders and pass values as a tuple.
• Ignoring connection timeouts: Long-lived connections can be dropped by the MySQL server (default wait_timeout is 8 hours). In production, use a connection pool or add reconnect logic to handle stale connections.
• Skipping error handling: Database operations can fail for many reasons (network issues, constraint violations, deadlocks). Wrap your calls in try/except blocks and handle mysql.connector.Error or pymysql.err.OperationalError explicitly.

Summary

• Install either mysql-connector-python or PyMySQL via pip; both are pure Python and work on Windows without a C compiler.
• Create a connection with host, user, password, and database parameters, then use a cursor to execute SQL.
• Always use parameterized queries (%s placeholders) to prevent SQL injection.
• Call connection.commit() after every write operation because autocommit is off by default.
• Use connection pooling in production to avoid the overhead of opening a new connection for every request.