How to Connect Python to MySQL Database

Introduction

Connecting Python to a MySQL database is a fundamental skill for backend developers, data analysts, and software engineers. Python is widely used for web applications, automation, and data processing, while MySQL is one of the most popular relational database management systems. When combined, Python and MySQL allow developers to build powerful, data-driven applications.

This guide explains how to connect Python to a MySQL database step by step. It is designed for beginners and intermediate learners and includes real-world use cases, practical examples, and best practices.

Why Connect Python to MySQL?

Python MySQL integration is commonly used in real-world applications where structured data storage and retrieval are required.

Common Use Cases

• Web applications that store user data
• Inventory and billing systems
• Data analysis and reporting tools
• Automation scripts for database operations
• Enterprise-level backend systems

Benefits of Using Python with MySQL

• Easy-to-read and maintainable code
• Strong community and library support
• Secure and scalable database handling
• Cross-platform compatibility

Prerequisites for Python MySQL Connection

Before connecting Python to MySQL, ensure the following requirements are met.

Popular Python Libraries to Connect MySQL

1. mysql-connector-python

This is the official MySQL driver provided by Oracle. It is reliable, secure, and widely used for production applications.

2. PyMySQL

PyMySQL is a pure Python MySQL client library. It is lightweight and easy to use, making it suitable for smaller projects.

Installing MySQL Connector for Python

To install the official MySQL connector, use the following command.

📄

pip install mysql-connector-python

Creating a MySQL Database and Table

Before connecting Python to MySQL, create a database and a table.

📄

CREATE DATABASE company_db;

USE company_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    salary DECIMAL(10,2)
);

How to Connect Python to MySQL Database

Basic Python MySQL Connection Example

The following example demonstrates how to establish a connection between Python and MySQL.

📄

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="company_db"
)

if connection.is_connected():
    print("Connected to MySQL database")

Explanation of the Code

• mysql.connector is imported to enable MySQL connectivity
• connect method establishes the database connection
• Host, user, password, and database are required parameters
• is_connected verifies successful connection

Performing CRUD Operations Using Python and MySQL

Inserting Data into MySQL Table

📄

cursor = connection.cursor()

query = "INSERT INTO employees (name, email, salary) VALUES (%s, %s, %s)"
values = ("Anita Sharma", "anita@example.com", 50000)

cursor.execute(query, values)
connection.commit()

print("Record inserted successfully")

Fetching Data from MySQL Database

📄

cursor.execute("SELECT * FROM employees")
records = cursor.fetchall()

for row in records:
    print(row)

Updating Records

📄

update_query = "UPDATE employees SET salary = %s WHERE id = %s"
cursor.execute(update_query, (55000, 1))
connection.commit()

Deleting Records

📄

delete_query = "DELETE FROM employees WHERE id = %s"
cursor.execute(delete_query, (1,))
connection.commit()

Real-World Example: Employee Management System

In a real-world employee management system, Python connects to MySQL to:

• Store employee details
• Update salaries and job roles
• Generate reports
• Manage user access

This integration ensures secure data handling and fast performance for enterprise applications.

Handling Errors and Closing the Connection

Error Handling Example

📄

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="company_db"
    )
except mysql.connector.Error as error:
    print("Error:", error)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection closed")

Connecting Python to a MySQL database is a critical skill for modern application development. By using libraries like mysql-connector-python, developers can easily perform database operations efficiently and securely. This guide covered installation, connection setup, CRUD operations, real-world examples, and best practices to help you confidently integrate Python with MySQL.

Frequently Asked Questions (FAQs)

1. Which library is best for Python MySQL connection?

mysql-connector-python is the official and most reliable library for connecting Python to MySQL, especially for production use.

2. Can Python connect to remote MySQL databases?

Yes, Python can connect to remote MySQL databases by specifying the remote host IP and ensuring firewall and MySQL permissions are configured.

3. Is MySQL required to be installed locally?

No, MySQL can be hosted on a remote server or cloud platform while Python connects to it over the network.

4. How do I prevent SQL injection in Python?

Always use parameterized queries instead of string concatenation when executing SQL statements.

5. Can Python MySQL connection be used in web frameworks?

Yes, Python frameworks like Django and Flask use MySQL extensively for backend database operations.