Python MySQL Connection

Introduction

• What is MySQL?
• Why connect Python with MySQL?
• Real-world applications of Python-MySQL integration.

What is Python MySQL Connection?

Explain how Python applications communicate with MySQL databases using database connectors.

Benefits of Python MySQL Connection

• Data storage
• Data retrieval
• Automation
• Web applications
• Reporting systems

Installing MySQL Connector

Using pip

pip install mysql-connector-python

Verify Installation

import mysql.connector
print("Connector Installed Successfully")

Importing MySQL Connector

Syntax

import mysql.connector

Creating a MySQL Connection

Syntax

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

Example: Connect Python to MySQL

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="1234",
    database="company"
)
print("Connection Successful")

Output:

Checking Connection Status

Example:

if conn.is_connected():
    print("Database Connected")

Creating a Cursor Object

What is Cursor?

Cursor executes SQL queries.

Example:

cursor = conn.cursor()

Creating a Database Using Python

Example:

cursor.execute(
    "CREATE DATABASE company"
)

Creating a Table

Example:

query = """
CREATE TABLE employees(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
)
"""
cursor.execute(query)

Inserting Data into MySQL

Example:

sql = """
INSERT INTO employees
(id,name,salary)
VALUES
(1,'John',50000)
"""
cursor.execute(sql)
conn.commit()

Inserting Multiple Records

Example:

sql = """
INSERT INTO employees
(id,name,salary)
VALUES (%s,%s,%s)
"""
data = [
    (2,'Mike',60000),
    (3,'Sara',70000)
]
cursor.executemany(sql,data)
conn.commit()

Fetching Data from MySQL

Using fetchone()

cursor.execute(
    "SELECT * FROM employees"
)
row = cursor.fetchone()
print(row)

Using fetchall()

cursor.execute(
    "SELECT * FROM employees"
)
rows = cursor.fetchall()
for row in rows:
    print(row)

Updating Records

Example:

sql = """
UPDATE employees
SET salary=80000
WHERE id=1
"""
cursor.execute(sql)
conn.commit()

Deleting Records

Example:

sql = """
DELETE FROM employees
WHERE id=3
"""
cursor.execute(sql)
conn.commit()

Closing Connection

cursor.close()
conn.close()

Why closing connections is important.

Exception Handling in MySQL Connection

Example:

import mysql.connector
try:
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="1234"
    )
    print("Connected")
except mysql.connector.Error as e:
    print("Error:", e)

Real-Life Example: Student Management System

Create:

• Students table
• Insert student records
• Fetch student information

Complete working example.

Common MySQL Connection Errors

1. Access Denied Error

Cause:

Wrong username/password.

Solution:

2. Database Doesn’t Exist

Cause:

Incorrect database name.

Solution:

3. Connector Not Installed

Cause:

ModuleNotFoundError

Solution:

4. Connection Timeout

Cause:

MySQL server not running.

Solution:

Best Practices

• Always use parameterized queries.
• Close cursor and connection.
• Use try-except blocks.
• Avoid hardcoded credentials.
• Use connection pooling for large applications.
• Commit transactions carefully.

Advantages of Python MySQL Connection

Conclusion

Summarize Python-MySQL integration, installation, connection process, CRUD operations, error handling, and best practices. Mention that Python and MySQL together form the backbone of many web applications, ERP systems, CRM software, and data-driven applications.

Related Python Tutorials