Connecting to MySQL Server from Python
Handout (Approx. 5 Pages)
1. Introduction
Connecting to a MySQL Server from Python enables applications to store, retrieve, update,
and manage data dynamically. This integration is essential for backend development, data
analysis, automation, and enterprise systems. Python provides several database connectors
that make communication with MySQL simple, secure, and efficient.
This handout explains the concepts, tools, and step-by-step procedures required to connect
Python programs to a MySQL Server, execute SQL commands, handle errors, and close
connections properly.
2. MySQL–Python Connectivity Overview
Python communicates with MySQL using database connector libraries that follow the
Python DB-API standard. These libraries translate Python commands into SQL queries that
the MySQL Server can understand.
Commonly used MySQL connectors in Python include:
• mysql-connector-python (official, recommended)
• PyMySQL
• MySQLdb (legacy)
In academic and professional environments, mysql-connector-python is widely preferred
due to its stability and official support.
3. Prerequisites
Before connecting Python to MySQL, ensure the following requirements are met:
• MySQL Server is installed and running
• Python 3.x is installed
• MySQL Connector for Python is installed
• MySQL user credentials are available
• A database exists on the MySQL Server
3.1 Installing MySQL Connector for Python
pip install mysql-connector-python
After installation, the connector can be imported into Python programs.
4. Establishing a Connection to MySQL Server
4.1 Basic Connection Syntax
import [Link]
connection = [Link](
host="localhost",
user="root",
password="password",
database="school_db"
)
if connection.is_connected():
print("Connected to MySQL Server")
4.2 Explanation of Connection Parameters
• host: Address of the MySQL Server (e.g., localhost)
• user: MySQL username
• password: MySQL user password
• database: Name of the database to connect to
A successful connection confirms that Python can communicate with the MySQL Server.
5. Cursor Object and SQL Execution
A cursor object is required to execute SQL statements.
cursor = [Link]()
5.1 Executing SQL Queries
[Link]("SELECT DATABASE();")
record = [Link]()
print("Connected to database:", record)
The cursor fetches results returned by SQL queries.
6. Performing Basic Database Operations
6.1 Creating a Table
[Link]("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
)
""")
6.2 Inserting Data
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Sara", "sara@[Link]")
[Link](sql, values)
[Link]()
6.3 Retrieving Data
[Link]("SELECT * FROM users")
rows = [Link]()
for row in rows:
print(row)
7. Error Handling in MySQL Connections
Handling errors prevents application crashes and helps debugging.
try:
connection = [Link](
host="localhost",
user="root",
password="password",
database="school_db"
)
except [Link] as error:
print("Failed to connect:", error)
Common connection errors include:
• Invalid credentials
• Database does not exist
• MySQL Server not running
8. Security Best Practices
• Avoid hardcoding credentials in source code
• Use environment variables for passwords
• Apply least-privilege access control
• Always use parameterized queries
Example of secure query:
[Link]("SELECT * FROM users WHERE email = %s", (email,))
9. Closing the Connection
Properly closing resources is important for performance and security.
if connection.is_connected():
[Link]()
[Link]()
print("MySQL connection closed")
10. Summary
Connecting to a MySQL Server from Python is a fundamental skill for database-driven
applications. Using MySQL Connector for Python, developers can establish secure
connections, execute SQL commands, handle errors gracefully, and manage resources
efficiently. Mastery of these concepts enables the development of reliable and scalable
software systems.