Connecting Python to MySQL: A Complete Guide

MySQL is one of the most popular relational database management systems, widely used in web applications, enterprise solutions, and data-driven platforms. Python provides excellent support for MySQL, allowing developers to store, retrieve, and manage data seamlessly.

In this article, we’ll cover everything you need to know about connecting Python to MySQL, including setup, queries, and best practices.

Why Connect Python to MySQL?

MySQL is a reliable and scalable database that works well with Python applications. Common use cases include:

• Web development – Storing user accounts, sessions, and content.

• Data analysis – Retrieving large datasets for processing in Python.

• Enterprise software – Managing employees, customers, and sales records.

• Machine learning – Saving training datasets and model predictions.

Installing MySQL Connector

To connect Python to MySQL, you need the official MySQL Connector library. Install it using pip:

pip install mysql-connector-python

✅ This installs the MySQL driver for Python.

Establishing a Connection to MySQL

Here’s the basic code to connect Python to a MySQL database:

import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",   # or IP address of your MySQL server
    user="root",        # your MySQL username
    password="yourpassword",  # your MySQL password
    database="testdb"   # database name
)

print("Connection successful!")
connection.close()

✅ If the connection is successful, you’ll see the confirmation message.

Creating Tables in MySQL Using Python

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)
cursor = connection.cursor()

# Create a table
cursor.execute("""CREATE TABLE IF NOT EXISTS employees (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(50),
                    salary FLOAT)""")

print("Table created successfully!")
connection.close()

✅ This will create an employees table if it doesn’t already exist.

Inserting Data into MySQL

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)
cursor = connection.cursor()

# Insert data
cursor.execute("INSERT INTO employees (name, salary) VALUES (%s, %s)", ("Alice", 60000))

connection.commit()
print("Data inserted successfully!")
connection.close()

✅ Data is safely inserted into the employees table.

Fetching Data from MySQL

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)
cursor = connection.cursor()

cursor.execute("SELECT * FROM employees")

# Fetch all records
rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

✅ This retrieves all employee records from the database.

Updating and Deleting Data

Update Example:

cursor.execute("UPDATE employees SET salary = %s WHERE name = %s", (75000, "Alice"))
connection.commit()

Delete Example:

cursor.execute("DELETE FROM employees WHERE name = %s", ("Alice",))
connection.commit()

✅ These queries modify and remove data from MySQL tables.

Best Practices for Python-MySQL Connectivity

1. Always close connections after executing queries.

2. Use parameterized queries (%s) to prevent SQL injection.

3. Use try-except blocks to handle errors gracefully.

4. Use connection pooling for large-scale applications.

5. Consider ORMs like SQLAlchemy for complex projects.

Conclusion

Connecting Python to MySQL is straightforward with the mysql-connector-python library. By mastering connection setup, queries, and best practices, you can build reliable and data-driven Python applications. Whether you’re building a small project or a large enterprise system, Python + MySQL is a powerful combination.

SEO Keywords:

• connecting Python to MySQL

• Python MySQL connection example

• insert data into MySQL using Python

• fetch records from MySQL Python

• Python MySQL database tutorial