MySQL is one of the most popular relational databases in the world, and when combined with Python, it forms an unbeatable duo for web applications, data analysis, and enterprise systems. Whether you're building a REST API, a management system, or any application that needs reliable data storage, mastering the integration between Python and MySQL is an essential skill.
In this complete guide, you'll learn everything from connector installation to advanced techniques like connection pooling, ORM usage, and security best practices. All examples are functional and tested with Python 3.12+ and MySQL 8.0+.
Why Use MySQL with Python?
The Python ecosystem offers multiple ways to connect to MySQL, each with specific advantages. mysql-connector-python is the official Oracle-maintained driver, while PyMySQL is a pure Python alternative that doesn't depend on external C libraries. Both follow the PEP 249 — Python Database API Specification, which standardizes the database interface in Python.
Before we dive in, if you don't have a Python environment set up yet, check out our guide on installing and configuring your Python environment to get everything ready.
Installing the Connectors
mysql-connector-python (Official Oracle)
pip install mysql-connector-pythonOracle's official driver is the safest choice for enterprise environments, with full support for all MySQL 8.0+ features including caching_sha2_password authentication, SSL/TLS connections, and the X DevAPI protocol.
PyMySQL (Pure Python Alternative)
pip install pymysqlPyMySQL is an excellent choice when you need a lightweight, portable installation without C library dependencies. It's particularly useful in development environments or Docker containers where image size matters.
SQLAlchemy (ORM + Core)
pip install sqlalchemy pymysqlSQLAlchemy is the most mature ORM (Object-Relational Mapper) in the Python ecosystem. It abstracts database differences and provides a high-level layer for working with MySQL, PostgreSQL, SQLite, and others. If you prefer a more productive approach, SQLAlchemy is the way to go.
Basic MySQL Connection
With mysql-connector-python
import mysql.connector
config = {
"host": "localhost",
"user": "root",
"password": "your_password",
"database": "my_database"
}
conn = mysql.connector.connect(**config)
print(f"Connected to MySQL version {conn.get_server_info()}")
conn.close()
With PyMySQL
import pymysql
conn = pymysql.connect(
host="localhost",
user="root",
password="your_password",
database="my_database",
charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor
)
with conn.cursor() as cursor:
cursor.execute("SELECT VERSION()")
result = cursor.fetchone()
print(f"Version: {result}")
conn.close()
Notice the use of DictCursor in PyMySQL. This cursor returns results as dictionaries where column names are the keys — much more readable than numeric tuples.
CRUD Operations with Python and MySQL
Let's create a sample table and execute the four fundamental operations: Create, Read, Update, and Delete.
1. CREATE — Inserting Data
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="your_password", database="my_database"
)
cursor = conn.cursor()
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
values = ("Jane Smith", "[email protected]", 28)
cursor.execute(sql, values)
conn.commit()
print(f"User inserted with ID: {cursor.lastrowid}")
cursor.close()
conn.close()
Important: Never use f-strings or string concatenation to build SQL queries. Using %s as placeholders protects against SQL injection, one of the most common attacks in web applications. Real Python has an in-depth guide on MySQL security with Python worth reading.
2. READ — Querying Data
cursor = conn.cursor(dictionary=True)
sql = "SELECT id, name, email, age FROM users WHERE age > %s"
cursor.execute(sql, (18,))
for user in cursor.fetchall():
print(f"{user['name']} — {user['email']} — {user['age']} years old")
The dictionary=True parameter in mysql-connector-python (equivalent to DictCursor in PyMySQL) returns each row as a dictionary, making it easier to access data by column name.
3. UPDATE — Updating Records
sql = "UPDATE users SET email = %s WHERE id = %s"
cursor.execute(sql, ("[email protected]", 1))
conn.commit()
print(f"Rows affected: {cursor.rowcount}")
4. DELETE — Removing Records
sql = "DELETE FROM users WHERE id = %s"
cursor.execute(sql, (10,))
conn.commit()
print(f"Rows removed: {cursor.rowcount}")
For more complex scenarios involving multiple tables, check out our guide on SQLite with Python — the SQL fundamentals apply equally to MySQL, and the transaction and commit concepts are identical.
Connection Pooling in Python with MySQL
In real-world applications, opening and closing connections for every request is inefficient. Connection pooling maintains a set of reusable connections, dramatically reducing latency and resource consumption.
import mysql.connector.pooling
pool = mysql.connector.pooling.MySQLConnectionPool(
pool_name="my_pool",
pool_size=10,
pool_reset_session=True,
host="localhost",
user="root",
password="your_password",
database="my_database"
)
def get_user(user_id):
conn = pool.get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cursor.fetchone()
cursor.close()
conn.close()
return user
The pool automatically manages connection creation and destruction. In web applications with FastAPI or Django, you configure the pool once at startup and reuse it across all endpoints. The official MySQL connection pooling documentation details all available parameters.
Schema Migrations with Alembic
As your application evolves, the database schema needs to keep up. Alembic is the official migration tool for SQLAlchemy, letting you version every change in your table structure.
pip install alembic
alembic init migrations
Create a migration
alembic revision --autogenerate -m "create_users_table"
Apply migrations
alembic upgrade head
With Alembic, you can roll back migrations (alembic downgrade), auto-generate scripts from SQLAlchemy models, and keep a full history of database changes in Git-versioned files. It's an indispensable practice in team projects, eliminating the problem of divergent schemas across development, staging, and production environments.
Using MySQL with FastAPI Asynchronously
For modern applications requiring high concurrency, the aiomysql driver provides asynchronous MySQL operations that integrate seamlessly with Python's asyncio ecosystem.
import asyncio
import aiomysql
async def main():
pool = await aiomysql.create_pool(
host="localhost", user="root", password="your_password",
db="my_database", minsize=5, maxsize=20
)
async with pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT COUNT(*) FROM users")
total = await cursor.fetchone()
print(f"Total users: {total[0]}")
pool.close()
await pool.wait_closed()
asyncio.run(main())
Combined with FastAPI, this pattern lets you handle hundreds of concurrent requests without blocking the event loop. Each request acquires a connection from the pool, runs the query, and returns the connection efficiently.
Working with SQLAlchemy and MySQL
For medium to large projects, SQLAlchemy provides an abstraction that dramatically simplifies working with MySQL. You define Python models and the ORM automatically generates tables and SQL queries.
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker
engine = create_engine(
"mysql+pymysql://root:your_password@localhost:3306/my_database",
pool_size=10,
max_overflow=20
)
Base = declarative_base()
class User(Base):
tablename = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(100), nullable=False)
email = Column(String(150), unique=True, nullable=False)
age = Column(Integer)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
Insert
new_user = User(name="John", email="[email protected]", age=32)
session.add(new_user)
session.commit()
Query
users = session.query(User).filter(User.age > 25).all()
for u in users:
print(f"{u.name} - {u.email}")
session.close()
SQLAlchemy supports migrations with Alembic, lazy loading, eager loading, automated joins, and much more. If you're migrating from SQLite to MySQL, the change is nearly transparent — just alter the connection string.
Error Handling and Transactions
Every robust application needs to handle database failures. In Python, error handling is done with try/except, and transactions ensure data consistency.
import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
conn.start_transaction()
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
conn.commit()
print("Transfer completed successfully!")
except Error as e:
conn.rollback()
print(f"Transaction error: {e}")
finally:
if conn.is_connected():
cursor.close()
conn.close()
The start_transaction() method begins an explicit transaction. If any operation fails, rollback() reverts all changes, maintaining data integrity. The DigitalOcean tutorial on MySQL with Python offers additional error handling examples for real-world scenarios.
Security Best Practices
- Never store credentials in code: Use environment variables or
.envfiles. Thepython-dotenvlibrary makes configuration management easy. - Always use placeholders:
%sor?in SQL statements prevent SQL injection. Never concatenate strings with user data. - SSL/TLS in production: Configure SSL certificates to encrypt communication between Python and MySQL, especially for remote connections.
- Principle of least privilege: Create MySQL users with specific permissions for each application, avoiding the use of the root user.
- Validate data before insertion: Use type hints and libraries like Pydantic to validate data before sending it to the database.
If you work with multiple databases, our guide on MongoDB with Python shows how to integrate NoSQL databases alongside MySQL in hybrid applications.
Performance and Optimization
For high-traffic applications, several performance strategies are essential:
- Connection pooling: Reuse connections instead of creating new ones for each operation.
- Batch inserts: Use
executemany()to insert multiple records in a single operation. - Indexes: Create MySQL indexes on columns used in WHERE and JOIN clauses.
- Fetch size: Use
cursor.fetchmany(size)to process large result sets in batches. - Prepared statements: Reuse execution plans for repetitive queries.
# Batch insert with executemany
data = [
("Anna", "[email protected]", 25),
("Peter", "[email protected]", 32),
("Carla", "[email protected]", 29),
]
cursor.executemany(
"INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
data
)
conn.commit()
print(f"{cursor.rowcount} records inserted.")Additional Tools and Resources
Beyond the standard connectors, the Python ecosystem offers complementary tools for working with MySQL:
- Alembic: Schema migration system that works with SQLAlchemy, ideal for versioning database changes.
- mysqlclient: Alternative C-based driver, faster than PyMySQL in intensive benchmark operations.
- aiomysql: Async version of PyMySQL for use with asyncio, perfect for high-concurrency FastAPI applications.
- SQLAlchemy 2.0: The latest ORM version brings native async/await support and built-in type hints.
To go deeper, we recommend the official MySQL documentation, covering everything from administration to performance tuning. The W3Schools interactive tutorial on MySQL with Python is also a great resource for beginners to practice the basics.
Conclusion
The integration between Python and MySQL is robust, flexible, and well-documented. In this guide, you learned everything from connector installation to advanced techniques like connection pooling, SQLAlchemy ORM, and security best practices. With this knowledge, you're ready to build scalable and secure Python applications using MySQL as your database foundation.
The natural next steps include exploring schema migrations with Alembic, MySQL replication for high availability, and integration with web frameworks like FastAPI and Django. Keep exploring the Python universe and deepen your knowledge of relational and NoSQL databases with our free guides.
Recommended internal links: